June 21, 2012 at 6:34 pm
Hello all,
I've read articles and examples of this using techniques like XML path but I have a more complicated version. I need to concatenate based on start and end dates. For a given range I need all the CodeVal's there are active. These dates can be overlapping as well. Take for example this data set:
ID StartDt EndDt CodeVal
11 1/1/12 1/5/12 AAA
11 1/3/12 1/7/12 BBB
11 1/4/12 1/8/12 CCC
The result set I need is:
ID StartDt EndDt ConcatVal
11 1/1/12 1/2/12 AAA
11 1/3/12 1/3/12 AAA, BBB
11 1/4/12 1/5/12 AAA, BBB, CCC
11 1/6/12 1/7/12 BBB, CCC
11 1/8/12 1/8/12 CCC
I'd appreciate any help I can get!
create table #MyTable(ID int, StartDt datetime, EndDt datetime, CodeVal varchar(10))
Insert into #MyTable
Values(11,'1/1/2012','1/5/2012','AAA')
,(11,'1/3/2012','1/7/2012','BBB')
,(11,'1/4/2012','1/8/2012','CCC')
June 21, 2012 at 8:43 pm
The query you want should look something like this:
DECLARE @t TABLE (ID INT, StartDt DATETIME, EndDt DATETIME, CodeVal VARCHAR(3))
INSERT INTO @t
SELECT 11, '2012-01-01', '2012-01-05', 'AAA'
UNION ALL SELECT 11, '2012-01-03', '2012-01-07', 'BBB'
UNION ALL SELECT 11, '2012-01-04','2012-01-08', 'CCC'
;WITH Tally (n) AS (
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns),
CTE AS (
SELECT ID, StartDt, EndDt=date2, CodeVal
FROM @t t
CROSS APPLY (SELECT StartDt + n FROM Tally WHERE EndDt >= StartDt + n) x(date2)),
CTE2 AS (
SELECT ID, StartDt, EndDt, STUFF(
(SELECT ',', CodeVal
FROM CTE t2
WHERE t1.ID = t2.ID and t1.EndDt = t2.EndDt
ORDER BY CodeVal
FOR XML PATH('')), 1, 1, '') AS ConcatVal
FROM CTE t1
)
SELECT ID, StartDT, EndDT=MAX(EndDT), ConcatVal
FROM CTE2
GROUP BY ID, StartDT, ConcatVal
ORDER BY ID, StartDt, EndDt
You should adjust the TOP clause on the Tally table to include enough days to span the longest StartDt to EndDt period.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 22, 2012 at 7:45 am
Thanks! This has gotten me a lot closer. I made a couple of modifications to adjust the start date result and remove the xml tags. But this brought up a new issue. The start and end dates of the raw data can overlap but the start dates of the result set cannot. Here are my modified results (query posted below).
IDStartDT EndDT ConcatVal
112012-01-01 2012-01-02 AAA
112012-01-03 2012-01-03 AAA,BBB
112012-01-04 2012-01-05 AAA,BBB,CCC
112012-01-04 2012-01-07 BBB,CCC
112012-01-04 2012-01-08 CCC
The start dates are correct and match the data, but the results should look like this:
IDStartDT EndDT ConcatVal
112012-01-01 2012-01-02 AAA
112012-01-03 2012-01-03 AAA,BBB
112012-01-04 2012-01-05 AAA,BBB,CCC
112012-01-06 2012-01-07 BBB,CCC
112012-01-08 2012-01-08 CCC
Notice the lack of overlap. Admittedly I haven't started looking at what it would take to change the results but I thought I'd put this out there in case you or someone saw a quick and obvious way to do it.
Updated code:
DECLARE @t TABLE (ID INT, StartDt DATETIME, EndDt DATETIME, CodeVal VARCHAR(3))
INSERT INTO @t
SELECT 11, '2012-01-01', '2012-01-05', 'AAA'
UNION ALL SELECT 11, '2012-01-03', '2012-01-07', 'BBB'
UNION ALL SELECT 11, '2012-01-04','2012-01-08', 'CCC'
;WITH Tally (n) AS (
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns),
CTE AS (
SELECT ID, StartDt, EndDt=date2, CodeVal
FROM @t t
CROSS APPLY (SELECT StartDt + n FROM Tally WHERE EndDt >= StartDt + n) x(date2)),
CTE2 AS (
SELECT ID, StartDt, EndDt, STUFF(
(SELECT ',' + CodeVal
FROM CTE t2
WHERE t1.ID = t2.ID and t1.EndDt = t2.EndDt
ORDER BY CodeVal
FOR XML PATH('')), 1, 1, '') AS ConcatVal
FROM CTE t1
)
SELECT ID, StartDT=MAX(StartDT), EndDT=MAX(EndDT), ConcatVal
FROM CTE2
GROUP BY ID,/* StartDT,*/ ConcatVal
ORDER BY ID, StartDt, EndDt
June 22, 2012 at 8:52 am
Another question. In your first CTE you have a cross apply:
CROSS APPLY (SELECT StartDt + n FROM Tally WHERE EndDt >= StartDt + n) x(date2)
What is the "x(date2)" part of this? I've never seen a cross apply written this way and I can't find any examples. I don't understand this syntax.
June 22, 2012 at 9:11 am
rgldiatr (6/22/2012)
Another question. In your first CTE you have a cross apply:CROSS APPLY (SELECT StartDt + n FROM Tally WHERE EndDt >= StartDt + n) x(date2)
What is the "x(date2)" part of this? I've never seen a cross apply written this way and I can't find any examples. I don't understand this syntax.
It looks like I'm talking to myself but is the syntax:
Cross Apply (select something...) AS Function x which returns (date2)? This makes sense to me but I don't know if it's right.
June 22, 2012 at 7:38 pm
rgldiatr (6/22/2012)
rgldiatr (6/22/2012)
Another question. In your first CTE you have a cross apply:CROSS APPLY (SELECT StartDt + n FROM Tally WHERE EndDt >= StartDt + n) x(date2)
What is the "x(date2)" part of this? I've never seen a cross apply written this way and I can't find any examples. I don't understand this syntax.
It looks like I'm talking to myself but is the syntax:
Cross Apply (select something...) AS Function x which returns (date2)? This makes sense to me but I don't know if it's right.
You're correct. The CROSS APPLY must return a named field and you can either alias it within the SELECT or in parens after the table alias.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply