May 13, 2015 at 2:19 am
Hi all,
I have data similar to the below
CREATE TABLE #TEMP
(
TYPE VARCHAR(10),
SEQ INT,
SUB_TYPE VARCHAR(10))
INSERT INTO #TEMP
( TYPE,SEQ, SUB_TYPE )
VALUES
('TYPE1', 100, 'A' ),
('TYPE1', 110, '' ),
('TYPE1', 150, '' ),
('TYPE1', 170, 'B' ),
('TYPE1', 200, '' ),
('TYPE1', 220, '' ),
('TYPE1', 230, 'C' ),
('TYPE1', 250, '' ),
('TYPE1', 300, 'D' ),
('TYPE2', 110, 'A' ),
('TYPE2', 120, '' ),
('TYPE2', 150, 'B' ),
('TYPE2', 170, '' ),
('TYPE2', 200, 'C' ),
('TYPE2', 220, '' ),
('TYPE2', 230, '' ),
('TYPE2', 250, 'D' ),
('TYPE2', 300, '' )
SELECT * FROM #TEMP
DROP TABLE #TEMP
Now for each type the seq is very important. Effectively by order of seq the subtype stays the same until another subtype changes it. So for TYPE1 100,110 and 150 are A. 170, 200,220 are B. 230 and 250 are C and so on.
However as you can see the data isnt actually stored in the row. I need a select statement that shows this data.
I have done this:
SELECT t1.*,t3.SUB_TYPE FROM #TEMP t1
CROSS APPLY
(SELECT MAX(SEQ) SEQ FROM #TEMP AS t2 WHERE t1.SEQ >= t2.seq AND t2.SUB_TYPE <>'' AND t1.TYPE = t2.TYPE
GROUP BY t2.TYPE) t2
INNER JOIN
#TEMP t3
ON t3.TYPE = t1.TYPE AND t2.SEQ = t3.SEQ
And it seems to work. Is this the easiest way to do it or am i missing something?
Dan
May 13, 2015 at 5:05 am
Think this may do the same without the extra join, assuming that the SUB_TYPEs always go up in value A -> B -> C etc
SELECTT1.TYPE,
T1.SEQ,
T1.SUB_TYPE,
ISNULL(NULLIF(T1.SUB_TYPE,''),CA1.Sub_Type)
FROM#TEMP AS T1
CROSSAPPLY (
SELECTC.TYPE,
SEQ = MIN(C.SEQ),
SUB_TYPE = MAX(C.SUB_TYPE)
FROM#TEMP AS C
WHERET1.TYPE = C.TYPE
AND C.SEQ <= T1.SEQ
AND C.SUB_TYPE <> ''
GROUPBY C.TYPE
) AS CA1(Type,Seq,Sub_Type)
May 13, 2015 at 5:42 am
There are at least two alternatives which may perform better than your original. How much better depends to a great extent on indexing:
SELECT t.*, x.SUB_TYPE
FROM #TEMP t
OUTER APPLY (
SELECT TOP 1 ti.SUB_TYPE
FROM #TEMP ti
WHERE ti.TYPE = t.TYPE
AND ti.SEQ <= t.SEQ
AND ti.SUB_TYPE > ''
ORDER BY ti.SEQ DESC) x
SELECT t.*, x.SUB_TYPE
FROM #TEMP t
OUTER APPLY (
SELECT SUB_TYPE = MAX(ti.SUB_TYPE)
FROM #TEMP ti
WHERE ti.TYPE = t.TYPE
AND ti.SEQ <= t.SEQ
AND ti.SUB_TYPE > ''
) x
SELECT
t1.*,
t3.SUB_TYPE
FROM #TEMP t1
CROSS APPLY (
SELECT MAX(SEQ) SEQ
FROM #TEMP AS t2
WHERE t1.SEQ >= t2.seq
AND t2.SUB_TYPE <>''
AND t1.TYPE = t2.TYPE
GROUP BY t2.TYPE) t2
INNER JOIN #TEMP t3
ON t3.TYPE = t1.TYPE
AND t2.SEQ = t3.SEQ
CREATE INDEX ix_Helper ON #TEMP (SEQ, TYPE, SUB_TYPE)
SELECT t.*, x.SUB_TYPE
FROM #TEMP t
OUTER APPLY (
SELECT TOP 1 ti.SUB_TYPE
FROM #TEMP ti
WHERE ti.TYPE = t.TYPE
AND ti.SEQ <= t.SEQ
AND ti.SUB_TYPE > ''
ORDER BY ti.SEQ DESC) x
SELECT t.*, x.SUB_TYPE
FROM #TEMP t
OUTER APPLY (
SELECT SUB_TYPE = MAX(ti.SUB_TYPE)
FROM #TEMP ti
WHERE ti.TYPE = t.TYPE
AND ti.SEQ <= t.SEQ
AND ti.SUB_TYPE > ''
) x
SELECT
t1.*,
t3.SUB_TYPE
FROM #TEMP t1
CROSS APPLY (
SELECT MAX(SEQ) SEQ
FROM #TEMP AS t2
WHERE t1.SEQ >= t2.seq
AND t2.SUB_TYPE <>''
AND t1.TYPE = t2.TYPE
GROUP BY t2.TYPE) t2
INNER JOIN #TEMP t3
ON t3.TYPE = t1.TYPE
AND t2.SEQ = t3.SEQ
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 13, 2015 at 5:52 am
Try
SELECT TYPE, SEQ, b.SUB_TYPE
FROM #TEMP a
CROSS APPLY (SELECT TOP(1) SUB_TYPE
FROM #TEMP
WHERE TYPE = a.TYPE AND SEQ <= a.SEQ AND SUB_TYPE >''
ORDER BY SEQ DESC
) b
ORDER BY TYPE, SEQ
Any order of SUB_TYPE will do.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply