May 22, 2015 at 9:05 am
Hello all,
I need to create a script that adds an incrementing suffix to two columns, but restarts based on the value of another column. I found a similar question in the SQL Server 2000 forum, but it doesn't quite fit and also I'm working with SQL Server 2008 R2. The code below both creates a table with test data and tries to carry out the task. If you run this, you will see that the VISITNUM column has a value of UNS in row 4, UNS.1 in row 5 and UNS.2 in row 6. In row 7 it's V200, then in rows 8 and 9 it's UNS.3 for both. The same suffix gets applied to the VISIT column, but of course if I can solve this for VISITNUM then adding the suffix to VIST as well will be easy.
What I need is for row 8 to have UNS and row 9 to have UNS.1. In other words, any time the VISITNUM is UNS several times in a row, I need to add that ".X" suffix, but if a row has something other than UNS, I need to start over again the next time it's UNS again.
Any help appreciated! Here's my SQL code - be kind ;-):
CREATE TABLE #testing(
KitID varchar(20),
SubjID varchar(20),
VISIT varchar(60),
VISITNUM varchar(20),
LBDTC varchar(19),
rownum int NULL,
denserank int NULL
)
--insert test data
INSERT INTO #testing
SELECT 'C1756XXXXX1', '020701', 'Unscheduled', 'UNS', '2012-08-14T08:40:00', null, null --single unscheduled
INSERT INTO #testing
SELECT 'C1756XXXXX2', '020701', 'Visit 1, Rollover', 'V100', '2012-08-01T08:40:00', null, null --three scheduled with different collection times
INSERT INTO #testing
SELECT 'C1756XXXXX3', '020701', 'Visit 1, Rollover', 'V100', '2012-08-01T09:40:00', null, null
INSERT INTO #testing
SELECT 'C1756XXXXX4', '020701', 'Visit 1, Rollover', 'V100', '2012-08-01T10:40:00', null, null
INSERT INTO #testing
SELECT 'C1756XXXXX5', '020701', 'Unscheduled', 'UNS', '2012-08-18T08:32:00', null, null --two unscheduled with same collection time
INSERT INTO #testing
SELECT 'C1756XXXXX6', '020701', 'Unscheduled', 'UNS', '2012-08-19T08:32:00' , null, null
INSERT INTO #testing
SELECT 'C1756XXXXX7', '020701', 'Visit 2, Rollover', 'V200', '2012-08-20T10:40:00', null, null --single scheduled
INSERT INTO #testing
SELECT 'C1756XXXXX8', '020701', 'Unscheduled', 'UNS', '2012-08-22T08:30:00', null, null --single unscheduled
INSERT INTO #testing
SELECT 'C1756XXXXX8', '020701', 'Unscheduled', 'UNS', '2012-08-25T08:30:00', null, null --single unscheduled
INSERT INTO #testing
SELECT 'C1756XXXXX7', '020701', 'Visit 2, Rollover', 'V200', '2012-08-27T10:40:00', null, null --single scheduled
-- set rownum & denserank
;WITH cte AS (
SELECT [KitID],[SUBJID],[VISIT],[VISITNUM],[LBDTC],
ROW_NUMBER() OVER (PARTITION BY subjid ORDER BY SUBSTRING(LBDTC,1,10) ASC) rownum,
DENSE_RANK() OVER (PARTITION BY subjid ORDER BY SUBSTRING(LBDTC,1,10) ASC) rnk
FROM #testing
)
UPDATE #testing
SET rownum = cte.rownum, denserank = cte.rnk
FROM #testing x
JOIN cte
ON x.kitid = cte.kitid
AND x.subjid = cte.subjid
AND x.visit = cte.visit
AND x.VISITNUM = cte.VISITNUM
AND x.LBDTC = cte.LBDTC
-- Compare to previous row and add final ranking
;WITH y AS (
SELECT *,
RANK() OVER (PARTITION BY subjid, VISITNUM ORDER BY SUBSTRING(LBDTC,1,10) ASC) rnk2
FROM #testing --order by subjid, lbdtc
),
x2 AS (
select * from #testing
)
UPDATE #testing
SET VISITNUM = x.VISITNUM + '.' + CONVERT(varchar(5), y.rnk2-1)
, VISIT = x.VISIT + '.' + CONVERT(varchar(5), y.rnk2-1)
FROM #testing x
JOIN y
ON x.kitid = y.kitid
JOIN x2
ON x.rownum = x2.rownum - 1
AND x.subjid = x2.subjid
WHERE x.VISITNUM = 'UNS'
AND y.rnk2 >1
-- check result
select * from #testing order by subjid, lbdtc
--clean up
drop table #testing
May 22, 2015 at 10:21 am
8 views and no answers - is my question really hard or really stupid? 😉
May 22, 2015 at 11:31 am
Using just the base test data (w/o the updates)
; WITH CTE AS (
SELECT
t.KitID,
t.SubjID,
t.VISIT,
t.VISITNUM,
t.LBDTC,
ROW_NUMBER() OVER (ORDER BY t.SubjID,t.LBDTC,t.KitID) AS rn1,
ROW_NUMBER() OVER (PARTITION BY t.VISITNUM ORDER BY t.SubjID,t.LBDTC,t.KitID) AS rn2
FROM
#testing t
)
SELECT
c1.KitID,
c1.SubjID,
c1.VISIT,
c1.VISITNUM,
c1.LBDTC,
CASE
WHEN c1.VISITNUM = c2.VISITNUM THEN CAST(ROW_NUMBER() OVER (PARTITION BY c1.VISITNUM + c2.VISITNUM, c1.rn1 - c1.rn2 ORDER BY c1.rn1) AS VARCHAR(5))
ELSE ''
END AS VisitSeq
FROM
CTE c1
LEFT JOIN CTE c2
ON c1.rn1 = c2.rn1 + 1
AND c1.VISITNUM = c2.VISITNUM
ORDER BY
c1.rn1
May 22, 2015 at 12:20 pm
Jason - that did it perfectly. Many thanks, if you're ever in Rochester, NY I owe you a beer (or other beverage of your choice as long as it doesn't break my wallet)!
May 22, 2015 at 12:47 pm
jkalmar 43328 (5/22/2015)
Jason - that did it perfectly. Many thanks, if you're ever in Rochester, NY I owe you a beer (or other beverage of your choice as long as it doesn't break my wallet)!
I appreciate the kind words. If find myself up there, I'll look you up and take you up on the offer. If you're ever in Jax, FL... Do the same. 🙂
Glad it'll work for you.
May 22, 2015 at 12:51 pm
Oh... Just a side note... If/when you make the jump to SQL Server 2012 or later, revisit this code. You'll be able to use the LAD & LEAD functions to replace the self join, which will make the code quite a bit more efficient.
May 22, 2015 at 2:00 pm
jkalmar 43328 (5/22/2015)
8 views and no answers - is my question really hard or really stupid? 😉
Heh... you couldn't do it now, could you? You need to be patient. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 26, 2015 at 10:09 am
I dream of the day they upgrade to 2012 here...
May 26, 2015 at 10:10 am
Jeff Moden (5/22/2015)
jkalmar 43328 (5/22/2015)
8 views and no answers - is my question really hard or really stupid? 😉Heh... you couldn't do it now, could you? You need to be patient. 😉
Well I figured you were all smarter than me! 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply