January 31, 2017 at 8:54 am
Hi guys!
So I am stuck! I am trying to find prior line of service and I cannot come up with an appropriate script to do that. Here is an example of what I am trying to do:
| ID | FIRST_NAME | LAST_NAME | START DATE | END DATE | status | comment |
| 1234567 | JANE | DOE | 2016-09-06 | 2017-01-31 | TERMINATED | for this line |
| 1234567 | JANE | DOE | 2015-09-22 | 2016-09-05 | ACTIVE | I'd like to get this line and display both |
| 1234567 | JANE | DOE | 2015-09-08 | 2015-09-21 | TERMINATED | for this line |
| 1234567 | JANE | DOE | 2014-10-06 | 2015-09-07 | ACTIVE | I'd like to get this line and display both |
| 9998888 | NANCY | FUENTES | 2014-11-20 | 2017-01-31 | TERMINATED | for this line |
| 9998888 | NANCY | FUENTES | 2014-09-02 | 2014-11-19 | ON LEAVE | I'd like to get this line and display both |
| 9998888 | NANCY | FUENTES | 2009-09-08 | 2014-09-01 | ACTIVE | BUT NOT THIS ONE |
| 9998888 | NANCY | FUENTES | 2009-08-01 | 2009-09-07 | ACTIVE | AND NOT THIS ONE |
I am completely out of ideas. Do you guys have any script and willing to share that displays, for each terminated line, the line prior to the terminated one?
Any help would be greatly appreciated:)
Thank you, Monika.
January 31, 2017 at 9:00 am
Could you provide what your output is meant to look like. I don't really understand what you mean by "for this line". For this line you want to do what...?? Then you have another line that states "I'd like to get this line and display both" Both what?
DDL and DLM would be nice as well.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 31, 2017 at 9:04 am
I'm assuming that your column [START DATE] is what determines the order on a Per Id basis;
here's soemthing i slapped together, does this get you closer?
i had to use row_number in order to be able to compare "previous"
;WITH MySampleTable([ID],[FIRST_NAME],[LAST_NAME],[START DATE],[END DATE],[status],[comment])
AS
(
SELECT '1234567','JANE','DOE','2016-09-06','2017-01-31','TERMINATED','for this line' UNION ALL
SELECT '1234567','JANE','DOE','2015-09-22','2016-09-05','ACTIVE','I''d like to get this line and display both' UNION ALL
SELECT '1234567','JANE','DOE','2015-09-08','2015-09-21','TERMINATED','for this line' UNION ALL
SELECT '1234567','JANE','DOE','2014-10-06','2015-09-07','ACTIVE','I''d like to get this line and display both' UNION ALL
SELECT '9998888','NANCY','FUENTES','2014-11-20','2017-01-31','TERMINATED','for this line' UNION ALL
SELECT '9998888','NANCY','FUENTES','2014-09-02','2014-11-19','ON LEAVE','I''d like to get this line and display both' UNION ALL
SELECT '9998888','NANCY','FUENTES','2009-09-08','2014-09-01','ACTIVE','BUT NOT THIS ONE' UNION ALL
SELECT '9998888','NANCY','FUENTES','2009-08-01','2009-09-07','ACTIVE','AND NOT THIS ONE'
),WithRowNum
AS
(
SELECT row_number() Over(partition By ID Order By [START DATE]) AS RW, * FROM MySampleTable
)
SELECT * FROM WithRowNum T1
INNER JOIN WithRowNum T2
ON T1.ID = T2.ID
AND T1.[START DATE] < T2.[START DATE]
AND T1.[RW] + 1 = T2.[RW]
Lowell
January 31, 2017 at 9:12 am
Thom A - Tuesday, January 31, 2017 9:00 AMCould you provide what your output is meant to look like. I don't really understand what you mean by "for this line". For this line you want to do what...?? Then you have another line that states "I'd like to get this line and display both" Both what?DDL and DLM would be nice as well.
Hi Thom,
Yes, output should look like this:
| ID | FIRST_NAME | LAST_NAME | START DATE | END DATE | status | comment |
| 1234567 | JANE | DOE | 2016-09-06 | 2017-01-31 | TERMINATED | for this line |
| 1234567 | JANE | DOE | 2015-09-22 | 2016-09-05 | ACTIVE | I'd like to get this line and display both |
| 1234567 | JANE | DOE | 2015-09-08 | 2015-09-21 | TERMINATED | for this line |
| 1234567 | JANE | DOE | 2014-10-06 | 2015-09-07 | ACTIVE | I'd like to get this line and display both |
| 9998888 | NANCY | FUENTES | 2014-11-20 | 2017-01-31 | TERMINATED | for this line |
| 9998888 | NANCY | FUENTES | 2014-09-02 | 2014-11-19 | ON LEAVE | I'd like to get this line and display both |
the following 2 lines should not be displayed
| 9998888 | NANCY | FUENTES | 2009-09-08 | 2014-09-01 | ACTIVE | BUT NOT THIS ONE |
| 9998888 | NANCY | FUENTES | 2009-08-01 | 2009-09-07 | ACTIVE | AND NOT THIS ONE |
I'd basically like the script to look at each line of service and look for any rows with Status=TERMINATED, if line found, display that line and the one prior to this line. For example, NANCY FUENTES has 4 lines of service, my top line has status=TERMINATED, I would need to display that line and the one prior to it, but not lines 3 and 4:
| 9998888 | NANCY | FUENTES | 2009-09-08 | 2014-09-01 | ACTIVE | BUT NOT THIS ONE |
| 9998888 | NANCY | FUENTES | 2009-08-01 | 2009-09-07 | ACTIVE | AND NOT THIS ONE |
January 31, 2017 at 9:17 am
Lowell - Tuesday, January 31, 2017 9:04 AMI'm assuming that your column [START DATE] is what determines the order on a Per Id basis;
here's soemthing i slapped together, does this get you closer?
i had to use row_number in order to be able to compare "previous"
;WITH MySampleTable([ID],[FIRST_NAME],[LAST_NAME],[START DATE],[END DATE],[status],[comment])
AS
(
SELECT '1234567','JANE','DOE','2016-09-06','2017-01-31','TERMINATED','for this line' UNION ALL
SELECT '1234567','JANE','DOE','2015-09-22','2016-09-05','ACTIVE','I''d like to get this line and display both' UNION ALL
SELECT '1234567','JANE','DOE','2015-09-08','2015-09-21','TERMINATED','for this line' UNION ALL
SELECT '1234567','JANE','DOE','2014-10-06','2015-09-07','ACTIVE','I''d like to get this line and display both' UNION ALL
SELECT '9998888','NANCY','FUENTES','2014-11-20','2017-01-31','TERMINATED','for this line' UNION ALL
SELECT '9998888','NANCY','FUENTES','2014-09-02','2014-11-19','ON LEAVE','I''d like to get this line and display both' UNION ALL
SELECT '9998888','NANCY','FUENTES','2009-09-08','2014-09-01','ACTIVE','BUT NOT THIS ONE' UNION ALL
SELECT '9998888','NANCY','FUENTES','2009-08-01','2009-09-07','ACTIVE','AND NOT THIS ONE'
),WithRowNum
AS
(
SELECT row_number() Over(partition By ID Order By [START DATE]) AS RW, * FROM MySampleTable
)
SELECT * FROM WithRowNum T1
INNER JOIN WithRowNum T2
ON T1.ID = T2.ID
AND T1.[START DATE] < T2.[START DATE]
AND T1.[RW] + 1 = T2.[RW]
Hi Lowell, thanks so much. Its a good start for me. Would you have a suggestion as to how substitute the actual values to whatever the data in the table (this table I am working with has millions of rows 🙁 (I only provided a small sample). I was just clarifying to Thom what it is exactly I need to display:
I'd basically like the script to look at each line of service and look for any rows with Status=TERMINATED, if line found, display that line and the one prior to this line. For example, NANCY FUENTES has 4 lines of service, my top line has status=TERMINATED, I would need to display that line and the one prior to it,
| 9998888 | NANCY | FUENTES | 2014-11-20 | 2017-01-31 | TERMINATED | for this line |
| 9998888 | NANCY | FUENTES | 2014-09-02 | 2014-11-19 | ON LEAVE | I'd like to get this line and display both |
but not lines 3 and 4:
| 9998888 | NANCY | FUENTES | 2009-09-08 | 2014-09-01 | ACTIVE | BUT NOT THIS ONE |
| 9998888 | NANCY | FUENTES | 2009-08-01 | 2009-09-07 | ACTIVE | AND NOT THIS ONE |
Thank you guy:)
January 31, 2017 at 9:19 am
Using Lowell's as a start maybe:;WITH MySampleTable([ID],[FIRST_NAME],[LAST_NAME],[START DATE],[END DATE],[status],[comment])
AS
(
SELECT '1234567','JANE','DOE','2016-09-06','2017-01-31','TERMINATED','for this line' UNION ALL
SELECT '1234567','JANE','DOE','2015-09-22','2016-09-05','ACTIVE','I''d like to get this line and display both' UNION ALL
SELECT '1234567','JANE','DOE','2015-09-08','2015-09-21','TERMINATED','for this line' UNION ALL
SELECT '1234567','JANE','DOE','2014-10-06','2015-09-07','ACTIVE','I''d like to get this line and display both' UNION ALL
SELECT '9998888','NANCY','FUENTES','2014-11-20','2017-01-31','TERMINATED','for this line' UNION ALL
SELECT '9998888','NANCY','FUENTES','2014-09-02','2014-11-19','ON LEAVE','I''d like to get this line and display both' UNION ALL
SELECT '9998888','NANCY','FUENTES','2009-09-08','2014-09-01','ACTIVE','BUT NOT THIS ONE' UNION ALL
SELECT '9998888','NANCY','FUENTES','2009-08-01','2009-09-07','ACTIVE','AND NOT THIS ONE' ),WithRowNum
AS
(
SELECT row_number() Over(partition By ID Order By [START DATE]) AS RW,
*,
LEAD(status) OVER (partition By ID Order By [START DATE]) AS NextStatus
FROM MySampleTable
)
SELECT [ID],
[FIRST_NAME],
[LAST_NAME],
[START DATE],
[END DATE],
[status],
[comment]
FROM WithRowNum
WHERE status = 'Terminated'
OR NextStatus = 'Terminated'
ORDER BY ID,
[START DATE];
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 31, 2017 at 9:20 am
WITH MySampleTable([ID],[FIRST_NAME],[LAST_NAME],[START_DATE],[END_DATE],[status],[comment])
AS
(
SELECT '1234567','JANE','DOE','2016-09-06','2017-01-31','TERMINATED','for this line' UNION ALL
SELECT '1234567','JANE','DOE','2015-09-22','2016-09-05','ACTIVE','I''d like to get this line and display both' UNION ALL
SELECT '1234567','JANE','DOE','2015-09-08','2015-09-21','TERMINATED','for this line' UNION ALL
SELECT '1234567','JANE','DOE','2014-10-06','2015-09-07','ACTIVE','I''d like to get this line and display both' UNION ALL
SELECT '9998888','NANCY','FUENTES','2014-11-20','2017-01-31','TERMINATED','for this line' UNION ALL
SELECT '9998888','NANCY','FUENTES','2014-09-02','2014-11-19','ON LEAVE','I''d like to get this line and display both' UNION ALL
SELECT '9998888','NANCY','FUENTES','2009-09-08','2014-09-01','ACTIVE','BUT NOT THIS ONE' UNION ALL
SELECT '9998888','NANCY','FUENTES','2009-08-01','2009-09-07','ACTIVE','AND NOT THIS ONE'
)
SELECT
ID
, FIRST_NAME
, LAST_NAME
, START_DATE
, END_DATE
, status
, CASE status
WHEN 'TERMINATED' THEN LAG(status) OVER (PARTITION BY ID ORDER BY START_DATE)
ELSE ''
END AS PriorStatus
, comment
FROM MySampleTable
ORDER BY
ID
, START_DATE
John
January 31, 2017 at 9:25 am
Use LEAD to find the next value of the status and then pull records where the current or next status is 'Terminated'.
;
WITH CTE AS
(
SELECT *, next_status = LEAD([status]) OVER(PARTITION BY [ID] ORDER BY [START DATE])
FROM
( VALUES
('1234567','JANE','DOE','2016-09-06','2017-01-31','TERMINATED','for this line'),
('1234567','JANE','DOE','2015-09-22','2016-09-05','ACTIVE','I''d like to get this line and display both'),
('1234567','JANE','DOE','2015-09-08','2015-09-21','TERMINATED','for this line'),
('1234567','JANE','DOE','2014-10-06','2015-09-07','ACTIVE','I''d like to get this line and display both'),
('9998888','NANCY','FUENTES','2014-11-20','2017-01-31','TERMINATED','for this line'),
('9998888','NANCY','FUENTES','2014-09-02','2014-11-19','ON LEAVE','I''d like to get this line and display both'),
('9998888','NANCY','FUENTES','2009-09-08','2014-09-01','ACTIVE','BUT NOT THIS ONE'),
('9998888','NANCY','FUENTES','2009-08-01','2009-09-07','ACTIVE','AND NOT THIS ONE')
) MySampleTable([ID],[FIRST_NAME],[LAST_NAME],[START DATE],[END DATE],[status],[comment])
)
SELECT *
FROM CTE
WHERE [status] = 'Terminated'
OR next_status = 'Terminated'
ORDER BY ID, [START DATE] DESC
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 31, 2017 at 9:40 am
You guys are awesome !!!! It worked, many thanks to each andevery one of you great people J
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply