calculating MTD on Daily Sales

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

General SQL Server Forums

New to SQL Server Programming

calculating MTD on Daily Sales

Author

Topic

bhavyam
Starting Member

4 Posts

Posted - 2011-05-29 : 23:57:20

Hi

i have calculated MTD sales figures from the daily sales figures
where the date is queried. however when a queried date has no daily sales then the MTD sales is blank. Is there anyway i can avoid this and the MTD figures gets calculated from the previous day hence leaving no blanks.

here is my query it has COGS and Sales QTY

SELECT D.[Date in char], D.Subdiv, D.Customer ,D.Item, D.[Item Description], D.[Sales Amt],
(SELECT Sum(a.[Sales Amt])
FROM dbo.SalesDetails AS a
WHERE (a.[Date in char] <= D.[Date in char] and
a.[Item] = D.[Item])
AND (Month(a.[Date in char]) =
Month(D.[Date in char] ))) AS MTDSales,
(SELECT Sum(a.[COGS Amt])
FROM dbo.SalesDetails AS a
WHERE (a.[Date in char] <= D.[Date in char] and
a.[Item] = D.[Item])
AND (Month(a.[Date in char]) =
Month(D.[Date in char] ))) AS MTDCOGS,
(SELECT Sum(a.[Sales QTY])
FROM dbo.SalesDetails AS a
WHERE (a.[Date in char] <= D.[Date in char] and
a.[Item] = D.[Item])
AND (Month(a.[Date in char]) =
Month(D.[Date in char] ))) AS MTDSalesQTY

FROM dbo.SalesDetails D
Where D.[Date in char] = '2011-05-05'
Group by D.Subdiv,D.Customer ,D.Item, D.[Item Description], D.[Date in char], D.[Sales Amt]
Order by D.[Date in char]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-30 : 11:31:25

Instead of using the specific date in the WHERE clause, you can use the month and year of the date that is passed in to the month and year of the data in your table as shown below:


WHERE
   month(D.[Date in char]) = month('2011-05-05')
   and year(D.[Date in char]) = year('2011-05-05')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-30 : 12:15:50

What is the version of SQL Server you are using ?

KH
[spoiler]Time is always against us[/spoiler]

bhavyam
Starting Member

4 Posts

Posted - 2011-05-30 : 18:46:41

to sunitabek
they query is built for a BI product, where that date in the WHERE clause, is actually a date picker, so that the users can chose the date they want to see. so i cant use the month and the year separately

to khtn
its SQL sever 2008

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-30 : 19:16:46

so, if the queried date has no daily sales, how to get the subdiv, customer, item etc ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources