May 16, 2017 at 12:59 am
create table ##TestTable
(
IncidentId int,
File_date date,
ScheduleTime time,
ExceutedTime time,
Expected_Result int
)
insert into ##TestTable values(123,'05/14/2017','01:00','01:30',0)
insert into ##TestTable values(123,'05/14/2017','03:00','03:45',0)
insert into ##TestTable values(123,'05/14/2017','06:00','00:00',1)
insert into ##TestTable values(123,'05/15/2017','01:00','00:00',1)
insert into ##TestTable values(123,'05/15/2017','03:00','03:45',0)
insert into ##TestTable values(123,'05/15/2017','06:00','00:00',2)
insert into ##TestTable values(123,'05/16/2017','01:00','01:30',0)
insert into ##TestTable values(123,'05/16/2017','03:00','03:45',0)
insert into ##TestTable values(123,'05/16/2017','06:00','00:00',3)
insert into ##TestTable values(123,'05/17/2017','01:00','01:30',0)
insert into ##TestTable values(123,'05/17/2017','03:00','03:45',0)
insert into ##TestTable values(123,'05/17/2017','06:00','06:05',0)
I'm looking for calculating aging, for example one incident can have multiple schedules in a day, if one schedule failed in next day aging should be 1 and so on , I would like to see aging bucket 0-5days,6-10days,11-15days,16-20days,>20days
We have to consider both incident and schedule for calculating aging. Any help much appreciated
Thanks,
Dhana.
May 16, 2017 at 1:42 am
koti.raavi - Tuesday, May 16, 2017 12:59 AMcreate table ##TestTable
(
IncidentId int,
File_date date,
ScheduleTime time,
ExceutedTime time
)
insert into ##TestTable values(123,'05/14/2017','01:00','01:30')
insert into ##TestTable values(123,'05/14/2017','03:00','03:45')
insert into ##TestTable values(123,'05/14/2017','06:00','00:00')
insert into ##TestTable values(123,'05/15/2017','01:00','00:00')
insert into ##TestTable values(123,'05/15/2017','03:00','03:45')
insert into ##TestTable values(123,'05/15/2017','06:00','06:10')
insert into ##TestTable values(123,'05/16/2017','01:00','01:30')
insert into ##TestTable values(123,'05/16/2017','03:00','03:45')
insert into ##TestTable values(123,'05/16/2017','06:00','00:00')
insert into ##TestTable values(456,'05/14/2017','01:00','01:30')
insert into ##TestTable values(456,'05/14/2017','03:00','03:45')
insert into ##TestTable values(456,'05/14/2017','06:00','00:00')
insert into ##TestTable values(456,'05/15/2017','01:00','00:00')
insert into ##TestTable values(456,'05/15/2017','03:00','03:45')
insert into ##TestTable values(456,'05/15/2017','06:00','06:10')
insert into ##TestTable values(456,'05/16/2017','01:00','01:30')
insert into ##TestTable values(456,'05/16/2017','03:00','03:45')
insert into ##TestTable values(456,'05/16/2017','06:00','00:00')
I'm looking for calculating aging, for example one incident can have multiple schedules in a day, if one schedule failed in next day aging should be 1 and so on , I would like to see aging bucket 0-5days,6-10days,11-15days,16-20days,>20days
We have to consider both incident and schedule for calculating aging. Any help much appreciatedThanks,
Dhana.
Please post your expected results from the sample data.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 16, 2017 at 1:58 am
Added expected result column, please let me know if any questions, Thanks.
May 16, 2017 at 7:43 am
koti.raavi - Tuesday, May 16, 2017 1:58 AMAdded expected result column, please let me know if any questions, Thanks.
Thanks. There's this, but I think there's a more performant solution.SELECT IncidentId, File_date, ScheduleTime, Result,
n = CASE WHEN Failure = 0 THEN 0
ELSE DENSE_RANK() OVER(PARTITION BY IncidentID ORDER BY Failure DESC, rn1-rn2) END
FROM (
SELECT IncidentId, File_date, ScheduleTime, Failure, Result,
rn1 = ROW_NUMBER() OVER(PARTITION BY IncidentID ORDER BY File_Date, ScheduleTime),
rn2 = ROW_NUMBER() OVER(PARTITION BY IncidentID, Failure ORDER BY File_Date, ScheduleTime)
FROM #TestTable
CROSS APPLY (SELECT Failure = CASE WHEN ExecutedTime > '00:00:00.0000000' THEN 0 ELSE 1 END) x
) d
ORDER BY IncidentID, File_Date, ScheduleTime
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 16, 2017 at 8:11 am
great, Perfect Thanks!, it worked , Thanks Chris 🙂
May 16, 2017 at 8:33 am
koti.raavi - Tuesday, May 16, 2017 8:11 AMgreat, Perfect Thanks!, it worked , Thanks Chris 🙂
You're welcome, thanks for the feedback.
Hint - keep an eye on this page in case someone comes up with a more performant query.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 17, 2017 at 4:20 am
Hi, im having some issues with the result, please have a look new data below and let me know any questions
--Drop table #Test
create table #Test
(
IncidentId int,
File_date Date,
ScheduleTime time,
ExecutedTime time,
Result Int
)
Insert into #test values(217096,'2017-04-02','17:00:00.0000000','00:00:00.0000000',1)
Insert into #test values(217096,'2017-04-02','23:00:00.0000000','00:00:00.0000000',1)
Insert into #test values(217096,'2017-04-03','17:00:00.0000000','00:00:00.0000000',2)
Insert into #test values(217096,'2017-04-03','23:00:00.0000000','00:00:00.0000000',2)
Insert into #test values(217096,'2017-04-04','17:00:00.0000000','00:00:00.0000000',3)
Insert into #test values(217096,'2017-04-04','23:00:00.0000000','00:00:00.0000000',3)
Insert into #test values(217096,'2017-04-05','17:00:00.0000000','00:00:00.0000000',4)
Insert into #test values(217096,'2017-04-05','23:00:00.0000000','00:00:00.0000000',4)
Insert into #test values(217096,'2017-04-06','17:00:00.0000000','00:00:00.0000000',5)
Insert into #test values(217096,'2017-04-06','23:00:00.0000000','00:00:00.0000000',5)
Insert into #test values(217096,'2017-04-07','17:00:00.0000000','00:00:00.0000000',6)
Insert into #test values(217096,'2017-04-07','23:00:00.0000000','00:00:00.0000000',6)
Insert into #test values(217096,'2017-04-08','17:00:00.0000000','00:00:00.0000000',7)
Insert into #test values(217096,'2017-04-08','23:00:00.0000000','00:00:00.0000000',7)
Insert into #test values(217096,'2017-04-09','17:00:00.0000000','00:00:00.0000000',8)
Insert into #test values(217096,'2017-04-09','23:00:00.0000000','00:00:00.0000000',8)
Insert into #test values(217096,'2017-04-10','17:00:00.0000000','00:00:00.0000000',9)
Insert into #test values(217096,'2017-04-10','23:00:00.0000000','00:00:00.0000000',9)
Insert into #test values(217096,'2017-04-11','17:00:00.0000000','00:00:00.0000000',10)
Insert into #test values(217096,'2017-04-11','23:00:00.0000000','00:00:00.0000000',10)
Insert into #test values(217096,'2017-04-12','17:00:00.0000000','00:00:00.0000000',11)
Insert into #test values(217096,'2017-04-12','23:00:00.0000000','00:00:00.0000000',11)
Insert into #test values(217096,'2017-04-13','17:00:00.0000000','00:00:00.0000000',12)
Insert into #test values(217096,'2017-04-13','23:00:00.0000000','00:00:00.0000000',12)
Insert into #test values(217096,'2017-04-14','17:00:00.0000000','00:00:00.0000000',13)
Insert into #test values(217096,'2017-04-14','23:00:00.0000000','00:00:00.0000000',13)
Insert into #test values(217096,'2017-04-15','17:00:00.0000000','00:00:00.0000000',14)
Insert into #test values(217096,'2017-04-15','23:00:00.0000000','00:00:00.0000000',14)
Insert into #test values(217096,'2017-04-16','17:00:00.0000000','00:00:00.0000000',15)
Insert into #test values(217096,'2017-04-16','23:00:00.0000000','00:00:00.0000000',15)
Insert into #test values(217096,'2017-04-17','17:00:00.0000000','00:00:00.0000000',16)
Insert into #test values(217096,'2017-04-17','23:00:00.0000000','00:00:00.0000000',16)
Insert into #test values(217096,'2017-04-18','17:00:00.0000000','00:00:00.0000000',17)
Insert into #test values(217096,'2017-04-18','23:00:00.0000000','00:00:00.0000000',17)
Insert into #test values(217096,'2017-04-19','17:00:00.0000000','00:00:00.0000000',18)
Insert into #test values(217096,'2017-04-19','23:00:00.0000000','00:00:00.0000000',18)
Insert into #test values(217096,'2017-04-20','23:00:00.0000000','00:00:00.0000000',19)
Insert into #test values(217096,'2017-04-20','17:00:00.0000000','00:00:00.0000000',19)
Insert into #test values(217096,'2017-04-21','17:00:00.0000000','17:01:46.0000000',0)
Insert into #test values(217096,'2017-04-21','23:00:00.0000000','23:01:59.0000000',0)
Insert into #test values(217096,'2017-04-22','23:00:00.0000000','23:03:08.0000000',0)
Insert into #test values(217096,'2017-04-22','17:00:00.0000000','17:02:58.0000000',0)
Insert into #test values(217096,'2017-04-23','17:00:00.0000000','17:04:46.0000000',0)
Insert into #test values(217096,'2017-04-23','23:00:00.0000000','23:00:10.0000000',0)
Insert into #test values(217096,'2017-04-24','17:00:00.0000000','17:04:57.0000000',0)
Insert into #test values(217096,'2017-04-24','23:00:00.0000000','23:00:11.0000000',0)
Insert into #test values(217096,'2017-04-25','17:00:00.0000000','17:01:41.0000000',0)
Insert into #test values(217096,'2017-04-25','23:00:00.0000000','23:02:04.0000000',0)
Insert into #test values(217096,'2017-04-26','17:00:00.0000000','17:03:26.0000000',0)
Insert into #test values(217096,'2017-04-26','23:00:00.0000000','23:03:38.0000000',0)
Insert into #test values(217096,'2017-04-27','17:00:00.0000000','17:04:54.0000000',0)
Insert into #test values(217096,'2017-04-27','23:00:00.0000000','00:00:00.0000000',1)
Insert into #test values(217096,'2017-04-28','23:00:00.0000000','23:00:42.0000000',0)
Insert into #test values(217096,'2017-04-28','17:00:00.0000000','17:00:32.0000000',0)
Insert into #test values(217096,'2017-04-29','17:00:00.0000000','17:00:16.0000000',0)
Insert into #test values(217096,'2017-04-29','23:00:00.0000000','23:04:51.0000000',0)
Insert into #test values(217096,'2017-04-30','17:00:00.0000000','17:01:44.0000000',0)
Insert into #test values(217096,'2017-04-30','23:00:00.0000000','23:00:43.0000000',0)
Insert into #test values(217096,'2017-05-01','23:00:00.0000000','23:01:16.0000000',0)
Insert into #test values(217096,'2017-05-01','17:00:00.0000000','17:02:21.0000000',0)
Insert into #test values(217096,'2017-05-02','23:00:00.0000000','23:01:45.0000000',0)
May 17, 2017 at 5:16 am
The rows are marked exactly as I would expect. What would you expect to see?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 17, 2017 at 5:42 am
the query you have provided is failing due to multiple fail of incidents, I have added expected result column, please execute query once and let me know any questions. Thanks for your time
May 17, 2017 at 5:51 am
koti.raavi - Wednesday, May 17, 2017 5:42 AMthe query you have provided is failing due to multiple fail of incidents, I have added expected result column, please execute query once and let me know any questions. Thanks for your time
You want a new incident number for each day?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 17, 2017 at 5:52 am
ChrisM@Work - Wednesday, May 17, 2017 5:51 AMkoti.raavi - Wednesday, May 17, 2017 5:42 AMthe query you have provided is failing due to multiple fail of incidents, I have added expected result column, please execute query once and let me know any questions. Thanks for your time
You want a new incident number for each day?
This is different to the original requirement, where incident number spanned days:
insert into ##TestTable values(123,'05/14/2017','06:00','00:00',1)
insert into ##TestTable values(123,'05/15/2017','01:00','00:00',1)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 17, 2017 at 6:01 am
I just provided sample incident id and it is fixed, I have 43 incidents and having different schedules
did you get a chance to look new data I have provided?, let me know any questions thanks
May 17, 2017 at 6:13 am
ChrisM@Work - Wednesday, May 17, 2017 5:52 AMChrisM@Work - Wednesday, May 17, 2017 5:51 AMkoti.raavi - Wednesday, May 17, 2017 5:42 AMthe query you have provided is failing due to multiple fail of incidents, I have added expected result column, please execute query once and let me know any questions. Thanks for your time
You want a new incident number for each day?
This is different to the original requirement, where incident number spanned days:
insert into ##TestTable values(123,'05/14/2017','06:00','00:00',1)
insert into ##TestTable values(123,'05/15/2017','01:00','00:00',1)
yes It will span days, the one I have given also span days too, please see below
Insert into #test values(217096,'2017-04-02','17:00:00.0000000','00:00:00.0000000',1)
Insert into #test values(217096,'2017-04-02','23:00:00.0000000','00:00:00.0000000',1)
Insert into #test values(217096,'2017-04-03','17:00:00.0000000','00:00:00.0000000',2)
Insert into #test values(217096,'2017-04-03','23:00:00.0000000','00:00:00.0000000',2)
May 17, 2017 at 6:20 am
koti.raavi - Wednesday, May 17, 2017 6:13 AMChrisM@Work - Wednesday, May 17, 2017 5:52 AMChrisM@Work - Wednesday, May 17, 2017 5:51 AMkoti.raavi - Wednesday, May 17, 2017 5:42 AMthe query you have provided is failing due to multiple fail of incidents, I have added expected result column, please execute query once and let me know any questions. Thanks for your time
You want a new incident number for each day?
This is different to the original requirement, where incident number spanned days:
insert into ##TestTable values(123,'05/14/2017','06:00','00:00',1)
insert into ##TestTable values(123,'05/15/2017','01:00','00:00',1)
yes It will span days, the one I have given also span days too, please see below
Insert into #test values(217096,'2017-04-02','17:00:00.0000000','00:00:00.0000000',1)
Insert into #test values(217096,'2017-04-02','23:00:00.0000000','00:00:00.0000000',1)
Insert into #test values(217096,'2017-04-03','17:00:00.0000000','00:00:00.0000000',2)
Insert into #test values(217096,'2017-04-03','23:00:00.0000000','00:00:00.0000000',2)
In your first sample, a single incident number is assigned to the 14th and 15th of May.
Please confirm that you now wish to change this so that a new incident number is assigned for each day.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 17, 2017 at 6:29 am
yes correct i have mutiple incidents,
suppose 123 having 3schedules in a day and 217096 having 2schedules in day..these are jobs used to update data in database
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply