Help in getting out put - 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)

Help in getting out put

Author

Topic

scr2k8
Starting Member

3 Posts

Posted - 2008-09-20 : 16:40:38

Here I have resultset of data after combing the all the four tables.
This I need to run on 12 years of data.I need to get the running total with respect to the months.This basically for the report generation
for that sake here I sample data how it look like all in one table.
Can any one suggest the solution for this

CREATE TABLE test_Prd(
PrdId int ,
PrdSrl varchar(10) ,
PlcyCd char(2) ,
WntyStDate datetime
)

INSERT INTO test_Prd VALUES (1001,'F1001','QB','03/13/2008')
go
INSERT INTO test_Prd VALUES (1002,'F1002','QB','01/22/2008')
go
INSERT INTO test_Prd VALUES (1003,'W1001','QB','02/02/2008')
go
INSERT INTO test_Prd VALUES (1004,'F1003','SB','05/10/2008')
go
INSERT INTO test_Prd VALUES (1005,'F1005','SB','04/24/2008')
go
INSERT INTO test_Prd VALUES (1006,'F1006','QB','02/10/2008')
go
INSERT INTO test_Prd VALUES (1007,'W1002','QB','03/20/2008')
go
INSERT INTO test_Prd VALUES (1008,'F1007','QB','04/22/2008')
go
INSERT INTO test_Prd VALUES (1009,'F1008','QB','06/17/2008')
go
INSERT INTO test_Prd VALUES (1010,'F1009','SB','08/12/2008')
go
INSERT INTO test_Prd VALUES (1011,'F10011','SB','03/13/2008')
go
INSERT INTO test_Prd VALUES (1012,'F10012','SB','01/22/2008')
go
INSERT INTO test_Prd VALUES (1013,'W10011','QB','02/02/2008')
go
INSERT INTO test_Prd VALUES (1014,'F10013','QB','05/10/2008')
go
INSERT INTO test_Prd VALUES (1016,'W10016','QB','02/10/2008')
go
INSERT INTO test_Prd VALUES (1017,'W10012','QB','05/20/2008')
go
INSERT INTO test_Prd VALUES (1015,'F10015','QB','04/24/2008')
go
INSERT INTO test_Prd VALUES (1018,'F10017','SB','04/14/2008')
go
INSERT INTO test_Prd VALUES (1019,'F10018','QB','06/17/2008')
go
INSERT INTO test_Prd VALUES (1020,'F10019','QB','08/12/2008')
go

**********
select substring(prdsrl,1,1) as sprd
,plcycd
,datediff(month,wntystdate,'08/31/2008') as mnth
,count(*) as cnt
from test_Prd
WHERE wntystdate BETWEEN '01/01/2007' AND '08/31/2008'
and datediff(month,wntystdate,'08/31/2008')>0
group by substring(prdsrl,1,1)
,plcycd
,datediff(month,wntystdate,'08/31/2008')

order by 1,2,3
*********

I need the output like below
sprd plcycd mnth cnt RunningTotal
------- --------- ------- ------
F QB 1 0 8
F QB 2 2 8
F QB 3 1 6
F QB 4 2 5
F QB 5 1 3
F QB 6 1 2
F QB 7 1 1
F SB 1 0 5
F SB 2 0 5
F SB 3 1 5
F SB 4 2 4
F SB 5 1 2
F SB 6 0 1
F SB 7 1 1
W QB 1 0 5
W QB 2 0 5
W QB 3 1 5
W QB 4 0 4
W QB 5 1 4
W QB 6 3 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-21 : 02:07:08

may be this

select m.sprd,m.plcycd,m.mnth,m.cnt,
t.Runcnt
from
(
select substring(prdsrl,1,1) as sprd
,plcycd
,datediff(month,wntystdate,'08/31/2008') as mnth
,count(*) as cnt 
from test_Prd
WHERE wntystdate BETWEEN '01/01/2007' AND '08/31/2008'
and datediff(month,wntystdate,'08/31/2008')>0
group by substring(prdsrl,1,1)
,plcycd
,datediff(month,wntystdate,'08/31/2008')
)m
outer apply (select count(*) AS Runcnt
             from test_Prd 
             WHERE wntystdate BETWEEN '01/01/2007' AND '08/31/2008'
             and datediff(month,wntystdate,'08/31/2008')>0
             and datediff(month,wntystdate,'08/31/2008') >=m.mnth)t
order by 1,2,3

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-09-22 : 09:40:49

check this too...,

select substring(prdsrl,1,1) as sprd
,plcycd
,datediff(month,wntystdate,'08/31/2008') as mnth
,count(*) as cnt
from test_Prd
WHERE wntystdate BETWEEN '01/01/2007' AND '08/31/2008'
and datediff(month,wntystdate,'08/31/2008')>0
group by substring(prdsrl,1,1)
,plcycd
,datediff(month,wntystdate,'08/31/2008')
order by sprd,plcycd,mnth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 09:46:19

quote:
Originally posted by VGuyz

check this too...,

select substring(prdsrl,1,1) as sprd
,plcycd
,datediff(month,wntystdate,'08/31/2008') as mnth
,count(*) as cnt
from test_Prd
WHERE wntystdate BETWEEN '01/01/2007' AND '08/31/2008'
and datediff(month,wntystdate,'08/31/2008')>0
group by substring(prdsrl,1,1)
,plcycd
,datediff(month,wntystdate,'08/31/2008')
order by sprd,plcycd,mnth

Did you read what OP asked for?
whats the difference b/w this query and original query posted by OP?
Where are you finding running total in above query?

scr2k8
Starting Member

3 Posts

Posted - 2008-09-22 : 12:17:51

This the output I will get from the query

sprd plcycd mnth cnt
------- --------- ------- ------
F QB 2 2
F QB 3 1
F QB 4 2
F QB 5 1
F QB 6 1
F QB 7 1
F SB 3 1
F SB 4 2
F SB 5 1
F SB 7 1
W QB 3 1
W QB 5 1
W QB 6 3

Now I need the output like below

sprd plcycd mnth cnt RunningTotal
---- ------ ------ ------ ---------

F QB 1 0 8
F QB 2 2 8
F QB 3 1 6
F QB 4 2 5
F QB 5 1 3
F QB 6 1 2
F QB 7 1 1
F SB 1 0 5
F SB 2 0 5
F SB 3 1 5
F SB 4 2 4
F SB 5 1 2
F SB 6 0 1
F SB 7 1 1
W QB 1 0 5
W QB 2 0 5
W QB 3 1 5
W QB 4 0 4
W QB 5 1 4
W QB 6 3 3

I hope this clear what the out put required

scr2k8
Starting Member

3 Posts

Posted - 2008-09-22 : 16:16:08

Here I am giving one more query which will get running totals but I am missing bold onces can any one suggest the solution.

SELECT a.sprd,a.plcycd,a.mnth,a.cnt,SUM(b.cnt)AS RunningTotal
from
(
SELECT substring(prdsrl,1,1) as sprd
,plcycd
,datediff(month,wntystdate,'08/31/2008') as mnth
,count(*) as cnt
from test_Prd
WHERE wntystdate BETWEEN '01/01/2007' AND '08/31/2008'
AND datediff(month,wntystdate,'08/31/2008')>0
GROUP BY substring(prdsrl,1,1),plcycd,datediff(month,wntystdate,'08/31/2008')
) a
CROSS JOIN
(
SELECT
substring(prdsrl,1,1) as sprd
,plcycd
,datediff(month,wntystdate,'08/31/2008') as mnth
,count(*) as cnt
from test_Prd
WHERE wntystdate BETWEEN '01/01/2007' AND '08/31/2008'
AND datediff(month,wntystdate,'08/31/2008')>0
GROUP BY substring(prdsrl,1,1),plcycd,datediff(month,wntystdate,'08/31/2008')
) b
WHERE (a.mnth<=b.mnth) and a.sprd =b.sprd and a.plcycd =b.plcycd
GROUP BY a.sprd,a.plcycd,a.mnth,a.cnt
ORDER BY a.sprd,a.plcycd,a.mnth

THE OUTPUT WITH ABOVE QUERY
sprd plcycd MNTH cnt RunningTotal
------- --------- ------- ------ ---------------
F QB 2 2 8
F QB 3 1 6
F QB 4 2 5
F QB 5 1 3
F QB 6 1 2
F QB 7 1 1
F SB 3 1 5
F SB 4 2 4
F SB 5 1 2
F SB 7 1 1
W QB 3 1 5
W QB 5 1 4
W QB 6 3 3

THE REQUIRED OUTPUT IS AS BELOW

sprd plcycd MNTH cnt RunningTotal
------- --------- ------- ------ ---------------
F QB 1 0 8
F QB 2 2 8
F QB 3 1 6
F QB 4 2 5
F QB 5 1 3
F QB 6 1 2
F QB 7 1 1
F SB 1 0 5
F SB 2 0 5
F SB 3 1 5
F SB 4 2 4
F SB 5 1 2
F SB 6 0 1
F SB 7 1 1
W QB 1 0 5
W QB 2 0 5
W QB 3 1 5
W QB 4 0 4
W QB 5 1 4
W QB 6 3 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 00:39:34

quote:
Originally posted by scr2k8

This the output I will get from the query

sprd plcycd mnth cnt
------- --------- ------- ------
F QB 2 2
F QB 3 1
F QB 4 2
F QB 5 1
F QB 6 1
F QB 7 1
F SB 3 1
F SB 4 2
F SB 5 1
F SB 7 1
W QB 3 1
W QB 5 1
W QB 6 3

Now I need the output like below

sprd plcycd mnth cnt RunningTotal
---- ------ ------ ------ ---------

F QB 1 0 8
F QB 2 2 8
F QB 3 1 6
F QB 4 2 5
F QB 5 1 3
F QB 6 1 2
F QB 7 1 1
F SB 1 0 5
F SB 2 0 5
F SB 3 1 5
F SB 4 2 4
F SB 5 1 2
F SB 6 0 1
F SB 7 1 1
W QB 1 0 5
W QB 2 0 5
W QB 3 1 5
W QB 4 0 4
W QB 5 1 4
W QB 6 3 3

I hope this clear what the out put required

didnt my solution work?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources