syntax help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

syntax help

Author

Topic

woyler
Starting Member

3 Posts

Posted - 2007-05-30 : 13:16:39

Hi All, Can someone give me a hand with the syntax to accomplish the following?

Table A
SERIAL UPTIME_MINUTES
A123 60
B456 50
C789 40

Table B
SERIAL REASON DOWNTIME_MIUNTES
A123 POWER 10
A123 WATER 20
A123 POWER 50
B456 ICE 10
C789 FIRE 50
C789 POWER 40

desired results
I want to return all rows (with totals for UPTIME_MINUTES) from A and totals from B where REASON = 'POWER'

SERIAL UPTIME DOWNTIME
A123 60 60
B456 50 0
C789 40 40

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-30 : 13:23:39

select SERIAL, UPTIME_MINUTES = sum(UPTIME_MINUTES), DOWNTIME_MIUNTES = sum(DOWNTIME_MIUNTES)
from
(
select SERIAL, UPTIME_MINUTES, DOWNTIME_MIUNTES = 0 from A
union all
select SERIAL, UPTIME_MINUTES = 0, DOWNTIME_MIUNTES from B where REASON = 'POWER'
) a
group by SERIAL

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

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-05-31 : 00:49:05

Select a.Serial, a.Uptime, isnull(b.Downtime, 0) as Downtime
from TableA a
left outer join (Select serial, sum(downtime) downtime from Tableb where reason = 'Power' group by serial) b
on a.serial = b.serial

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources