Date wise Group Query - 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 2008 Forums

Transact-SQL (2008)

Date wise Group Query

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-03-15 : 21:02:05

Hi,
Need a query to look after the child tables data and have to group the columns for total days in behalf of date.

TableA:
[ID] [StatusID] [Date] [PrevStatusID]
1 101 01-02-2012 0
1 102 15-02-2012 101
1 103 29-02-2012 102
1 105 15-03-2012 103
2 101 01-03-2012 0
3 101 01-03-2012 0
3 110 15-03-2012 101

From the above table need the output for Status wise Group. Where as Last status ID output will be from getdate() function.
Consider Getdate=16-03-2012. Then my result will be

[101] [102] [103] [105] [110]
43 14 15 1 1

For StatusID=1 and ID=1 Date=01-02-2012 and nextstatusID=102 and its date= 15-02-2012 Hence Total days=14
And StatusID=101 and ID=2 Date=01-03-2012 and no Next statusID, Hence consider GETDATE. Henve Total days=15
For StatusID=1 and ID=3 Date=01-03-20122 and nextstatusID=110 and its date= 15-03-2012 Hence Total days=14

So the result set as StatusID=101 is 14+15+14=43.

Please provide me the query to get this result.

Regards,
Kalai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 23:01:07

[code]

set dateformat dmy
declare @test table
(
[ID] int,
[StatusID] int,
[Date] datetime,
[PrevStatusID] int
)
insert @test
select 1, 101, '01-02-2012', 0 union all
select 1, 102, '15-02-2012', 101 union all
select 1, 103, '29-02-2012', 102 union all
select 1, 105, '15-03-2012', 103 union all
select 2, 101, '01-03-2012', 0 union all
select 3, 101, '01-03-2012', 0 union all
select 3, 110, '15-03-2012', 101

select *
from
(
select t1.StatusID,datediff(dd,t1.date,coalesce(t2.date,getdate())) as dayselapsed
from @test t1
left join @test t2
on t2.[PrevStatusID] = t1.StatusID
and t1.id = t2.id
)t
pivot (sum(dayselapsed) for StatusID IN ([101],[102],[103],[105]))p

101 102 103 105
-----------------------------
43 14 15 1

[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources