June 13, 2012 at 10:23 am
Nagaram (6/13/2012)
The input data always consistent .begin_time having incremental date also end_time having incremental date.
I need to group the Consecutive TECH_MODE records by selecting MIN(BEGIN_TIME) , MAX(END_TIME)
-- Output should be 4 records(for 4 TECH modes i.e ).
record one for TECH_MODE 6
record 2 for TECH_MODE 5
record 3 for TECH_MODE 13
record 4 for TECH_MODE 5
Please help me .
Thanks,
Now, as Jared has asked, what have you done to try and solve this problem? Show us your code.
June 13, 2012 at 10:33 am
I tried this ...
But getting same rank for all TECH_MODE 5, Some where partition is missing .
I am unable figure out it.
SELECT * , DENSE_RANK() OVER( ORDER BY TECH_MODE_ID)
FROM SAMPLE
ORDER BY BEGIN_TIME
June 13, 2012 at 10:49 am
You understand why that is not working, right? Because you want the TECH_MODE_ID to be ranked differently if something occurred between the same one. You are going to have to figure out some way to delineate those groups before using a rank() or dense_rank()... Keep trying, and I will try some things as well.
Jared
CE - Microsoft
June 13, 2012 at 10:55 am
I am trying with permutation and combinations ... not yet figure out.
Still trying ..
June 13, 2012 at 10:56 am
I think you can use below query
SELECT REFERENCE_ID
,TECH_MODE_ID
,MIN(BEGIN_TIME)
,MAX(END_TIME)
FROM SAMPLE
GROUP BY
REFERENCE_ID
,TECH_MODE_ID
,CONVERT(VARCHAR(20), BEGIN_TIME, 100)
June 13, 2012 at 11:01 am
venkat_reddy7 (6/13/2012)
I think you can use below querySELECT REFERENCE_ID
,TECH_MODE_ID
,MIN(BEGIN_TIME)
,MAX(END_TIME)
FROM SAMPLE
GROUP BY
REFERENCE_ID
,TECH_MODE_ID
,CONVERT(VARCHAR(20), BEGIN_TIME, 100)
I think it will not work in all cases .What will happen if I add another record at last
152012-05-04 20:29:22.0002012-05-04 20:29:52.000
thanks for giving Query , but it will fail above scenario
June 13, 2012 at 11:01 am
Lynn, I think I have seen you do something like this before. Like a nested bunch of different rank functions? Hmm...
Jared
CE - Microsoft
June 13, 2012 at 11:30 am
Ok, this may not be the best or cleanest... but is the best I could do quickly:
;
WITH bt_rank (
ID
,TECH_MODE_ID
,begin_time
,newGroup
)
AS (
SELECT id
,tech_mode_id
,begin_time
,RANK() OVER (
ORDER BY begin_time
) - RANK() OVER (
PARTITION BY TECH_MODE_ID ORDER BY begin_time
) AS newGroup
FROM sample
)
,groupRank (
tech_mode_id
,newGroup
,min_begin_time
)
AS (
SELECT tech_mode_id
,newGroup
,MIN(BEGIN_time) AS min_begin_time
FROM bt_rank
GROUP BY TECH_MODE_ID
,newGroup
)
,finalRank (
tech_mode_id
,newGroup
,finalRank
)
AS (
SELECT tech_mode_id
,newGroup
,RANK() OVER (
ORDER BY min_begin_time
) AS finalRank
FROM groupRank
)
SELECT s.ID
,s.REFERENCE_ID
,s.TECH_MODE_ID
,s.BEGIN_TIME
,s.END_TIME
,f.finalRank
FROM sample s
INNER JOIN bt_rank b ON s.id = b.id
INNER JOIN finalRank f ON b.TECH_MODE_ID = f.tech_mode_id
AND b.newGroup = f.newGroup
ORDER BY ID
Jared
CE - Microsoft
June 13, 2012 at 11:35 am
Wow .. Thanks.,
This big code working Great .. I will do some testing on this ....
Thanks a lot
June 13, 2012 at 11:37 am
This seems like it works:
WITH SAMPLE (REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME) AS
(
SELECT 1, 6, CONVERT(DATETIME,'2012-05-03 20:29:22'),CONVERT(DATETIME,'2012-05-03 20:29:52') UNION ALL
SELECT 1, 5, '2012-05-03 20:29:53','2012-05-03 20:29:55' UNION ALL
SELECT 1, 5, '2012-05-03 20:29:56','2012-05-03 20:30:03' UNION ALL
SELECT 1, 13, '2012-05-03 20:30:04','2012-05-03 20:30:05' UNION ALL
SELECT 1, 5, '2012-05-03 20:30:06','2012-05-03 20:30:09' UNION ALL
SELECT 1, 5, '2012-05-03 20:30:10','2012-05-03 20:30:19' UNION ALL
SELECT 1, 5, '2012-05-03 20:30:20','2012-05-03 20:34:50'
), cte2 AS
(
SELECT * , Grp = ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID ORDER BY BEGIN_TIME) -
ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID, TECH_MODE_ID ORDER BY BEGIN_TIME)
FROM SAMPLE
), cte3 AS
(
SELECT REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME,
[Rank] = DENSE_RANK() OVER( ORDER BY Grp)
FROM cte2
)
SELECT REFERENCE_ID,
TECH_MODE_ID,
BEGIN_TIME = MIN(BEGIN_TIME),
END_TIME = MAX(END_TIME)
FROM cte2
GROUP BY REFERENCE_ID, TECH_MODE_ID, Grp
ORDER BY BEGIN_TIME;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 13, 2012 at 11:41 am
The idea I used was to separate the same grouping of TECH_MODE_ID into separate groups by subtracting the rank from within the grouping from the rank of all rows. This difference will be the same when the rows in the TECH_MODE_ID group are not consecutive when ordered by BEGIN_TIME. Essentially, you can then separate the parent group of TECH_MODE_ID into groups based on that and if they are consecutive.
Jared
CE - Microsoft
June 13, 2012 at 11:42 am
Great Result ...
Thank you very much ..
June 13, 2012 at 11:44 am
WayneS (6/13/2012)
This seems like it works:
WITH SAMPLE (REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME) AS
(
SELECT 1, 6, CONVERT(DATETIME,'2012-05-03 20:29:22'),CONVERT(DATETIME,'2012-05-03 20:29:52') UNION ALL
SELECT 1, 5, '2012-05-03 20:29:53','2012-05-03 20:29:55' UNION ALL
SELECT 1, 5, '2012-05-03 20:29:56','2012-05-03 20:30:03' UNION ALL
SELECT 1, 13, '2012-05-03 20:30:04','2012-05-03 20:30:05' UNION ALL
SELECT 1, 5, '2012-05-03 20:30:06','2012-05-03 20:30:09' UNION ALL
SELECT 1, 5, '2012-05-03 20:30:10','2012-05-03 20:30:19' UNION ALL
SELECT 1, 5, '2012-05-03 20:30:20','2012-05-03 20:34:50'
), cte2 AS
(
SELECT * , Grp = ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID ORDER BY BEGIN_TIME) -
ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID, TECH_MODE_ID ORDER BY BEGIN_TIME)
FROM SAMPLE
), cte3 AS
(
SELECT REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME,
[Rank] = DENSE_RANK() OVER( ORDER BY Grp)
FROM cte2
)
SELECT REFERENCE_ID,
TECH_MODE_ID,
BEGIN_TIME = MIN(BEGIN_TIME),
END_TIME = MAX(END_TIME)
FROM cte2
GROUP BY REFERENCE_ID, TECH_MODE_ID, Grp
ORDER BY BEGIN_TIME;
No because "ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID, TECH_MODE_ID ORDER BY BEGIN_TIME)" will always evaluate to 1 since REFERENCE_ID is the primary key.
Jared
CE - Microsoft
June 13, 2012 at 11:52 am
No because "ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID, TECH_MODE_ID ORDER BY BEGIN_TIME)" will always evaluate to 1 since REFERENCE_ID is the primary key.
SSCrazy,
It will work I think . REFERENCE_ID is not the primary key... it is foreign key
June 13, 2012 at 11:54 am
SQLKnowItAll (6/13/2012)
WayneS (6/13/2012)
This seems like it works:
WITH SAMPLE (REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME) AS
(
SELECT 1, 6, CONVERT(DATETIME,'2012-05-03 20:29:22'),CONVERT(DATETIME,'2012-05-03 20:29:52') UNION ALL
SELECT 1, 5, '2012-05-03 20:29:53','2012-05-03 20:29:55' UNION ALL
SELECT 1, 5, '2012-05-03 20:29:56','2012-05-03 20:30:03' UNION ALL
SELECT 1, 13, '2012-05-03 20:30:04','2012-05-03 20:30:05' UNION ALL
SELECT 1, 5, '2012-05-03 20:30:06','2012-05-03 20:30:09' UNION ALL
SELECT 1, 5, '2012-05-03 20:30:10','2012-05-03 20:30:19' UNION ALL
SELECT 1, 5, '2012-05-03 20:30:20','2012-05-03 20:34:50'
), cte2 AS
(
SELECT * , Grp = ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID ORDER BY BEGIN_TIME) -
ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID, TECH_MODE_ID ORDER BY BEGIN_TIME)
FROM SAMPLE
), cte3 AS
(
SELECT REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME,
[Rank] = DENSE_RANK() OVER( ORDER BY Grp)
FROM cte2
)
SELECT REFERENCE_ID,
TECH_MODE_ID,
BEGIN_TIME = MIN(BEGIN_TIME),
END_TIME = MAX(END_TIME)
FROM cte2
GROUP BY REFERENCE_ID, TECH_MODE_ID, Grp
ORDER BY BEGIN_TIME;
No because "ROW_NUMBER() OVER (PARTITION BY REFERENCE_ID, TECH_MODE_ID ORDER BY BEGIN_TIME)" will always evaluate to 1 since REFERENCE_ID is the primary key.
Actually, Jared, Wayne's solution is direction I was going and it looks like it should work as well. It does with my test data.
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply