Solved

SQL Syntax Help with Group, Sum and dates

Posted on 2011-09-09
3
273 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:upgraders
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:upgraders
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now