query with esspressions

[rsbutterfly16]

hi guys , i need help with my espressions in this view..... my espressionskeep giving me an error message... can anyone help?



SELECT dbo.[query 2 b ].Expr1, dbo.[query 2 b ].Expr1 AS Customer_No,
MAX(dbo.[query 2 b ].Invoice_Date) AS MaxofInvoice_Date, MIN(dbo.[query 2 b ].Invoice_Date)
AS MinOfInvoice_Date, [MinofInvoice_Date] AS [Min], dbo.[Information Query for Customers].CustomerName, dbo.[Sales].[Sales Representative],
dbo.[Sales].[Customer Type], dbo.[Sales].[Bonus Check Date], dbo.[query 2 b ]._Facility_ID,
COUNT(dbo.[query 2 b ].Expr1) AS Expr9, dbo.[query 2 b ].PM_Parent_ID, DATEDIFF(Day, MaxofInvoice_Date, MinOfInvoice_Date)AS Expr2,
CASE When([Expr12]>= 90 then 1 else 0) AS Expr14, DATEDIFF(DAY, GETDATE()-[MinOfInvoice_Date])AS Expr12

FROM dbo.[query 2 b ] LEFT OUTER JOIN
dbo.[Information Query for Customers] ON dbo.[query 2 b ].Expr1 = dbo.[Information Query for Customers].Expr1 LEFT OUTER JOIN
dbo.Cross_Refference ON dbo.[Information Query for Customers].Expr1 = dbo.Cross_Refference.Customer_No LEFT OUTER JOIN
dbo.[Sales] ON dbo.Cross_Refference.Customer_No = dbo.[Sales].Customer_No

WHERE (dbo.[Sales].[Sales Representative] = 'Lucy') AND (dbo.[Sales].[Customer Type] = 'New')

GROUP BY dbo.[query 2 b ].Expr1, dbo.[query 2 b ].Expr1, dbo.[Information Query for Customers].CustomerName,
dbo.[Sales].[Sales Representative], dbo.[Sales].[Customer Type], dbo.[Sales].[Bonus Check Date],
dbo.[query 2 b ]._Facility_ID, dbo.[query 2 b ].PM_Parent_ID




these are the errors i am getting:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'MinofInvoice_Date'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MinOfInvoice_Date'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MaxofInvoice_Date'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Expr12'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MinOfInvoice_Date'.





I would really apreciate it!!!

Jessy

 

[pdx_man]

You are trying to do too much in one query.

Try something like:

Select Field1,
MAX(Field2) as MyMax,
MIN(Field2) as MyMin
:
:
Into #Results
From MyTable
Group By Field1

Select DateDiff(Month, MyMax, MyMin) as MyDiff
From #Results

Drop Table #Results

 

[rsbutterfly16]

i am not sure what you mean, add a temporary table and then drop it?

 

[SQL_Hell]

I think the problem lies here

MIN(dbo.[query 2 b ].Invoice_Date)
AS MinOfInvoice_Date, [MinofInvoice_Date] AS [Min],[/COLOR]


You seem to be creating an alias on an already aliased column? which sql server does not reconise

change to...

MIN(dbo.[query 2 b ].Invoice_Date)
AS [Min]

and do the same for the max value as well

 

[rsbutterfly16]

thanks, i did it and i get the same errors, am i mission something in the min and max function? it seems that is not being recognized....