September 27, 2021 at 4:57 pm
Hello,
Thanks if you can help. DDL below.
This is similar to problems usually solved with RANK but I have an additional requirement.
I want the most recent record within my date range and then the previous record prior to my date range. So essentially I need to dedupe within the date range and the get the next most recent row prior to my data range.
Required Results
MyIdentifier 1 needs grades A and D
MyIdentifier 2 needs grades C and F
MyIdentifier 3 needs grades F and C
MyIdentifier 4 needs grade A
DROP TABLE IF EXISTS #temptable
CREATE TABLE #temptable (MyIdentifier INT,LoadDateTime DATETIME,Grade CHAR(1))
INSERT INTO #temptable
VALUES
(1,'2021-09-26 16:17:42.037','A'),(1,'2021-09-25 11:17:42.037','C'),(1,'2021-09-22 11:17:42.037','D'),(1,'2020-04-25 11:17:42.037','F'),(2,'2021-09-25 12:17:42.037','C'),(2,'2021-09-24 10:17:42.037','A'),(2,'2021-09-20 11:17:42.037','F'),(2,'2021-04-25 11:17:42.037','B'),(3,'2021-09-26 10:17:42.037','F'),(3,'2021-09-21 11:17:42.037','C'),(4,'2021-09-24 10:17:42.037','A')
DECLARE @StartDate DATE = '2021-09-24',@EndDate DATE = '2021-09-27'
SELECT
*,RANK() OVER (PARTITION BY MyIdentifier ORDER BY LoadDateTime DESC) LoadDateTimeRank
FROM #temptable
ORDER BY MyIdentifier
September 27, 2021 at 5:20 pm
You could try using ROW_NUMBER twice. Once to find the most recent row within the MyIdentifier partition and then again to find the 2nd oldest row (also within the MyIdentifier partition)
with cte as (
select *,
row_number() over (partition by MyIdentifier order by LoadDateTime desc) rn_recent,
row_number() over (partition by MyIdentifier order by LoadDateTime) rn_old
from #temptable)
select *
from cte
where rn_recent=1
or rn_old=2;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 27, 2021 at 5:35 pm
That works with my sample data but if there are more than 2 records prior to the date range I don't think it will work. See below with additional sample data.
DROP TABLE IF EXISTS #temptable
CREATE TABLE #temptable (MyIdentifier INT,LoadDateTime DATETIME,Grade CHAR(1))
INSERT INTO #temptable
VALUES
(1,'2021-09-26 16:17:42.037','A'),(1,'2021-09-25 11:17:42.037','C'),(1,'2021-09-22 11:17:42.037','D'),(1,'2020-04-25 11:17:42.037','F'),(2,'2021-09-25 12:17:42.037','C'),(2,'2021-09-24 10:17:42.037','A'),(2,'2021-09-20 11:17:42.037','F'),(2,'2021-04-25 11:17:42.037','B'),(3,'2021-09-26 10:17:42.037','F'),(3,'2021-09-21 11:17:42.037','C'),(4,'2021-09-24 10:17:42.037','A')
--Additional data
INSERT INTO #temptable
VALUES
(1,'2019-01-26 16:17:42.037','X'),
(1,'2019-02-26 16:17:42.037','Y')
DECLARE @StartDate DATE = '2021-09-24',@EndDate DATE = '2021-09-27'
SELECT
*,RANK() OVER (PARTITION BY MyIdentifier ORDER BY LoadDateTime DESC) LoadDateTimeRank,
row_number() over (partition by MyIdentifier order by LoadDateTime desc) rn_recent,
row_number() over (partition by MyIdentifier order by LoadDateTime) rn_old
FROM #temptable
ORDER BY MyIdentifier,LoadDateTime DESC
;with cte as (
select *,
row_number() over (partition by MyIdentifier order by LoadDateTime desc) rn_recent,
row_number() over (partition by MyIdentifier order by LoadDateTime) rn_old
from #temptable )
select *
from cte
where rn_recent=1
or rn_old=2;
September 27, 2021 at 5:45 pm
I guess this works but its not particularly pleasing to me.
SELECT
*
FROM
(
SELECT
*
FROM
(
SELECT
*,RANK() OVER (PARTITION BY MyIdentifier ORDER BY LoadDateTime DESC) LoadDateTimeRank
FROM #temptable
) T1
WHERE
LoadDateTimeRank = 1 AND
LoadDateTime >= @StartDate AND LoadDateTime <= @EndDate
UNION ALL
SELECT
*
FROM
(
SELECT
*,RANK() OVER (PARTITION BY MyIdentifier ORDER BY LoadDateTime DESC) LoadDateTimeRank
FROM #temptable
WHERE
LoadDateTime < @StartDate
) T1
WHERE
LoadDateTimeRank = 1
) ugly
ORDER BY
MyIdentifier,
LoadDateTime DESC
September 27, 2021 at 6:08 pm
O sorry, I was thinking you meant 2nd from the end. I seemed to have missed the date range part too
declare @StartDate DATE = '2021-09-24', @EndDate DATE = '2021-09-27'
;with cte as (
select top 1 with ties *
from #temptable
where LoadDateTime between @StartDate and @EndDate
order by rank() over (partition by MyIdentifier order by LoadDateTime desc))
select c.*, v.mx_grade
from cte c
outer apply (select top 1 Grade
from #temptable t
where c.MyIdentifier=t.MyIdentifier
and LoadDateTime < @StartDate
order by LoadDateTime desc) v(mx_grade);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply