Reducing 3 statements to 1

Question

Post reply

Reducing 3 statements to 1

Alan G-436699

Mr or Mrs. 500

Points: 504
More actions
March 18, 2008 at 8:31 am

#379251

Hi, I have 3 statements below which I'm sure should really be one statement but I can't see how to do it. Can anyone help?
selectTransactionID,
sum(Agent_Perc) as SumGrossCommPerc
into#CommGross
frommstt_Commissions Comm
whereComm.CommissionType = 1
andComm.GrossOrNet = 'G'
groupby TransactionID
selectTransactionID,
sum(Agent_Perc) as SumNetCommPerc
into#CommNet
frommstt_Commissions Comm
whereComm.CommissionType = 1
andComm.GrossOrNet = 'N'
groupby TransactionID
selectTransactionID,
1 as CommissionType,
SumGrossCommPerc,
SumNetCommPerc
into#CommAll
from#CommGross gross
left outer join #CommNet net on gross.TransactionID = net.TransactionID

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Sure! coming right up...

select TransactionID,

sum(case when Comm.GrossOrNet = 'G' then Agent_Perc else 0 end) as SumGrossCommPerc,

sum(case when Comm.GrossOrNet = 'N' then Agent_Perc else 0 end) as SumNetCommPerc,

from mstt_Commissions Comm

where Comm.CommissionType = 1

group by TransactionID

You could select this INTO #comm_all if you still need that temp table for something.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Steve Jones - SSC Editor SSC Guru Points: 734498 More actions · Answer 2

I'm not sure how you can combine these when they are all going to separate temp tables. The issue is that if these are used later, then combining them might cause issues.

Otherwise, you can use what's been suggested

Alan G-436699 Mr or Mrs. 500 Points: 504 More actions · Answer 3

Thanks! I thought it would involve a case statement but I couldn't think how to code it. Cheers!

Adam Haines SSC-Insane Points: 23217 More actions · Answer 4

Another solution, very similar to Matt's except this one filter to only get the records that have a gross or net = G or N.

SELECT TransactionID,

CommissionType,

SUM(CASE WHEN GrossOrNet = 'G' THEN Agent_Perc ELSE 0 END) AS [SumGrossCommPerc],

SUM(CASE WHEN GrossOrNet = 'N' THEN Agent_Perc ELSE 0 END) AS [SumNetCommPerc],

FROM mstt_Commissions

WHERE (CommissionType = 1 AND GrossOrNet = 'G') OR

(CommissionType = 1 AND GrossOrNet = 'N')

GROUP BY TransactionID, CommissionType

My blog: http://jahaines.blogspot.com

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Adam Haines (3/18/2008)
Another solution, very similar to Matt's except this one filter to only get the records that have a gross or net = G or N.
SELECT TransactionID,
CommissionType,
SUM(CASE WHEN GrossOrNet = 'G' THEN Agent_Perc ELSE 0 END) AS [SumGrossCommPerc],
SUM(CASE WHEN GrossOrNet = 'N' THEN Agent_Perc ELSE 0 END) AS [SumNetCommPerc],
FROM mstt_Commissions
WHERE (CommissionType = 1 AND GrossOrNet = 'G') OR
(CommissionType = 1 AND GrossOrNet = 'N')
GROUP BY TransactionID, CommissionType

Good thought - but grouping by Commissiontype will now go back to putting the gross and the net on separate lines. How about...