Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Group

Posted on 2014-03-27
4
Medium Priority
?
285 Views
Last Modified: 2014-03-27
Hello,
can you please help,
I use below code to get Driver payments from SQL 2008
Can you please help me group them in a way that I only get one Total number per Driver.
Please see attached.

Select PickupDriver AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = AO.PickupDriver)  AS [Driver Name],PUDriverCommission,
DeliveryDriver AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = AO.DeliveryDriver)  AS [Driver Name],DELDriverCommission,
Driver3 AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = AO.Driver3)  AS [Driver Name],Driver3Commission,
Driver4 AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = AO.Driver4)  AS [Driver Name],Driver4Commission,
Driver5 AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = AO.Driver5)  AS [Driver Name],Driver5Commission
From ActiveOrders AO
UNION ALL
Select PickupDriver AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = FO.PickupDriver)  AS [Driver Name],PUDriverCommission,
DeliveryDriver AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = FO.DeliveryDriver)  AS [Driver Name],DELDriverCommission,
Driver3 AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = FO.Driver3)  AS [Driver Name],Driver3Commission,
Driver4 AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = FO.Driver4)  AS [Driver Name],Driver4Commission,
Driver5 AS [Driver#], (Select Name from Drivers where Drivers.DriverNumber = FO.Driver5)  AS [Driver Name],Driver5Commission
From FinalizedOrders FO
where orderno in (select orderno from FinalizedOrdersDriverSettlements where Driver1SettlementNo is null)

Thank you for your help,
Sample.xlsx
0
Comment
Question by:W.E.B
  • 2
  • 2
4 Comments
 
LVL 5

Expert Comment

by:jayakrishnabh
ID: 39959009
;with cte(
select [Driver#] as Driver, [Driver Name] as Dname, [PUDriverCommission] as Commission From YourTable where [Driver#] is not null
UNION ALL
select [Driver#] as Driver, [Driver Name] as Dname, [DELDriverCommission] as Commission  From YourTable where [Driver#] is not null
UNION ALL
select [Driver#] as Driver, [Driver Name] as Dname, [Driver3Commission] as Commission From YourTable where [Driver#] is not null
UNION ALL
select [Driver#] as Driver, [Driver Name] as Dname, [Driver4Commission] as Commission From YourTable where [Driver#] is not null
UNION ALL
select [Driver#] as Driver, [Driver Name] as Dname, [Driver5Commission] as Commission From YourTable where [Driver#] is not null
)
select Driver, SUM(ISNULL(Commission,0))
from cte
group by Driver
0
 

Author Comment

by:W.E.B
ID: 39959047
Hello,
thank you for the help,

I'm having a problem applying your suggestion,
how do I use this on 2 tables.
I have Activeorders, FinalizedOrders

I appreciate if you can show me the whole code, this way I can learn how to.

thanks
0
 
LVL 5

Accepted Solution

by:
jayakrishnabh earned 2000 total points
ID: 39959063
;with cte1 as (
select [Driver#] as Driver, [Driver Name] as Dname, [PUDriverCommission] as Commission From Activeorders where [Driver#] is not null
UNION ALL
select [Driver#] as Driver, [Driver Name] as Dname, [DELDriverCommission] as Commission  From FinalizedOrders where [Driver#] is not null
)
select Driver, SUM(ISNULL(Commission,0))
from cte1
group by Driver
0
 

Author Closing Comment

by:W.E.B
ID: 39959169
Thank you very much.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question