| Author |
 Topic  |
|
|
learning_grsql
Posting Yak Master
155 Posts |
 Posted - 09/03/2012 : 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 |
Edited by - learning_grsql on 09/03/2012 11:13:14
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47098 Posts |
Posted - 09/03/2012 : 15:02:17
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 09/04/2012 : 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
India
24 Posts |
Posted - 09/04/2012 : 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
|
|
|
| |
Topic |
|
|
|
calculate subtotal weekly once
