Quick Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Quick Question

Author

Topic

leesan
Starting Member

1 Post

Posted - 2006-12-28 : 21:28:43

Hi Everyone,
I'm new to the board and somewhat new to sql too. So I have a small problem that I hope someone can help me with.

DATA
Name: time1 time2
Jack 1 2
Jack 2 3
Jack 3 4
Jack 8 9
Jane 3 4

I'd like to get a table that combines all the continuous times for each person so that it looks like this:

Jack 1 4
Jack 8 9
Jane 3 4

I'm desperate, and I appreciate it any help! Thanks!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-29 : 03:45:28

I don't think you'll do that with a single query, although one of the gurus that hangs around here will no doubt prove me wrong!

However you can do it fairly simply in a stored procedure like this - assuming a table created like this

CREATE TABLE NameTimes ([Name] varchar(20), Time1 int, Time2 int )
INSERT NameTimes
SELECT 'Jack', 1, 2 UNION ALL
SELECT 'Jack', 2, 3 UNION ALL
SELECT 'Jack', 3, 4 UNION ALL
SELECT 'Jack', 8, 9 UNION ALL
SELECT 'Jane', 3, 4

You can create a stored procedure like this

CREATE PROCEDURE GetNameTimes
AS
SELECT a.[Name], a.Time1, a.Time2
INTO #Temp
FROM NameTimes a
WHERE NOT EXISTS (SELECT * FROM NameTimes WHERE [Name] = a.[Name] AND Time2 = a.Time1)
WHILE 1=1
BEGIN
	UPDATE #Temp
	SET Time2 = a.Time2
	FROM NameTimes a
	WHERE a.Time1 = #Temp.Time2 AND a.Time2 > #Temp.Time2
	IF @@rowcount = 0 BREAK
END
SELECT * FROM #Temp

When you call that like this

EXEC GetNameTimes

You should get this result

Name                 Time1       Time2
-------------------- ----------- -----------
Jack                 1           4
Jack                 8           9
Jane                 3           4

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-29 : 08:57:37

snSQL,

Well done! Heh... I had to chuckle a bit because you have the same attitude as me... My attitude is "here's one way, watch for others!"

Leesan,

snSQL is correct to some great extent... I agree that it probably can't be solved with a single SELECT, but with a little SQL prestidigitation, it can be solved in a single query...

--===============================================================
--      This section is just to setup to test and would not be
--      included in your final code.  I added a couple of extra
--      data points just to be sure of the code.
--===============================================================
--===== If the test table exists, drop it
     IF OBJECT_ID('TempDB.dbo.#NameTimes') IS NOT NULL
        DROP TABLE #NameTimes

--===== Create and populate the test table
 CREATE TABLE #NameTimes ([Name] VARCHAR(20), StartTime INT, EndTime INT )
 INSERT #NameTimes ([Name], StartTime, EndTime)
 SELECT 'Jack',1 ,2  UNION ALL
 SELECT 'Jack',2 ,3  UNION ALL
 SELECT 'Jack',3 ,4  UNION ALL
 SELECT 'Jack',8 ,9  UNION ALL
 SELECT 'Jane',3 ,4  UNION ALL
 SELECT 'Jane',5 ,9  UNION ALL
 SELECT 'Jack',10,11 UNION ALL
 SELECT 'Jane',9 ,12 

--===============================================================
--      Solve the problem (your final code should look something
--      like this)
--===============================================================
--===== This part solves for multiple "adjacent" times 
 SELECT t1.[Name],
        MIN(t1.StartTime) AS StartTime,
        MAX(t2.EndTime) AS EndTime
   FROM #NameTimes t1
  INNER JOIN #NameTimes t2
     ON t1.[Name] = t2.Name
    AND t1.EndTime = t2.StartTime
  GROUP BY t1.[Name]

UNION ALL --===== This combines the results of the 2 queries

--===== This part solves for "non-adjacent" times
 SELECT t1.[Name],t1.StartTime,t1.EndTime
   FROM #NameTimes t1
   LEFT JOIN #NameTimes t2
     ON t1.[Name] = t2.[Name]
    AND (t1.EndTime = t2.StartTime OR t1.StartTime = t2.EndTime)
  WHERE t2.[Name] IS NULL
  ORDER BY t1.[Name],t1.StartTime

--Jeff Moden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-29 : 12:23:23

That's exactly what I was thinking Jeff

Your solution won't work if there are multiple ranges, for example if you change your test data to this

INSERT #NameTimes ([Name], StartTime, EndTime)
SELECT 'Jack',1 ,2 UNION ALL
SELECT 'Jack',2 ,3 UNION ALL
SELECT 'Jack',3 ,4 UNION ALL
SELECT 'Jack',8 ,9 UNION ALL
SELECT 'Jane',3 ,4 UNION ALL
SELECT 'Jane',5 ,9 UNION ALL
SELECT 'Jack',9 ,10 UNION ALL
SELECT 'Jack',10,11 UNION ALL
SELECT 'Jane',9 ,12

Then you get this result
Jack 1, 11
Jane 3, 4
Jane 5, 12

Instead of
Jack 1, 4
Jack 8, 11
Jane 3, 4
Jane 5, 12

It's the multiple unknown number of ranges of unknown length that makes me think my recursive approach updating a temp table will be the only way to go.
On 2005 you'll do it with a CTE in a single query.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-29 : 13:28:43

Crud! I see what you mean! Definitely overlooked that! I gotta look at this again 'cause it's not the first time I've seen such a request... thanks for the feedback...

--Jeff Moden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-29 : 15:40:14

Ok... I think this one might be it... took a lesson from the old "missing ID" code I used to have... instead of looking for what is "adjacent", the code finds everything that isn't as starting points and works from there...

--===============================================================
--      This section is just to setup to test and would not be
--      included in your final code.  I added a couple of extra
--      data points just to be sure of the code.
--===============================================================
--===== If the test table exists, drop it
     IF OBJECT_ID('TempDB.dbo.#NameTimes') IS NOT NULL
        DROP TABLE #NameTimes

--===== Create and populate the test table
 CREATE TABLE #NameTimes ([Name] VARCHAR(20), StartTime INT, EndTime INT )
 INSERT #NameTimes ([Name], StartTime, EndTime)
SELECT 'Jack',1 ,3 UNION ALL
SELECT 'Jack',3 ,4 UNION ALL
SELECT 'Jack',4 ,7 UNION ALL
SELECT 'Jack',8 ,9 UNION ALL
SELECT 'Jane',3 ,4 UNION ALL
SELECT 'Jane',5 ,9 UNION ALL
SELECT 'Jack',9 ,10 UNION ALL
SELECT 'Jack',10,11 UNION ALL
SELECT 'Jane',9 ,12

--===============================================================
--      Solve the problem (your final code should look something
--      like this)
--===============================================================
 SELECT t2.Name,
        t2.StartTime,
        EndTime = (--Correlated subquery finds rows for end of periods
                  SELECT MIN(t3.EndTime)
                    FROM #NameTimes t3
                    LEFT JOIN #NameTimes t4
                      ON t3.[Name] = t4.Name
                     AND t3.EndTime = t4.StartTime 
                   WHERE t4.Name IS NULL
                     AND t3.Name = t2.Name
                     AND t3.StartTime >= t2.StartTime
                   )
   FROM #NameTimes t1
  RIGHT JOIN #NameTimes t2
     ON t1.[Name] = t2.Name
    AND t1.EndTime = t2.StartTime 
  WHERE t1.Name IS NULL
  ORDER BY t2.Name,t2.StartTime

Really sorry about the earlier oversight...

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources