November 2, 2012 at 3:46 am
Hi,
I need min,max time from this how to proceed on this
Set of records (1,5,6......) on this bases i need to split and get the min and max time.
Rid,Startdate
1,'2012-11-02 12:45:18.540'
2,'2012-11-02 12:45:18.540'
3,'2012-11-02 12:45:18.541'
4,'2012-11-02 12:45:18.545'
5,'2012-11-02 12:45:18.610'
6,'2012-11-02 12:45:18.640'
7,'2012-11-02 12:45:18.640'
8,'2012-11-02 12:45:18.641'
9,'2012-11-02 12:45:18.745'
10,'2012-11-02 12:45:18.780'
11,'2012-11-02 12:45:18.780'
12,'2012-11-02 12:45:18.780'
.
.
.
So i need to get min, max time of above records which will look like
1-1,'2012-11-02 12:45:18.540','2012-11-02 12:45:18.540'
2-6,'2012-11-02 12:45:18.540','2012-11-02 12:45:18.640'
7-12,'2012-11-02 12:45:18.640','2012-11-02 12:45:18.780'
.
.
.
November 2, 2012 at 4:04 am
can explain in more detail what you want
also post some DDL (create table) and parameter sample which you want to pass to query
November 2, 2012 at 4:08 am
Is any rule for a splitting by Rid?
Will you pass Rid range boundaries as input parameters into stored proc or you will have them stored in some other table?
November 2, 2012 at 4:08 am
you would proably have to create a table that had the Rid's gor each group.
eg
CREATE TABLE #Groups (
GroupStart Int
,GroupEnd Int
,GroupName char(10)
)
CREATE TABLE #RawData (
RId Int
,StartTime dateTime
)
Insert into #RawData
values(1,'2012-11-02 12:45:18.540'),
(2,'2012-11-02 12:45:18.540'),
(3,'2012-11-02 12:45:18.541'),
(4,'2012-11-02 12:45:18.545'),
(5,'2012-11-02 12:45:18.610'),
(6,'2012-11-02 12:45:18.640')
Insert into #Groups
values(1,1,'1-1'),
(2,6,'2-6')
Select
GroupName
,MIN(StartTime)
,MAX(StartTime)
From
#RawData r
JOIN #Groups g on r.RId between g.GroupStart and g.GroupEnd
Group by GroupName
)
If you have disparate groups then you can change this to be
Drop table #Groups
drop table #RawData
CREATE TABLE #Groups (
GroupRow Int
,GroupName char(10)
)
CREATE TABLE #RawData (
RId Int
,StartTime dateTime
)
Insert into #RawData
values(1,'2012-11-02 12:45:18.540'),
(2,'2012-11-02 12:45:18.540'),
(3,'2012-11-02 12:45:18.541'),
(4,'2012-11-02 12:45:18.545'),
(5,'2012-11-02 12:45:18.610'),
(6,'2012-11-02 12:45:18.640')
Insert into #Groups
values(1,'1-1'),
(2,'2-6 ex 4'),
(3,'2-6 ex 4'),
(5,'2-6 ex 4'),
(6,'2-6 ex 4'),
(4,'4-4')
Select
GroupName
,MIN(StartTime)
,MAX(StartTime)
From
#RawData r
JOIN #Groups g on r.RId = g.GroupRow
Group by GroupName
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 2, 2012 at 7:40 am
Thanks it works for me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply