Help On Calculating Aging
- koti.raavi - Wednesday, May 17, 2017 6:29 AM
This doesn't really answer the question...
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 - ChrisM@Work - Wednesday, May 17, 2017 7:02 AM
if possible provide query for latest data I have provided that would be great, thanks
- koti.raavi - Wednesday, May 17, 2017 7:38 AM
No problem
SELECT *, dr = CASE WHEN Failure = 1 THEN DENSE_RANK() OVER(PARTITION BY IncidentID, Grp ORDER BY File_Date) ELSE 0 END
FROM (
SELECT *, Grp = rn1-rn2
FROM (
SELECT *,
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 #test t
CROSS APPLY (SELECT Failure = CASE WHEN ExecutedTime > '00:00:00.0000000' THEN 0 ELSE 1 END) x
) d1
) d2
ORDER BY File_Date, ScheduleTime“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 - ChrisM@Work - Wednesday, May 17, 2017 7:55 AM
it looks good but having one issue, please run below update statement
UPDATE A
SET ExecutedTime='00:00:00.0000000'
From #Test A Where File_date='2017-04-28'
For 2017-04-28 date and schedule 17:00:00:00 having aging 2 but it should have one.
the reason for 1 is it is successfully executed on 2017-04-28 (17:00:00:00). Thank you Chris.
I have attached pic for this. - koti.raavi - Wednesday, May 17, 2017 8:20 AMChrisM@Work - Wednesday, May 17, 2017 7:55 AM
it looks good but having one issue, please run below update statement
UPDATE A
SET ExecutedTime='00:00:00.0000000'
From #Test A Where File_date='2017-04-28'
For 2017-04-28 date and schedule 17:00:00:00 having aging 2 but it should have one.
the reason for 1 is it is successfully executed on 2017-04-28 (17:00:00:00). Thank you Chris.
I have attached pic for this.Please compare the logic of your update statement with "it is successfully executed on 2017-04-28".
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
Apologies,
For 2017-04-27 date having two schedules that is 17:00 and 23:00 , 17:00 is executed successfully - result is 0 and 23:00 is not executed- result is 1
For 2017-04-28 schedule 17:00 is not executed the result should be 1 but it showing as "2" after running update statement provided above, hope this helps! thanks! - koti.raavi - Wednesday, May 17, 2017 8:43 AM
It looks consistent and correct to me:
217096 2017-04-27 23:00:00.0000000 00:00:00.0000000 1 1 52 39 13 1
217096 2017-04-28 17:00:00.0000000 00:00:00.0000000 0 1 53 40 13 2
217096 2017-04-28 23:00:00.0000000 00:00:00.0000000 0 1 54 41 13 2“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
Nope,why last column data is number 2 for 17:00:00.000000 schedule on 2017-04-28 it should be number 1 beacause it is successfully on 2017-04-27 for 17:00:00.00000 schedule,thanks
- koti.raavi - Wednesday, May 17, 2017 9:41 AM
Why is the rule different for this specific data point? It contradicts the rules for the previous group - increment the number on each new day.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
sorry looks like some confusion, rule is same ,the code is fine for incident having one schedule
but the problem with multiple schedule , when im using query for real data im finding more scenarios,sorry for trouble.Thanks
- koti.raavi - Wednesday, May 17, 2017 9:54 AM
Change your sample data to reflect your observations with the real data, and I'll be able to help.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 - koti.raavi - Wednesday, May 17, 2017 9:41 AM
late to this thread...and kudos to Chris for his patience.
is this the result set you are looking for?+--------+------------+------------------+------------------+---+---+----+----+----+---+
| 217096 | 2017-04-27 | 17:00:00.0000000 | 17:04:54.0000000 | 0 | 0 | 26 | 7 | 19 | 0 |
| 217096 | 2017-04-27 | 23:00:00.0000000 | 00:00:00.0000000 | 1 | 1 | 26 | 20 | 6 | 1 |
| 217096 | 2017-04-28 | 17:00:00.0000000 | 00:00:00.0000000 | 0 | 1 | 27 | 20 | 7 | 1 |
| 217096 | 2017-04-28 | 23:00:00.0000000 | 00:00:00.0000000 | 0 | 1 | 27 | 21 | 6 | 2 |
| 217096 | 2017-04-29 | 17:00:00.0000000 | 17:00:16.0000000 | 0 | 0 | 28 | 8 | 20 | 0 |
+--------+------------+------------------+------------------+---+---+----+----+----+---+________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Yes correct Livingston, you and chris are masters,kudos to both of you. thanks for your time to read all chat content.????
- koti.raavi - Wednesday, May 17, 2017 10:37 AM
a small amendment to Chris's code will deliver then....unless you add other requirements !
SELECT *, dr = CASE WHEN Failure = 1 THEN DENSE_RANK() OVER(PARTITION BY IncidentID, Grp ORDER BY File_Date) ELSE 0 END
FROM (
SELECT *, Grp = rn1-rn2
FROM (
SELECT *,
rn1 = ROW_NUMBER() OVER(PARTITION BY IncidentID, ScheduleTime ORDER BY File_Date ),
rn2 = ROW_NUMBER() OVER(PARTITION BY IncidentID, ScheduleTime, Failure ORDER BY File_Date)
FROM #test t
CROSS APPLY (SELECT Failure = CASE WHEN ExecutedTime > '00:00:00.0000000' THEN 0 ELSE 1 END) x
) d1
) d2
ORDER BY File_Date, ScheduleTime________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day - J Livingston SQL - Wednesday, May 17, 2017 10:44 AM
Nice catch G!
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply