Solved

SQL Syntax Help with Group, Sum and dates

Posted on 2011-09-09
3
277 Views
Last Modified: 2012-05-12
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'.
0
Comment
Question by:Jason Jones
3 Comments
 
LVL 15

Accepted Solution

by:
pateljitu earned 250 total points
ID: 36513004
Did you create function MakeDates for this query?
0
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 250 total points
ID: 36513056
MakeDates appears to be a Table Valued function.  Make sure it exists in your current database and that you prefix it with the correct schema (possibly dbo).
0
 
LVL 1

Author Comment

by:Jason Jones
ID: 36522208
Thanks for the replies, I posted this late Friday and just now back in the office.  I checked and there is a function I forgot to bring over.  Thanks for pointing that out.. how do you guys want me to divvy points?
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question