Find and print - 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

General SQL Server Forums

New to SQL Server Programming

Find and print

Author

Topic

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-04-04 : 13:01:51

Hi All
I am stuck with the problem and want your help, What I want
to do is I have a table checks which looks like-:

Items--Waitarea--Datecreated
1-------A1-------2011-10-12
1-------A1-------2011-10-13
1-------A1-------2011-10-14
1-------A1-------2011-10-15
1-------A2-------2011-10-16
1-------A2-------2011-10-18
2-------A2-------2011-10-19
2-------A2-------2011-11-17
2-------A2-------2011-12-17
3-------A2-------2012-01-17
3-------A2-------2012-01-18
3-------A3-------2012-01-19
3-------A1-------2012-01-20
4-------A2-------2012-01-21
4-------A2-------2012-01-22
4-------A2-------2012-01-31

What i need to find is items and datediff from current date
to created date with respect to wait area. You will get more clear with example

For Eg. For items 1 the wait area changes from A1 to A2 at
2011-10-16 and remains A2 so i need to find the date differeence from
today date to 2011-10-16

For item 2 that wait area remains same for entire period that is
A2 so datediff from today date to 2011-10-19(last created date of that item)

For item 3 that wait area A2 then it becomes A3 then becomes A1
so need datediff from today to 2012-01-19

For item 4 that wait area A2 remains for the entire period
so need datediff from today-2012-01-21

The expected output wanted
items---daycount
1-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2011-10-16
2-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2011-10-19
3-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2012-01-20
4-----datediff(day,checks.datecreated,getdate()--here checks.datecreated will be 2012-01-21

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-04-04 : 13:53:36

DECLARE @Checks Table(Items tinyint,WaitArea char(2),DateCreated date)

INSERT INTO @Checks
VALUES

(1,'A1','2011-10-12'),
(1,'A1','2011-10-13'),
(1,'A1','2011-10-14'),
(1,'A1','2011-10-15'),
(1,'A2','2011-10-16'),
(1,'A2','2011-10-18'),
(2,'A2','2011-10-19'),
(2,'A2','2011-11-17'),
(2,'A2','2011-12-17'),
(3,'A2','2012-01-17'),
(3,'A2','2012-01-18'),
(3,'A3','2012-01-19'),
(3,'A1','2012-01-20'),
(4,'A2','2012-01-21'),
(4,'A2','2012-01-22'),
(4,'A2','2012-01-31')

select Items,WaitArea,DateCreated
,DATEDIFF(day,DateCreated,getdate())
from
(

select Items,WaitArea,DateCreated
,DENSE_RANK() Over(PARTITION BY Items Order BY WaitArea desc) as Switch
,ROW_NUMBER() OVER(PARTITION BY Items,WaitArea Order BY DateCreated) as SwitchDate
from @Checks
) a

where Switch = 1 and SwitchDate = 1
order by 1,2,3

Jim

Everyday I learn something that somebody else already knew

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-04-04 : 14:06:13

But want only
Items and datefiff column in resultant output table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources