query syntax help 1

[woyler]

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

 

[gmmastros]

[COLOR=blue]Select[/color]	[COLOR=#FF00FF]Coalesce[/color](A.Serial, B.Serial) [COLOR=blue]As[/color] Serial,
        [COLOR=#FF00FF]Coalesce[/color](UpTime, 0) [COLOR=blue]As[/color] UpTime,
        [COLOR=#FF00FF]Coalesce[/color](DownTime, 0) [COLOR=blue]As[/color] DownTime
[COLOR=blue]From[/color]    (
        [COLOR=blue]Select[/color] Serial, Sum(UPTIME_MINUTES) [COLOR=blue]As[/color] Uptime
        [COLOR=blue]From[/color]   TableA
        [COLOR=blue]Group[/color] [COLOR=blue]By[/color] Serial
        ) [COLOR=blue]As[/color] A
        [COLOR=blue]Full[/color] [COLOR=blue]Outer[/color] [COLOR=blue]Join[/color] (
          [COLOR=blue]Select[/color] Serial, Sum(DOWNTIME_MINUTES) [COLOR=blue]As[/color] DownTime
          [COLOR=blue]From[/color]   TableB
          [COLOR=blue]Where[/color]  Reason = [COLOR=red]'POWER'[/color]
          [COLOR=blue]Group[/color] [COLOR=blue]By[/color] Serial
        ) [COLOR=blue]As[/color] B
        [COLOR=blue]On[/color] A.Serial = B.Serial

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Lamprey13]

Two things:
1. Assuming you do not need the FULL OUTER JOIN you could change Georges query to use a LEFT OUTER JOIN and that should make it slightly faster.

2. Depending on your data size a correlated sub query might be faster (but I'd probably stick with a join):

DECLARE @TableA TABLE (SERIAL VARCHAR(4), UPTIME_MINUTES INT)

INSERT @TableA
SELECT 'A123', 60
UNION SELECT 'B456', 50
UNION SELECT 'C789', 40
    
DECLARE @TableB TABLE(SERIAL VARCHAR(4), REASON VARCHAR(5), DOWNTIME_MINUTES INT)

INSERT @TableB
SELECT 'A123', 'POWER', 10
UNION SELECT 'A123', 'WATER', 20
UNION SELECT 'A123', 'POWER',50
UNION SELECT 'B456', 'ICE',10
UNION SELECT 'C789', 'FIRE',50
UNION SELECT 'C789', 'POWER', 40


SELECT 
	a.Serial,
	SUM(UPTIME_MINUTES) AS UpTime,
	COALESCE((SELECT SUM(b.DOWNTIME_MINUTES) FROM @TableB b WHERE b.SERIAL = a.SERIAL AND b.REASON = 'Power'), 0) AS DownTime
FROM
	@TableA AS a
GROUP BY
	a.Serial

 

[gmmastros]

Lamprey,

The reason I used a full outer join is to allow there to be a downtime record without a corresponding uptime record. Of course, there may be constraints that prevent this, but it wasn't stated.

So, for example, if you add:
UNION SELECT 'D987', 'POWER', 100
to your @TableB, the left join query will not show this record because there is no corresponding uptime record. The full outer join query will show it.

As for performance... I think woyler would be better off creating a lookup table for the reason and only storing an integer id in the table. But that's another question for another day.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom