March 30, 2009 at 6:15 am
Greetings,
I have table with the following columns
MasterId int identity column
UnitId int
EventTime int (will be a 1 when switched on and a 2 when switched off)
I need to return a result set in the following format:
MasterId, UnitId, OnTime, OffTime, Duration
Where MasterId would be the Master Id of the record for the On event
Please can someone help me?
Thanks,
Robert
March 30, 2009 at 7:14 am
You aren't showing any dates being stored so how would you get the duration? You need at least at start time stored in order to get a duration.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2009 at 7:44 am
Whoops apologies.
Each row had a DateTime field called 'RaisedTime'
March 30, 2009 at 7:55 am
Can you post the full table structure and some sample data like suggested in my the articles in my signature? At this point we also do not know what is the PK on the table and how we can relate the start row with the end row (UnitID or MasterID).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2009 at 8:25 am
Table structure as follows:
CREATE TABLE AcivationData
(
MasterId int identity primary key nonclustered,
UnitId int not null,
RaisedTime datetime,
EventType int
)
e.g.
12345,1,30 March 2009 16:00,1
12346,2,30 March 2009 16:12,1
12347,1,30 March 2009 16:15,2
12348,2,30 March 2009 16:20,2
need to produce 2 rows with
12345,1,30 March 2009 16:00,30 March 2009 16:15,15
12346,2,30 March 2009 16:12,30 March 2009 16:20,8
Example shows durations in minutes, I may be going down to seconds
Does this explain better what I am looking for help with?
Thanks again.
March 30, 2009 at 8:38 am
This should get you started. It does not handle if you have multiple starts and stops for a Unit. You can check out this article[/url] for some ideas on that:
CREATE TABLE #ActivationData
(
MasterId int primary key nonclustered,
UnitId int not null,
RaisedTime datetime,
EventType int
)
INSERT INTO
#ActivationData
SELECT
12345,
1,
'30 March 2009 16:00',
1
UNION ALL
SELECT
12346,
2,
'30 March 2009 16:12',
1
UNION ALL
SELECT
12347,
1,
'30 March 2009 16:15',
2
UNION ALL
SELECT
12348,
2,
'30 March 2009 16:20',
2
SELECT
AD.MasterId,
AD.UnitId,
AD.RaisedTime AS StartTime,
AD2.RaisedTime AS EndTime,
DATEDIFF(Minute, AD.RaisedTime, AD2.raisedTime) AS duration_in_minutes
FROM
#ActivationData AS AD JOIN
#ActivationData AS AD2
ON AD.UnitId = AD2.UnitId AND
AD.EventType + 1 = AD2.EventType
DROP TABLE #ActivationData
Also note how I modified your example data to populate the table so I could fairly easily run some tests.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2009 at 9:21 am
Hi Jack,
The example work well for thelimited data set.
The problem comes in where there are hundreds of records.
For every record with Event Type 1 you are getting you are getting a record for every single row where event type = 2
There should only be one row for each EventType =1 and the nearest EventType = 2 combination.
This should be the EventType = 2 with the RaisedTime immediately following the record with the EventType = 1 if you know what I mean?
This is why I am coming so unstuck!
Thanks,
Robert
March 30, 2009 at 9:42 am
Right, that's what I meant when I said
It does not handle if you have multiple starts and stops for a Unit
You need to remember that we can only give ideas/solutions based on the information/data you provide and that we are doing this as volunteers, so we have limited time to work on solutions.
BTW-it would be easier to solve this problem in 2005/2008 than 2000 which I assume you are using since you posted in a 2000 forum.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2009 at 10:06 am
Thanks Jack for all you help!
I do appreciate all you help and understand fully what is means to provide this kind of assistance as a volunteer. I do so myself in other forums on the net.
I also apologise for not providing sufficient detailed information when posting my question. I will endeavour in future to provide better input.
This particular problem has completely stymied me for days now.
The best solution I have come up with is to:
1) extract all the records for the start times and place it in a temporary table
2) create a cursor over this data
3) cursor through all the data from point 1 looking for the end data where eventtype = 2
4) if any is found I then update the date
This is a long winded approach but I am sure there must be a better way 🙂
Thanks again for all your help.
I do appreciate it!
March 30, 2009 at 10:07 am
I wish you used SQL 2005, as this can be easily done using ROW_NUMBER function. However, you can still achieve the same in SQL 2000 using three different ways that I know (and there can be many more ways too).
1. Special Update Method (using Variable, Indexes & Hints) is explained here in Jeff's article [/url]
2. Correlated Query (see this link)
3. Cursors (see this link)
Method 1 performs far better than Method 2 & 3 in most of the cases & Method 2 & 3 performs somewhat similar, but in some cases cursors performs better if it has written in the correct way.
Since Method 1 & 2 requires lot of time to write, I keep it to you to write and understand what exactly they do, I am providing you the correlated version
SELECT
MasterId, UnitId, StartTime, EndTime,
DATEDIFF( Minute, StartTime, EndTime ) AS duration_in_minutes
FROM(
SELECT
AD.MasterId,
AD.UnitId,
AD.RaisedTime AS StartTime,
( SELECT TOP 1 RaisedTime FROM #ActivationData AS AD2 WHERE AD.UnitId = AD2.UnitId AND AD2.EventType = 2 AND AD.RaisedTime <= AD2.RaisedTime ORDER BY AD2.RaisedTime ASC ) AS EndTime
FROM
#ActivationData AS AD
WHEREAD.EventType = 1
) A
--Ramesh
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply