Help with a complex SELECT statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with a complex SELECT statement

Author

Topic

zaroblan
Starting Member

6 Posts

Posted - 2009-03-30 : 08:19:09

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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-30 : 09:21:32

I don't see time stored in the table based on what you have given...

Whats the logic to calculate On time, Off time and duration?

zaroblan
Starting Member

6 Posts

Posted - 2009-03-30 : 09:45:38

Apologies. I left out a field called 'RaisedTime' in each record.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-30 : 09:49:26

[code]select
a.MasterId,
a.UnitId,
b.OnTime,
b.OffTime,
Duration=datediff(hour,b.OnTime,b.OffTime)
from
Your1stTable a
join
RaisedTime b on a.MasterId=b.MasterId and ...<specify the join condition>
where
a.EventTime =1[/code]

zaroblan
Starting Member

6 Posts

Posted - 2009-03-30 : 10:22:51

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,1,30 March 2009 16:15,2

need to produce 1 row with
12345,1,30 March 2009 16:00,30 March 2009 16:15,15

Does this explain better what I am looking for help with?
Thanks again everyone.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-30 : 10:46:22

This?

select MAX(case EventType when 1 then MasterId else 0 end),
UnitId,
MAX(case EventType when 1 then RaisedTime else 0 end),
MAX(case EventType when 2 then RaisedTime else 0 end)
from Activationdata
group by UnitId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources