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
SELECT 5,1986,1988,1,'C & B LLC (US)' UNION ALL
SELECT 10,1991,1992,1, 'Government of the Iceland' UNION ALL
SELECT 15,1992,1996,1, 'O''Martin LLP' UNION ALL
SELECT 20,1996,1998,1, 'WorldCo, Inc.' UNION ALL
SELECT 25,1998,2000,1, 'WorldCo, Inc.' UNION ALL
SELECT 30,2000,2002,1, 'XXX, Inc.' UNION ALL
SELECT 35,2002,2003,1, 'WorldCo, Inc.' UNION ALL
SELECT 40,2004,2006,1, 'Government of the Iceland' UNION ALL
SELECT 45,2006,2010,1, 'Government of the Iceland' UNION ALL
SELECT 50,2010,NULL,1, 'Pauls Equity Partners LLC' UNION ALL
SELECT 55,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:
SeqNo startYear endYear number name
5 1986 1988 1 C & B LLC (US)
10 1991 1992 1 Government of the Iceland
15 1992 1996 1 O'Martin LLP
20 1996 1998 1 WorldCo, Inc.
25 1998 2000 1 WorldCo, Inc.
30 2000 2002 1 XXX, Inc.
35 2002 2003 1 WorldCo, Inc.
40 2004 2006 1 Government of the Iceland
45 2006 2010 1 Government of the Iceland
50 2010 NULL 1 Pauls Equity Partners LLC
55 NULL NULL 1 ACME Consulting, Inc.
What I need:
SeqNo startYear endYear number name
55 NULL NULL 1 ACME Consulting, Inc.
50 2010 NULL 1 Pauls Equity Partners LLC
45 2004 2010 2 Government of the Iceland
40 2004 2010 2 Government of the Iceland
35 2002 2003 1 WorldCom, Inc.
30 2000 2002 1 XXX, Inc.
25 1996 2000 2 WorldCo, Inc.
20 1996 2000 2 WorldCo, Inc.
15 1992 1996 1 O'Martin LLP
10 1991 1992 1 Government of the Iceland
5 1986 1988 1 C & 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:
20 1996 1998 1 WorldCo, Inc.
25 1998 2000 1 WorldCo, Inc.
into this:
25 1996 2000 2 WorldCo, Inc.
20 1996 2000 2 WorldCo, 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://qa.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