calculate subtotal weekly once

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

calculate subtotal weekly once

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-03 : 11:06:17

Hi,

I have a table with date column and my sql query is somehow similar to below :


select date, sum(qty1), sum(qty2), sum(qty3), sum(qty1 + qty2 + qty3) as Total
group by date
with rollup

And output is somehow similar to below for the above code:


Date        |qty1|qty2|qty3|Total
5 July 2012 | 35 | 46 | 35 | 116
6 July 2012 | 23 | 33 | 12 | 68
7 July 2012 | 47 | 23 | 30 | 100
8 July 2012 | 2  | 5  | 3  | 10
null        | 107| 107| 80 | 294

But I'm looking for output something like below where I want subtotal for every saturday for period Sunday to Saturday.

In this example, 7th july is saturday.
Date |qty1|qty2|qty3|Total |subtotal
5 July 2012 | 35 | 46 | 35 | 116 |
6 July 2012 | 23 | 33 | 12 | 68 |
7 July 2012 | 47 | 23 | 30 | 100 | 284

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-03 : 15:02:17

[code]
select date, sum(qty1), sum(qty2), sum(qty3), sum(qty1 + qty2 + qty3) as Total,
case when datename(dw,date) ='Saturday' then sum(qty1 + qty2 + qty3) over (partition by dateadd(wk,datediff(wk,0,date),0)) else 0 end as Subtotal
group by date
with rollup
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-04 : 02:09:37

Thanks a lot visakh16...you are great
I have been just witnessing two more issue here. During end of month we need to calculate subtotal till 31st or30th irrespective of when Saturday falls.
For e.g.
(1) if last Saturday was on 27th, next subtotal i want between 28th and 31st so that I can close the month.
(2) if last Saturday was on 22nd, next subtotal i want between 23rd and 31st ...(next saturday will be 29th but we don't want on 29th as close of month is near..it is better if we calculate subtotal on 31st)...

I have been doing this things manually at end of month after exporting but it would be nice if it is automated...but if it is not possible not a problem. You already helped me for my main problem.

arpana patil
Starting Member

24 Posts

Posted - 2012-09-04 : 02:11:53

;with CTE(Rownumber,date,qty1,qty2,qty3,Total,Day)
as
(
select ROW_NUMBER() OVER (ORDER BY date ) as Rownumber,date,qty1,qty2,qty3,(qty1+qty2+qty3) as Total,DATENAME(dw,date) as Day from dbo.GetSubtotal
)
select a.Rownumber,a.date,a.qty1,a.qty2,a.qty3,a.Total,a.Day,a.Total+COALESCE((SELECT SUM(Total)
FROM CTE b
WHERE b.rownumber < a.rownumber),0 )
AS RunningTotal,case when Day='Saturday' then a.Total+COALESCE((SELECT SUM(Total)
FROM CTE b
WHERE b.rownumber < a.rownumber),0 ) else '' end as SubTotal
from CTE a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources