SQL Syntax Help with Group, Sum and dates
Posted on 2011-09-09
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..
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'.