query help combining records
-
num date1 date2 title
123451950-01-01 00:00:00.0001999-12-31 00:00:00.000title1
123452000-01-01 00:00:00.0002001-02-28 00:00:00.000title1
123452001-03-01 00:00:00.0002002-01-06 00:00:00.000title2
123452002-01-07 00:00:00.0002002-01-13 00:00:00.000title2
123452002-01-14 00:00:00.0002003-01-21 00:00:00.000title2
123452003-01-22 00:00:00.0002006-07-31 00:00:00.000title2
123452006-08-01 00:00:00.0002025-12-31 00:00:00.000title1
I have data shown as above in the 4 columns that I would like to combine as
123451950-01-01 00:00:00.0002001-02-28 00:00:00.000title1
123452001-03-01 00:00:00.0002006-07-31 00:00:00.000title2
123452006-08-01 00:00:00.0002025-12-31 00:00:00.000title1
-
Can't see the significance of 'num' here, but try this anyway
SELECT s1.num,s1.date1,
MIN(t1.date2) AS date2,
s1.title
FROM mytable s1
INNER JOIN mytable t1 ON s1.title=t1.title AND s1.date1 <= t1.date2+1
AND NOT EXISTS(SELECT * FROM mytable t2
WHERE t1.title=t2.title AND t1.date2+1 >= t2.date1 AND t1.date2+1 < t2.date2)
WHERE NOT EXISTS(SELECT * FROM mytable s2
WHERE s1.title=s2.title AND s1.date1 > s2.date1 AND s1.date1 <= s2.date2+1)
GROUP BY s1.num,s1.title,s1.date1
ORDER BY s1.date1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Oops. I meant that field to be employee id and so is needed in the query. I'm trying out your query. Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply