July 31, 2009 at 4:59 am
Hi,
Using the sql statement below in a stored procedure
rownum = row_number() over(partition by t.PTM_id, t.WL_id,case when 'P' in (c.activity__1,c.activity__2,c.activity__3) then 'C' else 'A' end order by C.date)
I get
PTM_idWL_iddateCancellationI GetI need
120308/01/2009C11
120320/01/2009A11
120302/02/2009C21
120316/02/2009C32
120317/03/2009A21
120321/04/2009A32
120320/05/2009C41
What I would like is the “I need” column where there partition is reset if the next in sequence is not the same as the previous one using Cancellation as the trigger for the partition.
Any suggestions would muchly appreciated.
Cheers
Doug
July 31, 2009 at 11:42 am
Well here is what I came up with:
CREATE TABLE #test (ptm_id INT, wl_id INT, [date] date, cancellation CHAR(1))
INSERT INTO #test VALUES (1, 203, '01/08/09', 'C')
INSERT INTO #test VALUES (1, 203, '01/20/09', 'A')
INSERT INTO #test VALUES (1, 203, '02/02/09', 'C')
INSERT INTO #test VALUES (1, 203, '02/16/09', 'C')
INSERT INTO #test VALUES (1, 203, '02/18/09', 'C')
INSERT INTO #test VALUES (1, 203, '03/17/09', 'A')
INSERT INTO #test VALUES (1, 203, '04/21/09', 'A')
INSERT INTO #test VALUES (1, 203, '05/20/09', 'C');
WITH testCTE AS
(SELECT cancellation, ROW_NUMBER() OVER (ORDER BY [date]) AS rowNum
FROM #test
),
testCTE2 AS
(
SELECT ptm_id, wl_id, [date], cancellation, ROW_NUMBER() OVER (ORDER BY [date]) AS rowNum
FROM #test
)
SELECT
testCTE2.ptm_id, testCTE2.wl_id, testCTE2.[date], testCTE2.cancellation, DENSE_RANK() OVER (PARTITION BY testCTE2.cancellation ORDER BY isnull(testCTE.rowNum,1))
FROM
testCTE2
LEFT JOIN testCTE ON (testCTE.rowNum = testCTE2.rowNum-1 AND testCTE.cancellation = testCTE2.cancellation)
ORDER BY testCTE2.[date]
DROP TABLE #test
I would guess there is a better way to do this but I stopped at the first solution I could figure out.
July 31, 2009 at 12:43 pm
This works for the sample data, but that might just be luck.. 🙂SELECT
*,
ROW_NUMBER() OVER (PARTITION BY R1, cancellation ORDER BY Date) AS
FROM
(
SELECT
*,
ROW_NUMBER() OVER (ORDER BY [Date])
- ROW_NUMBER() OVER (Partition BY cancellation ORDER BY [Date]) AS R1
FROM
#Test
) AS T
ORDER BY
[Date]
July 31, 2009 at 1:37 pm
I think I broke both of them:
CREATE TABLE #test (ptm_id INT, wl_id INT, [date] date, cancellation CHAR(1))
INSERT INTO #test VALUES (1, 203, '01/08/09', 'C')
INSERT INTO #test VALUES (1, 203, '01/20/09', 'A')
INSERT INTO #test VALUES (1, 203, '02/02/09', 'C')
INSERT INTO #test VALUES (1, 203, '02/16/09', 'C')
INSERT INTO #test VALUES (1, 203, '02/16/09', 'D')
INSERT INTO #test VALUES (1, 203, '02/16/09', 'C')
INSERT INTO #test VALUES (1, 203, '02/18/09', 'C')
INSERT INTO #test VALUES (1, 203, '02/18/09', 'C')
INSERT INTO #test VALUES (1, 203, '03/17/09', 'A')
INSERT INTO #test VALUES (1, 203, '04/21/09', 'A')
INSERT INTO #test VALUES (1, 203, '05/20/09', 'C');
I can't figure out a way to get the correct results.
July 31, 2009 at 1:55 pm
I couldn't figure out how to get mine to work with the new data so I modified Lamprey13's.
I think this will work.
CREATE TABLE #test (ptm_id INT, wl_id INT, [date] date, cancellation CHAR(1))
INSERT INTO #test VALUES (1, 203, '01/08/09', 'C')
INSERT INTO #test VALUES (1, 203, '01/20/09', 'A')
INSERT INTO #test VALUES (1, 203, '02/02/09', 'C')
INSERT INTO #test VALUES (1, 203, '02/16/09', 'C')
INSERT INTO #test VALUES (1, 203, '02/16/09', 'D')
INSERT INTO #test VALUES (1, 203, '02/16/09', 'C')
INSERT INTO #test VALUES (1, 203, '02/18/09', 'C')
INSERT INTO #test VALUES (1, 203, '02/18/09', 'C')
INSERT INTO #test VALUES (1, 203, '02/20/09', 'D')
INSERT INTO #test VALUES (1, 203, '03/17/09', 'A')
INSERT INTO #test VALUES (1, 203, '03/17/09', 'A')
INSERT INTO #test VALUES (1, 203, '04/21/09', 'E')
INSERT INTO #test VALUES (1, 203, '04/21/09', 'E')
INSERT INTO #test VALUES (1, 203, '05/20/09', 'C');
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY R1, cancellation ORDER BY [date]) AS
FROM
(
SELECT
*,
ROW_NUMBER() OVER (ORDER BY ptm_id, wl_id, [date], cancellation)
- ROW_NUMBER() OVER (Partition BY ptm_id, wl_id, cancellation ORDER BY [date]) AS R1
FROM
#Test
) AS T
ORDER BY
[date]
DROP TABLE #test
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply