June 13, 2012 at 8:14 am
CREATE TABLE SAMPLE
(
ID INT IDENTITY ,
REFERENCE_ID INT,
TECH_MODE_ID INT,
BEGIN_TIME DATETIME,
END_TIME DATETIME
)
INSERT INTO SAMPLE(REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME)
SELECT 1,6,'2012-05-03 20:29:22','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'
-- Expected out put if order by BEGIN_TIME:
1,6,'2012-05-03 20:29:22','2012-05-03 20:29:52'
1,5,'2012-05-03 20:29:53','2012-05-03 20:30:03' <red>(here first five min date, second five max date)</red>
1,13,'2012-05-03 20:30:04','2012-05-03 20:30:05'
1,5,'2012-05-03 20:30:06','2012-05-03 20:34:50' <red>(here first five min date, third five max date)</red>
Thanks
June 13, 2012 at 9:33 am
Hi, thanks for posting the DDL. However, I don't know what your question is or what logic you are trying to query on.
Jared
CE - Microsoft
June 13, 2012 at 9:42 am
Actually I need to apply one of the rank function .
Expected output 1:
IDREFERENCE_IDTECH_MODE_IDBEGIN_TIMEEND_TIME RANK
1162012-05-03 20:29:22.0002012-05-03 20:29:52.000 1
2152012-05-03 20:29:53.0002012-05-03 20:29:55.000 2
3152012-05-03 20:29:56.0002012-05-03 20:30:03.000 2
41132012-05-03 20:30:04.0002012-05-03 20:30:05.000 3
5152012-05-03 20:30:06.0002012-05-03 20:30:09.000 4
6152012-05-03 20:30:10.0002012-05-03 20:30:19.000 4
7152012-05-03 20:30:20.0002012-05-03 20:34:50.000:50' 4
Based on Above output I need to perform aggregation by using RANK,REFERENCE_ID,TECH_MODE_ID.
I need to select REFERENCE_IDTECH_MODE_IDmin(BEGIN_TIME),max(END_TIME), RANK
June 13, 2012 at 9:45 am
Ok, so you know what you need to do... Now what is the question? Have you tried anything?
Jared
CE - Microsoft
June 13, 2012 at 9:48 am
Nagaram (6/13/2012)
Actually I need to apply one of the rank function .Expected output 1:
IDREFERENCE_IDTECH_MODE_IDBEGIN_TIMEEND_TIME RANK
1162012-05-03 20:29:22.0002012-05-03 20:29:52.000 1
2152012-05-03 20:29:53.0002012-05-03 20:29:55.000 2
3152012-05-03 20:29:56.0002012-05-03 20:30:03.000 2
41132012-05-03 20:30:04.0002012-05-03 20:30:05.000 3
5152012-05-03 20:30:06.0002012-05-03 20:30:09.000 4
6152012-05-03 20:30:10.0002012-05-03 20:30:19.000 4
7152012-05-03 20:30:20.0002012-05-03 20:34:50.000:50' 4
Based on Above output I need to perform aggregation by using RANK,REFERENCE_ID,TECH_MODE_ID.
I need to select REFERENCE_IDTECH_MODE_IDmin(BEGIN_TIME),max(END_TIME), RANK
Looking at the following two records:
2 1 5 2012-05-03 20:29:53.000 2012-05-03 20:29:55.000 2
3 1 5 2012-05-03 20:29:56.000 2012-05-03 20:30:03.000 2
The end_date time of the first is one second less than the start_time of the next record. This is also true of the following three records:
5152012-05-03 20:30:06.0002012-05-03 20:30:09.000 4
6152012-05-03 20:30:10.0002012-05-03 20:30:19.000 4
7152012-05-03 20:30:20.0002012-05-03 20:34:50.000:50' 4
Is this pattern consistant or just something that happened in the sample data?
Also, I'm thinking the :50 at the end of the third records end_time is a typo.
June 13, 2012 at 9:51 am
your correct . !
:50 is just typo ..
Always begin_time in incremental value.
I am not sure on which rank function needs to be apply and where to apply to get above RANK .
In my sample data TECH_MODE field have value 5 in 2times ,
first two consecutive 5s needs to have same rank , and Next three consecutive 5s needs to have same rank
June 13, 2012 at 9:53 am
Click on the SQL Know-It-All in my signature and you will see that my most recent (although not that recent) post explains the different rank functions.
Jared
CE - Microsoft
June 13, 2012 at 9:56 am
Nagaram (6/13/2012)
I am not sure on which rank function needs to be apply and where to apply to get above RANK .In my sample data TECH_MODE field have value 5 in 2times ,
first two consecutive 5s needs to have same rank , and Next three consecutive 5s needs to have same rank
Looking at the data some more, if you isolate just on the begin_date and end_date values the previous records end_date is one second behind the next records begin_date. Is this pattern consistent in your data or just how the sample data is put together. What happens if the data looks like this:
INSERT INTO dbo.SAMPLE(REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME)
SELECT 1, 6, '2012-05-03 20:29:22','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' UNION ALL
SELECT 1, 5, '2012-05-03 20:34:51','2012-05-03 20:38:50' UNION ALL
SELECT 1, 5, '2012-05-03 20:38:51','2012-05-03 20:39:50';
June 13, 2012 at 9:58 am
thanks for suggesting rank function ..
I know about this..
But my problem is
-----------------------------------------------------
Looking at the following two records:
2 1 5 2012-05-03 20:29:53.000 2012-05-03 20:29:55.000 2
3 1 5 2012-05-03 20:29:56.000 2012-05-03 20:30:03.000 2
The end_date time of the first is one second less than the start_time of the next record. This is also true of the following three records:
5152012-05-03 20:30:06.0002012-05-03 20:30:09.000 4
6152012-05-03 20:30:10.0002012-05-03 20:30:19.000 4
7152012-05-03 20:30:20.0002012-05-03 20:34:50.000:50' 4
----------------------------------------------------
I am unable give same rank function for first set , second set...
I just given expected out put as rank ..
can you please help on this . how to get same rank
June 13, 2012 at 10:01 am
Nagaram (6/13/2012)
thanks for suggesting rank function ..I know about this..
But my problem is
-----------------------------------------------------
Looking at the following two records:
2 1 5 2012-05-03 20:29:53.000 2012-05-03 20:29:55.000 2
3 1 5 2012-05-03 20:29:56.000 2012-05-03 20:30:03.000 2
The end_date time of the first is one second less than the start_time of the next record. This is also true of the following three records:
5152012-05-03 20:30:06.0002012-05-03 20:30:09.000 4
6152012-05-03 20:30:10.0002012-05-03 20:30:19.000 4
7152012-05-03 20:30:20.0002012-05-03 20:34:50.000:50' 4
----------------------------------------------------
I am unable give same rank function for first set , second set...
I just given expected out put as rank ..
can you please help on this . how to get same rank
The end_date time of the first is one second less than the start_time of the next record. This is also true of the following three records
Yes, I noticed this, but you haven't answered the question. Is this always consistent?
June 13, 2012 at 10:01 am
Lynn Pettis (6/13/2012)
Nagaram (6/13/2012)
I am not sure on which rank function needs to be apply and where to apply to get above RANK .In my sample data TECH_MODE field have value 5 in 2times ,
first two consecutive 5s needs to have same rank , and Next three consecutive 5s needs to have same rank
Looking at the data some more, if you isolate just on the begin_date and end_date values the previous records end_date is one second behind the next records begin_date. Is this pattern consistent in your data or just how the sample data is put together. What happens if the data looks like this:
INSERT INTO dbo.SAMPLE(REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME)
SELECT 1, 6, '2012-05-03 20:29:22','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' UNION ALL
SELECT 1, 5, '2012-05-03 20:34:51','2012-05-03 20:38:50' UNION ALL
SELECT 1, 5, '2012-05-03 20:38:51','2012-05-03 20:39:50';
pattern is consistent always begin time of records is greater then the end_time of previous record..
June 13, 2012 at 10:02 am
Can you post what you have tried already? I'd love to help, but I'm not just going to do it for you if you know how the functions work (I charge $50 an hour for that 🙂 ) However, if you can show me what you have tried, I will work with that to see why it is not giving you the desired result.
Jared
CE - Microsoft
June 13, 2012 at 10:04 am
The pattern is consistent ..
Always BEGIN_TIME of records is greater then the END_TIME of previous record.
June 13, 2012 at 10:04 am
Nagaram (6/13/2012)
Lynn Pettis (6/13/2012)
Nagaram (6/13/2012)
I am not sure on which rank function needs to be apply and where to apply to get above RANK .In my sample data TECH_MODE field have value 5 in 2times ,
first two consecutive 5s needs to have same rank , and Next three consecutive 5s needs to have same rank
Looking at the data some more, if you isolate just on the begin_date and end_date values the previous records end_date is one second behind the next records begin_date. Is this pattern consistent in your data or just how the sample data is put together. What happens if the data looks like this:
INSERT INTO dbo.SAMPLE(REFERENCE_ID,TECH_MODE_ID,BEGIN_TIME,END_TIME)
SELECT 1, 6, '2012-05-03 20:29:22','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' UNION ALL
SELECT 1, 5, '2012-05-03 20:34:51','2012-05-03 20:38:50' UNION ALL
SELECT 1, 5, '2012-05-03 20:38:51','2012-05-03 20:39:50';
pattern is consistent always begin time of records is greater then the end_time of previous record..
Not quite there. Is it always consistent that it is 1 second different? Also, what about the changed data I presented earlier, how would that affect the desired result?
June 13, 2012 at 10:12 am
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,
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply