query table from another table

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

General SQL Server Forums

New to SQL Server Programming

query table from another table

Author

Topic

Gallantman
Starting Member

2 Posts

Posted - 2011-05-23 : 11:03:05

Hi,

I'm new to this formum so hopefully I've posted to the correct forum. I need help with querying an SQL db.

I have included a snap shop of my table - I have my main event DOWNTIME plus mini-events called "STATES". I want to group STATES by DOWNTIME but each entry has a unique ID so its hard to achieve this. e.g. return all STATES where time between 9:26 and 10:20 ??

Can I export all Events = DOWNTIME to a temp table and then query my main table again where tStart and tEnd between temp:tStart and temp:tEnd.

I'm new to SQL so would appreciate any help you can offer. Hopefully I've made myself clear.

Mark

Id Event tStart tEnd
1025237 DOWNTIME 09:26 10:20
1025238 STATES 09:26 09:29
1025240 STATES 09:29 09:31
1025241 STATES 09:31 10:20
1025242 DOWNTIME 10:20 11:05
....

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-23 : 11:29:25

Depends on what you want
select a.tstart, a.tend,
startstart = case when event = 'downtime' then null else b.tstart end ,
stateend = case when event = 'downtime' then null else b.tend end
from
(select tstart, tend from tbl where Event = 'DOWNTIME') a
join tbl b
on tstart betwen a.tstart and a.tend

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Gallantman
Starting Member

2 Posts

Posted - 2011-05-23 : 16:26:35

Thanks Nigel,

I guess what I need is to group the STATES by DOWNTIME. i.e. end up with a result like this ..either with the same id or a new unique id per group (I'm guessing to a new table). Any suggestions appreciated.

Id Event tStart tEnd
1025237 DOWNTIME 09:26 10:20
1025237 STATES 09:26 09:29
1025237 STATES 09:29 09:31
1025237 STATES 09:31 10:20
1025242 DOWNTIME 10:20 11:05

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources