Kristen Jones
asked on
SQL Syntax Help with Group, Sum and dates
I have a MS SQL 2008 Query that I had someone make and I am trying to do a similar query with a different database and I just chaneged the field name. But it is not working. The Table is Called Orders and it has two field ChargedTotal and ShippingTotal. I need a result that if I enter a date range that is will seperate each day out and Sum the totals for that day, or show a Null if nothing.
Here is what I have so far..
SELECT
CONVERT(varchar(6), MakeDates_2.thedate, 101) +
CONVERT(varchar(4), MakeDates_2.thedate, 120) AS DateTime,
CAST(SUM(S.ChargedTotal) AS int) AS ChargedTotal,
ROUND(SUM(S.ShippingTotal) , 3) AS ShippingTotal
FROM MakeDates('1/1/2011', '12/23/2011') AS MakeDates_2
LEFT OUTER JOIN (SELECT PaymentReceived, ChargedTotal, ShippingTotal,
CAST(CONVERT(varchar(10), OrderDate, 120) AS datetime) AS DATETIME2
FROM Orders) AS S ON S.PaymentReceived LIKE 'yes' AND S.DATETIME2 = MakeDates_2.thedate
GROUP BY MakeDates_2.thedate
ORDER BY MakeDates_2.thedate
But I get the error:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'MakeDates'.
Here is what I have so far..
SELECT
CONVERT(varchar(6), MakeDates_2.thedate, 101) +
CONVERT(varchar(4), MakeDates_2.thedate, 120) AS DateTime,
CAST(SUM(S.ChargedTotal) AS int) AS ChargedTotal,
ROUND(SUM(S.ShippingTotal)
FROM MakeDates('1/1/2011', '12/23/2011') AS MakeDates_2
LEFT OUTER JOIN (SELECT PaymentReceived, ChargedTotal, ShippingTotal,
CAST(CONVERT(varchar(10), OrderDate, 120) AS datetime) AS DATETIME2
FROM Orders) AS S ON S.PaymentReceived LIKE 'yes' AND S.DATETIME2 = MakeDates_2.thedate
GROUP BY MakeDates_2.thedate
ORDER BY MakeDates_2.thedate
But I get the error:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'MakeDates'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER