April 21, 2011 at 3:57 pm
I'd like to have the min and max date for each dept, but so far I've been stumped when the person goes a department they were previously in.
I've tried row number but haven't hit the right partition logic.
Test Data:
DECLARE @tbl TABLE (person CHAR(1), dept CHAR(1), date DATETIME)
INSERT INTO @tbl ( person, dept, date )
SELECT '1','A','2011-04-10 21:00:00' UNION
SELECT '1','A', '2011-04-10 22:31:00' UNION
SELECT '1','A', '2011-04-10 23:41:00' UNION
SELECT '1','B','2011-04-11 01:11:00' UNION
SELECT '1','C','2011-04-15 12:15:00' UNION
SELECT '1','B','2011-04-17 15:57:00' UNION
SELECT '1','D','2011-04-19 10:09:00' UNION
SELECT '1','C','2011-04-20 18:03:00' UNION
SELECT '1','C','2011-04-20 18:07:00' UNION
SELECT '1','C','2011-04-21 00:10:00' UNION
SELECT '1','C','2011-04-21 00:10:00'
SELECT * FROM @tbl
ORDER BY 1,3
Expected results:
1A2011-04-10 21:00:00 2011-04-11 01:11:00
1B 2011-04-11 01:11:00 2011-04-15 12:15:00
1C2011-04-15 12:15:002011-04-17 15:57:00
1B2011-04-17 15:57:002011-04-19 10:09:00
1D2011-04-19 10:09:002011-04-20 18:03:00
1C2011-04-20 18:03:002011-04-21 00:10:00
April 21, 2011 at 4:53 pm
Hey Scott,
What you needed to do was island the groupings, then come back to them. I did this using some RowNumbering tricks. Take a look at the following, let me know what questions you have.
/* http://www.sqlservercentral.com/Forums/Topic1097278-338-1.aspx */
DECLARE @tbl TABLE (person CHAR(1), dept CHAR(1), date DATETIME)
INSERT INTO @tbl ( person, dept, date )
SELECT '1', 'A', '2011-04-10 21:00:00' UNION
SELECT '1', 'A', '2011-04-10 22:31:00' UNION
SELECT '1', 'A', '2011-04-10 23:41:00' UNION
SELECT '1', 'B', '2011-04-11 01:11:00' UNION
SELECT '1', 'C', '2011-04-15 12:15:00' UNION
SELECT '1', 'B', '2011-04-17 15:57:00' UNION
SELECT '1', 'D', '2011-04-19 10:09:00' UNION
SELECT '1', 'C', '2011-04-20 18:03:00' UNION
SELECT '1', 'C', '2011-04-20 18:07:00' UNION
SELECT '1', 'C', '2011-04-21 00:10:00' UNION
SELECT '1', 'C', '2011-04-21 00:10:00'
;WITH cte AS
(SELECT
Person,
Dept,
[date] AS dt,
Row_Number() OVER (ORDER BY [DATE]) AS DateRN,
ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY [Date]) AS DDRN
FROM
@tbl
)
, cte2 AS
(SELECT *,
dateRN - DDRN AS GrpRN
FROM cte
)
--select * from cte2
SELECT
c.Person, c.Dept, c.GrpRN,
MIN( c.dt) AS StartDate,
ISNULL( (SELECT dt FROM cte2 AS c2 WHERE c2.DateRN - 1 = MAX(c.DateRN)), GETDATE()) AS EndDate
FROM
cte2 AS c
GROUP BY
c.Person, c.Dept, c.GrpRN
ORDER BY
MIN( c.dt)
/*
Expected results:
1A 2011-04-10 21:00:00 2011-04-11 01:11:00
1B 2011-04-11 01:11:00 2011-04-15 12:15:00
1C 2011-04-15 12:15:00 2011-04-17 15:57:00
1B 2011-04-17 15:57:00 2011-04-19 10:09:00
1D 2011-04-19 10:09:00 2011-04-20 18:03:00
1C 2011-04-20 18:03:00 2011-04-21 00:10:00
*/
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 22, 2011 at 5:46 am
Thanks Craig! That is what I was looking for.
The last end date was defaulting to getdate(), but I modified the second part of the isnull to be (SELECT dt FROM cte2 AS c2 WHERE c2.DateRN = MAX(c.DateRN)) and it appears to be working for this test case.
Thanks so much for the quick reply. This method is a lot cleaner than what I was attempting to do.
Scott
April 22, 2011 at 11:50 am
Scott Carter-357760 (4/22/2011)
Thanks Craig! That is what I was looking for.The last end date was defaulting to getdate(), but I modified the second part of the isnull to be (SELECT dt FROM cte2 AS c2 WHERE c2.DateRN = MAX(c.DateRN)) and it appears to be working for this test case.
Thanks so much for the quick reply. This method is a lot cleaner than what I was attempting to do.
Scott
No problem, glad to help you out. 🙂 Sorry about leaving the GETDATE() behind, I ended up rushed and wanted to dump this to you before I ended up forgetting to ever post it due to some fires.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply