UNION Query , maybe?… | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

UNION Query , maybe?…

I am fairly new to SQL and I am looking for some help or suggestions. The following produces duplicate results as shown below: SELECT DriverDataCourier.Class, DriverDataCourier.DriverNo, DriverPay.Pay,
CASE WHEN [Description] In (‘Bonus’)THEN [Amount]ELSE 0 END AS Bonus,
CASE WHEN [Description] In (‘Device Lease’)THEN [Amount]ELSE 0 END AS RadioLease,
CASE WHEN [Description] In (‘Uniform Lease’)THEN [Amount]ELSE 0 END AS UniformLease
FROM DriverPRAdjustment INNER JOIN (DriverDataCourier INNER JOIN DriverPay ON DriverDataCourier.DriverNo = DriverPay.DriverNo) ON DriverPRAdjustment.DriverNo = DriverPay.DriverNo
ORDER BY DriverDataCourier.Class, DriverDataCourier.DriverNo; results:
D-AREA 002045333.7100.0000.0000.0000
D-AREA 002045333.7100.0000.0000.0000
D-AREA 002045333.7100.0000-20.0000.0000
D-AREA 002045333.7100.0000.0000.0000
D-AREA 002045333.7100.0000.0000-10.0000
D-AREA 002084308.3800.0000.0000-10.0000
D-AREA 002084308.3800.0000.0000.0000
D-AREA 002084308.3800.0000-20.0000.0000
D-AREA 002084308.3800.0000.0000.0000
D-AREA 002084308.3800.0000.0000.0000 How can I get the results to look like this:
D-AREA 002045333.7100.0000-20.0000 -10.0000
D-AREA 002084308.3800.0000-20.0000 -10.0000 Again, any help is appreciated!
SELECT DriverDataCourier.Class, DriverDataCourier.DriverNo, DriverPay.Pay,
MIN(CASE WHEN [Description] In (‘Bonus’)THEN [Amount]ELSE 0 END) AS Bonus,
MIN(CASE WHEN [Description] In (‘Device Lease’)THEN [Amount]ELSE 0 END) AS RadioLease,
MIN(CASE WHEN [Description] In (‘Uniform Lease’)THEN [Amount]ELSE 0 END) AS UniformLease
FROM DriverPRAdjustment INNER JOIN (DriverDataCourier INNER JOIN DriverPay ON DriverDataCourier.DriverNo = DriverPay.DriverNo) ON DriverPRAdjustment.DriverNo = DriverPay.DriverNo
GROUP NY DriverDataCourier.Class, DriverDataCourier.DriverNo, DriverPay.Pay
ORDER BY DriverDataCourier.Class, DriverDataCourier.DriverNo
But this will work only when you want to select the leat value for Bonus, RadioLease and UniformLease.
Hope this works… Gaurav
BTW where is the join condition for DriverPRAdjustment and DriverDataCourier? Are you missing some piece? Gaurav
…And you may want to clean up the INNER JOINS and the confusing brackets around them. As with Gaurav’s solution, the solution actually lies in aggregating the outputs of the CASE statements and grouping. So you could replace the SUM or Gaurav’s MIN (or even MAX) with the exact logic that you actually want to implement. SELECT ddc.Class, ddc.DriverNo, dp.Pay,
SUM(CASE WHEN dpa.Description = ‘Bonus’ THEN dpa.Amount ELSE 0 END) AS Bonus,
SUM(CASE WHEN dpa.Description = ‘Device Lease’ THEN dpa.Amount ELSE 0 END) AS RadioLease,
SUM(CASE WHEN dpa.Description = ‘Uniform Lease’ THEN dpa.Amount ELSE 0 END) AS UniformLease
FROM DriverDataCourier ddc INNER JOIN DriverPay dp ON
ddc.DriverNo = dp.DriverNo INNER JOIN DriverPRAdjustment dpa ON
dpa.DriverNo = dp.DriverNo
GROUP BY ddc.Class, ddc.DriverNo, dp.Pay
ORDER BY ddc.Class, ddc.DriverNo
I just aliased the table names to make it easier to read the column names in the query (I just like it that way). NHO
I think brackets are very much a personal convention. I was confused by guaravs brackets a little too, but then your solution used no brackets at all <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />Personally i’ll wrap any ON criteria in brackets purely for readability, but each to his own I guess!
Hey guys I was just trying to reuse the code posted. In turn saving my typing time <img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ /><br /><br />Gaurav
Thank you very much, your help is much appreciated!!
]]>