June 26, 2013 at 2:32 pm
Hello Everyone - this seams easier than it has been.
Here's the sample data:
DECLARE @tblJobHist_source TABLE
( SeqNo int UNIQUE NOT NULL
,startYear int NULL
,endYear int NULL
,number int NOT NULL
,name varchar(255) NOT NULL);
DECLARE @tblJobHist_resultSet TABLE
( SeqNo int UNIQUE NOT NULL
,startYear int NULL
,endYear int NULL
,number int NOT NULL
,name varchar(255) NOT NULL);
INSERT INTO @tblJobHist_source
SELECT5,1986,1988,1,'C & B LLC (US)' UNION ALL
SELECT10,1991,1992,1, 'Government of the Iceland' UNION ALL
SELECT15,1992,1996,1,'O''Martin LLP' UNION ALL
SELECT20,1996,1998,1,'WorldCo, Inc.' UNION ALL
SELECT25,1998,2000,1,'WorldCo, Inc.' UNION ALL
SELECT30,2000,2002,1,'XXX, Inc.' UNION ALL
SELECT35,2002,2003,1,'WorldCo, Inc.' UNION ALL
SELECT40,2004,2006,1,'Government of the Iceland' UNION ALL
SELECT45,2006,2010,1,'Government of the Iceland' UNION ALL
SELECT50,2010,NULL,1,'Pauls Equity Partners LLC' UNION ALL
SELECT55,NULL,NULL,1,'ACME Consulting, Inc.';
INSERT INTO @tblJobHist_resultSet
SELECT 55,NULL,NULL,1,'ACME Consulting, Inc.' UNION ALL
SELECT 50,2010,NULL,1,'Pauls Equity Partners LLC' UNION ALL
SELECT 45,2004,2010,2,'Government of the Iceland' UNION ALL
SELECT 40,2004,2010,2,'Government of the Iceland' UNION ALL
SELECT 35,2002,2003,1,'WorldCom, Inc.' UNION ALL
SELECT 30,2000,2002,1,'XXX, Inc.' UNION ALL
SELECT 20,1996,2000,2,'WorldCo, Inc.' UNION ALL
SELECT 25,1996,2000,2,'WorldCo, Inc.' UNION ALL
SELECT 15,1992,1996,1,'O''Martin LLP' UNION ALL
SELECT 10,1991,1992,1,'Government of the Iceland' UNION ALL
SELECT 5,1986,1988,1,'C & B LLC (US)';
-- WHAT I HAVE NOW...
SELECT * FROM @tblJobHist_source;
-- WHAT I NEED...
SELECT * FROM @tblJobHist_resultSet ORDER BY SeqNo DESC;
What I have now:
SeqNostartYear endYearnumbername
51986 19881C & B LLC (US)
101991 19921Government of the Iceland
151992 19961O'Martin LLP
201996 19981WorldCo, Inc.
251998 20001WorldCo, Inc.
302000 20021XXX, Inc.
352002 20031WorldCo, Inc.
402004 20061Government of the Iceland
452006 20101Government of the Iceland
502010 NULL1Pauls Equity Partners LLC
55NULL NULL1ACME Consulting, Inc.
What I need:
SeqNostartYear endYearnumbername
55NULL NULL1ACME Consulting, Inc.
502010 NULL1Pauls Equity Partners LLC
452004 20102Government of the Iceland
402004 20102Government of the Iceland
352002 20031WorldCom, Inc.
302000 20021XXX, Inc.
251996 20002WorldCo, Inc.
201996 20002WorldCo, Inc.
151992 19961O'Martin LLP
101991 19921Government of the Iceland
51986 19881C & B LLC (US)
As you can see there are a couple companies that have connecting startYear & endYear... For those the I need to merge the dates; in other words, I need to change this:
201996 19981WorldCo, Inc.
251998 20001WorldCo, Inc.
into this:
251996 20002WorldCo, Inc.
201996 20002WorldCo, Inc.
Let me know if you need more clarity. Order is not important. Thanks a ton!
-- Itzik Ben-Gan 2001
June 26, 2013 at 2:46 pm
I think this meets your needs. Probably other (better?) ways of doing this too.
SELECT sr.SeqNo,dt.StartYear,dt.EndYear,number,sr.name
FROM @tblJobHist_source sr
INNER JOIN ( SELECT MIN(StartYear)StartYear,MAX(EndYear)EndYear,Name
FROM @tblJobHist_source dt
GROUP BY name
) dt ON sr.name = dt.NAME
June 26, 2013 at 2:49 pm
I overlooked the number column. What causes that to change from 1 to 2?
Edit....I figured it out by looking at it again. My first solution isn't accurate for what you need. I'll try and take another stab at it.
June 26, 2013 at 2:59 pm
Alan have you looked at islands and gaps. I am pretty sure this is exactly what you need here.
http://www.sqlservercentral.com/articles/T-SQL/71550/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2013 at 8:17 am
Sean Lange (6/26/2013)
Alan have you looked at islands and gaps. I am pretty sure this is exactly what you need here.
It is, thanks Sean. I don't have my Ben Gan books handy at the moment but I just found the High Performance Functions Windows book on PDF. I am close; I'll post my solution when I finish it.
Edit: Typo
-- Itzik Ben-Gan 2001
June 27, 2013 at 11:44 am
Thank you everyone. I figured it out... Not the prettiest solution but it works.
-- With the numbers
WITH islands AS
(SELECT t1.SeqNo, t1.StartYear, t1.EndYear, t1.name
FROM @tblJobHist_src t1
JOIN @tblJobHist_src t2
ON t1.name=t2.name
AND (t1.startYear=t2.endYear OR t1.endYear=t2.startYear)),
islands_updated AS
(SELECT sr.SeqNo,dt.StartYear,dt.EndYear, sr.name
FROM islands sr
JOIN (SELECT MIN(StartYear)StartYear, MAX(EndYear)EndYear,Name
FROM islands dt
GROUP BY name) dt ON sr.name = dt.NAME),
prep1 AS
(SELECT SeqNo, StartYear, EndYear, name
FROM islands_updated
UNION
SELECT TOP 20000000 SeqNo, StartYear, EndYear, name
FROM @tblJobHist_src
WHERE SeqNo NOT IN (SELECT SeqNo FROM islands_updated)
ORDER BY SeqNo DESC),
prep2 AS
(SELECT StartYear, EndYear, count(name) AS number, name
FROM prep1
GROUP BY name, StartYear, EndYear)
SELECT p1.SeqNo, p1.StartYear, p1.EndYear, p2.number, p1.name
FROM prep1 p1
JOIN prep2 p2 ON p1.name=p2.name AND ISNULL(p1.StartYear,0)=ISNULL(p2.StartYear,0)
ORDER BY p1.SeqNo DESC;
Thank you Brendan - As you can see, I used some of what you did in my solution.
-- Itzik Ben-Gan 2001
June 27, 2013 at 1:10 pm
What about this?
SELECT
SeqNo
, coalesce(Data.startYear, src.startYear) AS [startYear]
, coalesce(data.endYear, src.endYear) AS [endYear]
, coalesce(data.Number, src.Number) AS [Number]
, src.name
FROM @tblJobHist_src src
OUTER APPLY
(
SELECT
min(startYear) AS [startYear]
, max(endYear) AS [endYear]
, sum(Number) AS [Number]
FROM @tblJobHist_src src2
WHERE
src2.name = src.name
AND
(
src.startYear BETWEEN src2.startYear AND src2.endYear
OR src.endYear BETWEEN src2.startYear AND src2.endYear
)
) AS Data
ORDER BY src.SeqNo DESC;
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
June 27, 2013 at 1:17 pm
SQL_FS (6/27/2013)
What about this?
SELECT
SeqNo
, coalesce(Data.startYear, src.startYear) AS [startYear]
, coalesce(data.endYear, src.endYear) AS [endYear]
, coalesce(data.Number, src.Number) AS [Number]
, src.name
FROM @tblJobHist_src src
OUTER APPLY
(
SELECT
min(startYear) AS [startYear]
, max(endYear) AS [endYear]
, sum(Number) AS [Number]
FROM @tblJobHist_src src2
WHERE
src2.name = src.name
AND
(
src.startYear BETWEEN src2.startYear AND src2.endYear
OR src.endYear BETWEEN src2.startYear AND src2.endYear
)
) AS Data
ORDER BY src.SeqNo DESC;
That is what I was looking for SQL_FS. Thanks, Great work!!!
-- Itzik Ben-Gan 2001
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply