total for each day - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

total for each day

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2009-12-02 : 05:09:49

HI all,

I got this SP that has output of total sales a month.
But i want to make select that shows
Day of the week, date, value
ex
monday 1/1/2010 300

How can i achief this?

SELECT @totals=cast(SUM(totals) as nvarchar(100))
FROM (SELECT SUM(dbo.T_Order_Detail.Quantity * dbo.T_Order_Detail.Cost) AS totals
FROM dbo.T_Order_Main INNER JOIN
dbo.T_Customer ON dbo.T_Order_Main.CustomerID = dbo.T_Customer.CustomerID RIGHT OUTER JOIN
dbo.T_Order_Detail ON dbo.T_Order_Main.ORDERID = dbo.T_Order_Detail.OrderID
WHERE (dbo.T_Order_Main.Orderstatus = 8) AND (DATEDIFF(MONTH, dbo.T_Order_Main.Shipdate, GETDATE()) < 1) AND (dbo.T_Customer.site = @site)
GROUP BY dbo.T_Order_Detail.OrderID ) AS DERIVEDTBL

Thanks a lot

Mike

Sachin.Nand

2937 Posts

Posted - 2009-12-02 : 05:28:08

You can get the startdate & enddate of currentweek this way
select MIN(DATEADD(wk, DATEDIFF(wk,7, GETDATE()), 7))AS startdate ,
MIN(DATEADD(dd,7,DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6))) AS enddate

PBUH

mike13
Posting Yak Master

219 Posts

Posted - 2009-12-02 : 06:33:18

Hi,

Sorry that was not my question.

i want the totals of each day of the month.
I got this now but it is not working, i think because date has time (2009-11-06 10:25:18.523) etc

SELECT dbo.T_Order_Main.Shipdate, SUM(dbo.T_Order_Detail.Quantity * dbo.T_Order_Detail.Cost) AS totals
FROM dbo.T_Order_Main INNER JOIN
dbo.T_Customer ON dbo.T_Order_Main.CustomerID = dbo.T_Customer.CustomerID RIGHT OUTER JOIN
dbo.T_Order_Detail ON dbo.T_Order_Main.ORDERID = dbo.T_Order_Detail.OrderID
WHERE (dbo.T_Order_Main.Orderstatus = 8) AND (DATEDIFF(MONTH, dbo.T_Order_Main.Shipdate, GETDATE()) < 1)
GROUP BY dbo.T_Order_Main.Shipdate

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-02 : 07:19:01

Try this

SELECT dbo.T_Order_Main.Shipdate, SUM(dbo.T_Order_Detail.Quantity * dbo.T_Order_Detail.Cost) AS totals
FROM dbo.T_Order_Main INNER JOIN
dbo.T_Customer ON dbo.T_Order_Main.CustomerID = dbo.T_Customer.CustomerID RIGHT OUTER JOIN
dbo.T_Order_Detail ON dbo.T_Order_Main.ORDERID = dbo.T_Order_Detail.OrderID
WHERE (dbo.T_Order_Main.Orderstatus = 8) AND (DATEDIFF(MONTH, dbo.T_Order_Main.Shipdate, GETDATE()) < 1)
GROUP BY dateadd(day,datediff(day,0,dbo.T_Order_Main.Shipdate),0)

Madhivanan

Failing to plan is Planning to fail

mike13
Posting Yak Master

219 Posts

Posted - 2009-12-02 : 07:30:48

almost it says: collumn dbo.T_Order_Main.Shipdate is invalid in the select list because it is not contained in either aggregate function or the group by clause

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-02 : 07:34:14

SELECT dateadd(day,datediff(day,0,dbo.T_Order_Main.Shipdate),0), SUM(dbo.T_Order_Detail.Quantity * dbo.T_Order_Detail.Cost) AS totals
FROM dbo.T_Order_Main INNER JOIN
dbo.T_Customer ON dbo.T_Order_Main.CustomerID = dbo.T_Customer.CustomerID RIGHT OUTER JOIN
dbo.T_Order_Detail ON dbo.T_Order_Main.ORDERID = dbo.T_Order_Detail.OrderID
WHERE (dbo.T_Order_Main.Orderstatus = 8) AND (DATEDIFF(MONTH, dbo.T_Order_Main.Shipdate, GETDATE()) < 1)
GROUP BY dateadd(day,datediff(day,0,dbo.T_Order_Main.Shipdate),0)

Madhivanan

Failing to plan is Planning to fail

mike13
Posting Yak Master

219 Posts

Posted - 2009-12-02 : 07:49:56

Cool, that did the trick.
now i'm only missing the day name, like Monday,Tuesday

tnx a lot

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-02 : 07:57:21

SELECT datename(weekday,dbo.T_Order_Main.Shipdate),dateadd(day,datediff(day,0,dbo.T_Order_Main.Shipdate),0), SUM(dbo.T_Order_Detail.Quantity * dbo.T_Order_Detail.Cost) AS totals
FROM dbo.T_Order_Main INNER JOIN
dbo.T_Customer ON dbo.T_Order_Main.CustomerID = dbo.T_Customer.CustomerID RIGHT OUTER JOIN
dbo.T_Order_Detail ON dbo.T_Order_Main.ORDERID = dbo.T_Order_Detail.OrderID
WHERE (dbo.T_Order_Main.Orderstatus = 8) AND (DATEDIFF(MONTH, dbo.T_Order_Main.Shipdate, GETDATE()) < 1)
GROUP BY datename(weekday,dbo.T_Order_Main.Shipdate),dateadd(day,datediff(day,0,dbo.T_Order_Main.Shipdate),0)

Madhivanan

Failing to plan is Planning to fail

mike13
Posting Yak Master

219 Posts

Posted - 2009-12-02 : 16:25:00

that did the trick thanks a lot, you rule!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-03 : 02:16:19