I have a problem with the answered question from this question
Can you find the solution?
It's wrong result rn
Column if the data chr
like this one
WITH chr AS (
SELECT 581827 AS custno, '2012-11-08 08:38:00.000' AS moddate, 'EMSZC14' AS who UNION ALL
SELECT 581827, '2012-11-08 09:14:18.000', 'EMSZC49' UNION ALL
SELECT 581827, '2012-11-08 09:17:35.000', 'EMSZC14' UNION ALL -- It is Fail
SELECT 581827, '2012-11-08 09:17:35.000', 'EMSZC14' -- It is Fail
),
cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY custno ORDER BY moddate) rn1,
ROW_NUMBER() OVER (PARTITION BY custno, who ORDER BY moddate) rn2
FROM chr
)
SELECT custno, moddate, who,
ROW_NUMBER() OVER (PARTITION BY custno, rn1 - rn2 ORDER BY moddate) rn
FROM cte
ORDER BY
custno,
moddate;
Wrong Result :
custno moddate who rn
581827 2012-11-08 08:38:00.000 EMSZC14 1
581827 2012-11-08 09:14:18.000 EMSZC49 1
581827 2012-11-08 09:17:35.000 EMSZC14 2
581827 2012-11-08 09:17:35.000 EMSZC14 3
Expected Result :
custno moddate who rn
581827 2012-11-08 08:38:00.000 EMSZC14 1
581827 2012-11-08 09:14:18.000 EMSZC49 1
581827 2012-11-08 09:17:35.000 EMSZC14 1
581827 2012-11-08 09:17:35.000 EMSZC14 2
You just need to add who
in the final row_number
as follows:
WITH chr AS (
SELECT 581827 AS custno, '2012-11-08 08:38:00.000' AS moddate, 'EMSZC14' AS who UNION ALL
SELECT 581827, '2012-11-08 09:14:18.000', 'EMSZC49' UNION ALL
SELECT 581827, '2012-11-08 09:17:35.000', 'EMSZC14' UNION ALL -- It is Fail
SELECT 581827, '2012-11-08 09:17:35.000', 'EMSZC14' -- It is Fail
),
cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY custno ORDER BY moddate) rn1,
ROW_NUMBER() OVER (PARTITION BY custno, who ORDER BY moddate) rn2
FROM chr
)
SELECT custno, moddate, who,
ROW_NUMBER() OVER (PARTITION BY custno, who, rn1 - rn2 ORDER BY moddate) rn
FROM cte
ORDER BY
custno,
moddate;