October 11, 2017 at 6:11 am
Hi All,
Hello,
I have an issue in finding out the date range and id previous to an event. here I want to pull id and dates(start and end dates) before a status urgent is high. for example id 111 urgent date range is '2017-01-04 00:00:00.000' and 2017-03-07 00:00:00.000' , now I need to check if the previous status is high and if it high I need to pull id and date ranges of high . i.e; here '2016-10-31 00:00:00.000' and '2017-01-03 00:00:00.000' . Similar way for id 222 date range for urgent is 2017-01-04 00:00:00.000 and '2017-03-07 00:00:00.000' and prior status is medium i.e; not high I not need to return this id and dates. so my requirement is I need to check for current status is intensive and if very previous status is high I need to pull data or else empty. here I will only get 111 details. let me know if it is not clear.
Thanks in advance. below is the sample data.
declare @stats table
(id int,
status varchar(10),
startdate datetime,
enddate datetime
)
insert
into @stats values (111,'medium','2014-06-01 00:00:00.000','2015-05-13 00:00:00.000')
insert into @stats values (111,'high','2016-10-31 00:00:00.000','2017-01-03 00:00:00.000')
insert into @stats values (111,'urgent','2017-01-04 00:00:00.000','2017-03-07 00:00:00.000')
insert into @stats values (222,'high','2015-08-13 00:00:00.000','2015-05-13 00:00:00.000')
insert into @stats values (222,'medium','2016-10-31 00:00:00.000','2017-01-03 00:00:00.000')
Insert into @stats values (222,'urgent','2017-01-04 00:00:00.000','2017-03-07 00:00:00.000')
Select * From @stats
October 11, 2017 at 6:18 am
There are a couple of things that aren't clear:
1.) Are time spans ever overlapping?
2.) Are you seeking only data where an ID has an "urgent" status?
3.) Does any ID value have more than one "urgent" status?
It would be very helpful for you to specify the exact output you would expect given your sample data.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 11, 2017 at 6:38 am
Just in case you want what I think you want:CREATE TABLE #stats (
id int,
[status] varchar(10),
startdate datetime,
enddate datetime
);
INSERT INTO #stats
VALUES (111,'medium','2014-06-01 00:00:00.000','2015-05-13 00:00:00.000'),
(111,'high','2016-10-31 00:00:00.000','2017-01-03 00:00:00.000'),
(111,'urgent','2017-01-04 00:00:00.000','2017-03-07 00:00:00.000'),
(222,'high','2015-08-13 00:00:00.000','2015-05-13 00:00:00.000'),
(222,'medium','2016-10-31 00:00:00.000','2017-01-03 00:00:00.000'),
(222,'urgent','2017-01-04 00:00:00.000','2017-03-07 00:00:00.000');
WITH LEAD_VALUES AS (
SELECT id, [status], startdate, enddate,
CASE [status] WHEN 'high' THEN LEAD([status], 1, NULL) OVER(PARTITION BY id ORDER BY startdate, enddate) ELSE NULL END AS LEAD_STATUS
FROM #stats
)
SELECT L.id
, L.[status]
, L.startdate
, L.enddate
FROM LEAD_VALUES AS L
WHERE L.[status] = 'urgent'
OR
(
L.[status] = 'high'
AND
L.LEAD_STATUS = 'urgent'
)
ORDER BY L.id, L.startdate, L.enddate;
DROP TABLE #stats;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 11, 2017 at 7:36 am
Thank you so much for your reply,
I only need high id's and dates if there anything available previous to urgent. in this case I need only 111, high and dates. Thank you !
October 11, 2017 at 8:08 am
dhanekulakalyan - Wednesday, October 11, 2017 7:36 AMThank you so much for your reply,I only need high id's and dates if there anything available previous to urgent. in this case I need only 111, high and dates. Thank you !
Replacing yourSelect * From @stats
with something like select id, previousstatus as status, previousstartdate as startdate, previousenddate as enddate
from
(select
id,
status,
lag(status,1,'na') over (partition by id order by startdate) as previousstatus,
lag(startdate,1) over (partition by id order by startdate) as previousstartdate,
lag(enddate,1) over (partition by id order by enddate) as previousenddate
from @stats
) a
where status = 'urgent' and previousstatus = 'high'
should get you
id status startdate enddate
111 high 2016-10-31 00:00:00.000 2017-01-03 00:00:00.000
October 11, 2017 at 5:25 pm
sgmunson - Wednesday, October 11, 2017 6:38 AMJust in case you want what I think you want:CREATE TABLE #stats (
id int,
[status] varchar(10),
startdate datetime,
enddate datetime
);
INSERT INTO #stats
VALUES (111,'medium','2014-06-01 00:00:00.000','2015-05-13 00:00:00.000'),
(111,'high','2016-10-31 00:00:00.000','2017-01-03 00:00:00.000'),
(111,'urgent','2017-01-04 00:00:00.000','2017-03-07 00:00:00.000'),
(222,'high','2015-08-13 00:00:00.000','2015-05-13 00:00:00.000'),
(222,'medium','2016-10-31 00:00:00.000','2017-01-03 00:00:00.000'),
(222,'urgent','2017-01-04 00:00:00.000','2017-03-07 00:00:00.000');WITH LEAD_VALUES AS (
SELECT id, [status], startdate, enddate,
CASE [status] WHEN 'high' THEN LEAD([status], 1, NULL) OVER(PARTITION BY id ORDER BY startdate, enddate) ELSE NULL END AS LEAD_STATUS
FROM #stats
)
SELECT L.id
, L.[status]
, L.startdate
, L.enddate
FROM LEAD_VALUES AS L
WHERE L.[status] = 'urgent'
OR
(
L.[status] = 'high'
AND
L.LEAD_STATUS = 'urgent'
)
ORDER BY L.id, L.startdate, L.enddate;DROP TABLE #stats;
Thank you and this works for me, I changed LEAD to LAG because I need previous status. Thanks again for help
October 11, 2017 at 5:28 pm
SQLPirate - Wednesday, October 11, 2017 8:08 AMdhanekulakalyan - Wednesday, October 11, 2017 7:36 AMThank you so much for your reply,I only need high id's and dates if there anything available previous to urgent. in this case I need only 111, high and dates. Thank you !
Replacing your
Select * From @statswith something like
select id, previousstatus as status, previousstartdate as startdate, previousenddate as enddate
from
(select
id,
status,
lag(status,1,'na') over (partition by id order by startdate) as previousstatus,
lag(startdate,1) over (partition by id order by startdate) as previousstartdate,
lag(enddate,1) over (partition by id order by enddate) as previousenddate
from @stats
) a
where status = 'urgent' and previousstatus = 'high'should get you
id status startdate enddate
111 high 2016-10-31 00:00:00.000 2017-01-03 00:00:00.000
Thank you for your help ,will use lag for this scenario. Thanks again
October 12, 2017 at 8:07 am
dhanekulakalyan - Wednesday, October 11, 2017 5:28 PMSQLPirate - Wednesday, October 11, 2017 8:08 AMdhanekulakalyan - Wednesday, October 11, 2017 7:36 AMThank you so much for your reply,I only need high id's and dates if there anything available previous to urgent. in this case I need only 111, high and dates. Thank you !
Replacing your
Select * From @statswith something like
select id, previousstatus as status, previousstartdate as startdate, previousenddate as enddate
from
(select
id,
status,
lag(status,1,'na') over (partition by id order by startdate) as previousstatus,
lag(startdate,1) over (partition by id order by startdate) as previousstartdate,
lag(enddate,1) over (partition by id order by enddate) as previousenddate
from @stats
) a
where status = 'urgent' and previousstatus = 'high'should get you
id status startdate enddate
111 high 2016-10-31 00:00:00.000 2017-01-03 00:00:00.000Thank you for your help ,will use lag for this scenario. Thanks again
Try changing your perspective. You need three fields from one record ('high') and only one field from the other record ('urgent') and one field can come from either record (id), so why are you using the 'urgent' record as your reference instead of using the 'high' record?
select id, status, startdate, enddate
from
(select
id,
status,
lead(status,1,'na') over (partition by id order by startdate) as nextstatus,
startdate,
enddate
from @stats
) a
where status = 'high' and nextstatus = 'urgent'
By changing the reference to the record with the most fields we want, we now only need to do one lookup on another record instead of three.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 12, 2017 at 8:30 am
drew.allen - Thursday, October 12, 2017 8:07 AMTry changing your perspective. You need three fields from one record ('high') and only one field from the other record ('urgent') and one field can come from either record (id), so why are you using the 'urgent' record as your reference instead of using the 'high' record?
select id, status, startdate, enddate
from
(select
id,
status,
lead(status,1,'na') over (partition by id order by startdate) as nextstatus,
startdate,
enddate
from @stats
) a
where status = 'high' and nextstatus = 'urgent'By changing the reference to the record with the most fields we want, we now only need to do one lookup on another record instead of three.
Drew
That's a very good point and a much better solution.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply