March 8, 2019 at 1:27 pm
I have the following query to select rows where the LAST_UPDATE_DATE field is getting records that have a date value greater than or equal to the last 7 days, which works great.
SELECT 'NEW ROW' AS 'ROW_TYPE', A.EMPLID, B.FIRST_NAME, B.LAST_NAME,
A.BANK_CD, A.ACCOUNT_NUM, ACCOUNT_TYPE, PRIORITY, A.LAST_UPDATE_DATE
FROM PS_DIRECT_DEPOSIT D
INNER JOIN PS_DIR_DEP_DISTRIB A ON A.EMPLID = D.EMPLID AND A.EFFDT = D.EFFDT
INNER JOIN PS_EMPLOYEES B ON B.EMPLID = A.EMPLID
WHERE B.EMPL_STATUS NOT IN ('T','R','D')
AND ((A.DEPOSIT_TYPE = 'P' AND A.AMOUNT_PCT = 100)
OR A.PRIORITY = 999
OR A.DEPOSIT_TYPE = 'B')
AND A.EFFDT = (SELECT MAX(A1.EFFDT)
FROM PS_DIR_DEP_DISTRIB A1
WHERE A1.EMPLID = A.EMPLID
AND A1.EFFDT <= GETDATE())
AND D.EFF_STATUS = 'A'
AND D.EFFDT = (SELECT MAX(D1.EFFDT)
FROM PS_DIRECT_DEPOSIT D1
WHERE D1.EMPLID = D.EMPLID
AND D1.EFFDT <= GETDATE())
AND A.LAST_UPDATE_DATE >= GETDATE() - 7
What I would like to add onto this is to also add the previous (2nd MAX) row per EMPLID, so that I can output the 'old' row (that was prior to the last update the latest row meeting above criteria),
along with the new row that I already am outputting in the query. The result would look something similar to this:
ROW_TYPE EMPLID FIRST_NAME LAST_NAME BANK_CD ACCOUNT_NUM ACCOUNT_TYPE PRIORITY LAST_UPDATE_DATE
NEW ROW 12345 JOHN SMITH 123548999 45234879 C 999 2019-03-06 00:00:00.000
OLD ROW 12345 JOHN SMITH 214080046 92178616 C 999 2018-10-24 00:00:00.000
NEW ROW 56399 CHARLES MASTER 785816167 84314314 C 999 2019-03-07 00:00:00.000
OLD ROW 56399 CHARLES MASTER 345761227 547352 C 999 2017-05-16 00:00:00.000So the EMPLID would be ordered by NEW ROW, followed by OLD ROW as shown above. In this example the 'NEW ROW' is getting the record that is within the past 7 days, as indicated by the [/code] would be ordered by NEW ROW, followed by OLD ROW as shown above. In this example the 'NEW ROW' is getting the record that is within the past 7 days, as indicated by the LAST_UPDATE_DATE.
I would like to get feedback on how to modify the query so I can also get the 'old' row (which is the max row that is less than the 'NEW' row retrieved above).
March 8, 2019 at 3:48 pm
Use a CTE/ROW_NUMBER(). Since you didn't provide consumable data, this isn't tested.
WITH CTE AS
(
SELECT
A.EMPLID
, B.FIRST_NAME
, B.LAST_NAME
, A.BANK_CD
, A.ACCOUNT_NUM
, ACCOUNT_TYPE
, [PRIORITY]
, A.LAST_UPDATE_DATE
, ROW_NUMBER() OVER(PARTITION BY A.EMPLID ORDER BY A.EFFDT, D.EFFDT) AS rn
, MAX(A.LAST_UPDATE_DATE) OVER(PARTITION BY A.EMPLID) AS MAX_UPDATE_DATE
FROM PS_DIRECT_DEPOSIT D
INNER JOIN PS_DIR_DEP_DISTRIB A
ON A.EMPLID = D.EMPLID AND A.EFFDT = D.EFFDT
INNER JOIN PS_EMPLOYEES B
ON B.EMPLID = A.EMPLID
WHERE B.EMPL_STATUS NOT IN ('T','R','D')
AND ((A.DEPOSIT_TYPE = 'P' AND A.AMOUNT_PCT = 100)
OR A.PRIORITY = 999
OR A.DEPOSIT_TYPE = 'B')
AND D.EFF_STATUS = 'A'
)
SELECT *
FROM CTE
WHERE rn = 2
AND MAX_UPDATE_DATE >= GETDATE() - 7
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 8, 2019 at 4:58 pm
Hi Drew, this seems to work great! I made a slight tweak to the final select to output rows 1 and 2 (the 'new' and 'old' rows). Question, is there a way to have a column named ROW_TYPE and output the value 'NEW VALUE' for rn = 1 and output 'OLD VALUE' for rn = 2 ? I wasn't sure if this is possible to do in a CTE...
Also just looking at the results of the inner query within the CTE, here is a sample of the results I am getting:
EMPLID FIRST_NAME LAST_NAME BANK_CD ACCOUNT_NUM ACCOUNT_TYPE PRIORITY LAST_UPDATE_DATE rn MAX_UPDATE_DATE
1234 JOHN SMITH 021301115 132077051 C 310 2014-03-05 00:00:00.000 1 2019-02-13 00:00:00.000
1234 JOHN SMITH 021301115 132077035 C 310 2014-03-05 00:00:00.000 2 2019-02-13 00:00:00.000
1234 JOHN SMITH 021301115 132077682 C 999 2015-11-26 00:00:00.000 3 2019-02-13 00:00:00.000
1234 JOHN SMITH 022701115 132074784 C 999 2019-02-13 00:00:00.000 4 2019-02-13 00:00:00.000
As you can see it is outputting rn (row number) 1 with the oldest LAST_UPDATE_DATE row, whereas I would expect the MAX LAST_UPDATE_DATE for this ID to be rn 1. Can it easily be reordered?
Thanks again,
Kevin
March 8, 2019 at 10:45 pm
kdrymer - Friday, March 8, 2019 4:58 PMHi Drew, this seems to work great! I made a slight tweak to the final select to output rows 1 and 2 (the 'new' and 'old' rows). Question, is there a way to have a column named ROW_TYPE and output the value 'NEW VALUE' for rn = 1 and output 'OLD VALUE' for rn = 2 ? I wasn't sure if this is possible to do in a CTE...Also just looking at the results of the inner query within the CTE, here is a sample of the results I am getting:
EMPLID FIRST_NAME LAST_NAME BANK_CD ACCOUNT_NUM ACCOUNT_TYPE PRIORITY LAST_UPDATE_DATE rn MAX_UPDATE_DATE
1234 JOHN SMITH 021301115 132077051 C 310 2014-03-05 00:00:00.000 1 2019-02-13 00:00:00.000
1234 JOHN SMITH 021301115 132077035 C 310 2014-03-05 00:00:00.000 2 2019-02-13 00:00:00.000
1234 JOHN SMITH 021301115 132077682 C 999 2015-11-26 00:00:00.000 3 2019-02-13 00:00:00.000
1234 JOHN SMITH 022701115 132074784 C 999 2019-02-13 00:00:00.000 4 2019-02-13 00:00:00.000As you can see it is outputting rn (row number) 1 with the oldest LAST_UPDATE_DATE row, whereas I would expect the MAX LAST_UPDATE_DATE for this ID to be rn 1. Can it easily be reordered?
Thanks again,
Kevin
If my understanding is correct . I think you need to include
rn in (1,2) in the below query
WITH CTE AS
(
SELECT
A.EMPLID
, B.FIRST_NAME
, B.LAST_NAME
, A.BANK_CD
, A.ACCOUNT_NUM
, ACCOUNT_TYPE
, [PRIORITY]
, A.LAST_UPDATE_DATE
, ROW_NUMBER() OVER(PARTITION BY A.EMPLID ORDER BY A.EFFDT, D.EFFDT) AS rn
, MAX(A.LAST_UPDATE_DATE) OVER(PARTITION BY A.EMPLID) AS MAX_UPDATE_DATE
FROM PS_DIRECT_DEPOSIT D
INNER JOIN PS_DIR_DEP_DISTRIB A
ON A.EMPLID = D.EMPLID AND A.EFFDT = D.EFFDT
INNER JOIN PS_EMPLOYEES B
ON B.EMPLID = A.EMPLID
WHERE B.EMPL_STATUS NOT IN ('T','R','D')
AND ((A.DEPOSIT_TYPE = 'P' AND A.AMOUNT_PCT = 100)
OR A.PRIORITY = 999
OR A.DEPOSIT_TYPE = 'B')
AND D.EFF_STATUS = 'A'
)
SELECT *
FROM CTE
WHERE rn in (1,2)
AND MAX_UPDATE_DATE >= GETDATE() - 7
Saravanan
March 11, 2019 at 9:59 am
saravanatn - Friday, March 8, 2019 10:45 PMkdrymer - Friday, March 8, 2019 4:58 PMHi Drew, this seems to work great! I made a slight tweak to the final select to output rows 1 and 2 (the 'new' and 'old' rows). Question, is there a way to have a column named ROW_TYPE and output the value 'NEW VALUE' for rn = 1 and output 'OLD VALUE' for rn = 2 ? I wasn't sure if this is possible to do in a CTE...Also just looking at the results of the inner query within the CTE, here is a sample of the results I am getting:
EMPLID FIRST_NAME LAST_NAME BANK_CD ACCOUNT_NUM ACCOUNT_TYPE PRIORITY LAST_UPDATE_DATE rn MAX_UPDATE_DATE
1234 JOHN SMITH 021301115 132077051 C 310 2014-03-05 00:00:00.000 1 2019-02-13 00:00:00.000
1234 JOHN SMITH 021301115 132077035 C 310 2014-03-05 00:00:00.000 2 2019-02-13 00:00:00.000
1234 JOHN SMITH 021301115 132077682 C 999 2015-11-26 00:00:00.000 3 2019-02-13 00:00:00.000
1234 JOHN SMITH 022701115 132074784 C 999 2019-02-13 00:00:00.000 4 2019-02-13 00:00:00.000As you can see it is outputting rn (row number) 1 with the oldest LAST_UPDATE_DATE row, whereas I would expect the MAX LAST_UPDATE_DATE for this ID to be rn 1. Can it easily be reordered?
Thanks again,
KevinIf my understanding is correct . I think you need to include
rn in (1,2) in the below query
WITH CTE AS
(
SELECT
A.EMPLID
, B.FIRST_NAME
, B.LAST_NAME
, A.BANK_CD
, A.ACCOUNT_NUM
, ACCOUNT_TYPE
, [PRIORITY]
, A.LAST_UPDATE_DATE
, ROW_NUMBER() OVER(PARTITION BY A.EMPLID ORDER BY A.EFFDT, D.EFFDT) AS rn
, MAX(A.LAST_UPDATE_DATE) OVER(PARTITION BY A.EMPLID) AS MAX_UPDATE_DATE
FROM PS_DIRECT_DEPOSIT D
INNER JOIN PS_DIR_DEP_DISTRIB A
ON A.EMPLID = D.EMPLID AND A.EFFDT = D.EFFDT
INNER JOIN PS_EMPLOYEES B
ON B.EMPLID = A.EMPLID
WHERE B.EMPL_STATUS NOT IN ('T','R','D')
AND ((A.DEPOSIT_TYPE = 'P' AND A.AMOUNT_PCT = 100)
OR A.PRIORITY = 999
OR A.DEPOSIT_TYPE = 'B')
AND D.EFF_STATUS = 'A'
)SELECT *
FROM CTE
WHERE rn in (1,2)
AND MAX_UPDATE_DATE >= GETDATE() - 7
I actually meant to use rn <= 2 rather than rn=2. I think it's simpler update if the number of rows required changes.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 11, 2019 at 10:06 am
kdrymer - Friday, March 8, 2019 4:58 PMHi Drew, this seems to work great! I made a slight tweak to the final select to output rows 1 and 2 (the 'new' and 'old' rows). Question, is there a way to have a column named ROW_TYPE and output the value 'NEW VALUE' for rn = 1 and output 'OLD VALUE' for rn = 2 ? I wasn't sure if this is possible to do in a CTE...Also just looking at the results of the inner query within the CTE, here is a sample of the results I am getting:
EMPLID FIRST_NAME LAST_NAME BANK_CD ACCOUNT_NUM ACCOUNT_TYPE PRIORITY LAST_UPDATE_DATE rn MAX_UPDATE_DATE
1234 JOHN SMITH 021301115 132077051 C 310 2014-03-05 00:00:00.000 1 2019-02-13 00:00:00.000
1234 JOHN SMITH 021301115 132077035 C 310 2014-03-05 00:00:00.000 2 2019-02-13 00:00:00.000
1234 JOHN SMITH 021301115 132077682 C 999 2015-11-26 00:00:00.000 3 2019-02-13 00:00:00.000
1234 JOHN SMITH 022701115 132074784 C 999 2019-02-13 00:00:00.000 4 2019-02-13 00:00:00.000As you can see it is outputting rn (row number) 1 with the oldest LAST_UPDATE_DATE row, whereas I would expect the MAX LAST_UPDATE_DATE for this ID to be rn 1. Can it easily be reordered?
Thanks again,
Kevin
This is why we ask for sample data and expected results. It makes it much harder to test without it. Just change the ORDER BY in the OVER clause to DESC instead of the default ASC where appropriate.
You can output the NEW/OLD VALUE in the CTE, but it's much simpler to do it in the main query based on the rn field. It's a simple CASE expression.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 13, 2019 at 7:16 am
You can try the below query. It wasn't tested precisely because you provided no sample data to work with:WITH CTE AS (
SELECT
A.EMPLID
, B.FIRST_NAME
, B.LAST_NAME
, A.BANK_CD
, A.ACCOUNT_NUM
, ACCOUNT_TYPE
, [PRIORITY]
, A.LAST_UPDATE_DATE
, ROW_NUMBER() OVER(PARTITION BY A.EMPLID ORDER BY A.EFFDT DESC, D.EFFDT DESC) AS rn
, MAX(A.LAST_UPDATE_DATE) OVER(PARTITION BY A.EMPLID) AS MAX_UPDATE_DATE
FROM PS_DIRECT_DEPOSIT D
INNER JOIN PS_DIR_DEP_DISTRIB A
ON A.EMPLID = D.EMPLID AND A.EFFDT = D.EFFDT
INNER JOIN PS_EMPLOYEES B
ON B.EMPLID = A.EMPLID
WHERE B.EMPL_STATUS NOT IN ('T','R','D')
AND D.EFF_STATUS = 'A'
AND (
(
A.DEPOSIT_TYPE = 'P'
AND
A.AMOUNT_PCT = 100
)
OR
A.PRIORITY = 999
OR
A.DEPOSIT_TYPE = 'B'
)
)
SELECT
CASE rn
WHEN 1 THEN 'NEW ROW'
WHEN 2 THEN 'OLD ROW'
END AS ROW_TYPE
, *
FROM CTE
WHERE rn <= 2
AND MAX_UPDATE_DATE >= GETDATE() - 7
ORDER BY EMPLID, rn;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 19, 2019 at 5:00 pm
Thanks all for the help!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply