Complex Query

[cianobi]

HElp with the answer to the following...

TRS table weekhours currently has several fields including the following (simplified structure):

Assoc_ID
Project_ID
Category_code
Hours

If an associate (ABC) enters 4 hours productive, 2 hours TAFW, 2 hours idle and
another associate (DEF) enters 6 hours productive, 1 hours TAFW, 1 hours idle
then weekhours looks like this.

Assoc Proj Cat Hours
ABC XYZ PROD 4
ABC XYZ TAFW 2
ABC XYZ IDLE 2
DEF XYZ PROD 6
DEF XYZ TAFW 1
DEF XYZ IDLE 1

Assuming there are only 3 categories (not to be changed in next 5 years), you have to report this vertical storage as horizontal as follows:

Assoc Proj Prod TAFW IDLE
ABC XYZ 4 2 2
DEF XYZ 6 1 1

Condition - Only one select allowed. No temp table allowed. Hardcoding on CAT field is allowed.

 

[mwolf00]

SELECT
t.assoc
, t.proj
, sum(tProd.Hours)
, sum(tAFW.Hours)
, sum(tIdle.Hours)
FROM
myTable t
LEFT JOIN myTable tProd on t.assoc = tprod.assoc AND t.proj = tProd.proj AND tProd.Cat = 'PROD'
LEFT JOIN myTable tAFW on t.assoc = tAFW.assoc AND t.proj = tAFW.proj AND tAFW.Cat = 'TAFW'
LEFT JOIN myTable tIdle on t.assoc = tIdle.assoc AND t.proj = tIdle.proj AND tIdle.Cat = 'IDLE'
GROUP BY t.assoc, t.proj

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[cianobi]

This would work, but I'm looking to do it without using any joins as the tables are very large and it will take a long time to process...

 

[nickpark]

Hmm no joins eh. Use a cursor. Especially if you are already ordered by Assoc and Project.

declare cursor as select * from weekhours order by proj, assoc

fetch into....

while @fetch_status == 0
begin

  if @assoc = @prev_assoc and @proj = @prev_proj then
  begin
    if @cat = "PROD" then select @total_PROD += @PROD
    else if @cat = "TAFW" then select @total_TAPF += @TAFW
    else if @cat = "IDLE" then select @total_IDLE += @IDLE
    else raiseerror
  end
  else
  begin
    insert into results values(@total_PROD, @total_TPAF, @total_IDLE)
    select @total_PROD=0, @total_TPAF=0, @total_IDLE=0
  end
  select @prev_assoc = @assoc, @prev_proj = @proj

end

Sorry. I hate cursors. Wolfies answer is pure.

 

[DFC4000]

SELECT
Assoc,
Proj,
SUM(CASE WHEN Cat='PROD' THEN Hours ELSE 0 END) as PROD,
SUM(CASE WHEN Cat='TAFW' THEN Hours ELSE 0 END) as TAFW,
SUM(CASE WHEN Cat='IDLE' THEN Hours ELSE 0 END) as IDLE
FROM MyTable
GROUP BY Assoc, Proj

Think the above should work, cannot test on this machine - Standard Crosstab?