Solved

SQL Group

Posted on 2014-03-27
4
265 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 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 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