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

x
Solved

# SQL Select Total

Posted on 2015-01-26
Medium Priority
254 Views
Hello,
is there an simpler way of writing below script?
Also, when i run below, I don't get the distinct Date.
ie: one day on each line

SELECT Distinct (CAST(FinalizedOrders.OrderDate AS DATE)),(SUM (PUDriverCommission)) AS [Commission],
Case when (SUM (PUDriverCommission))< = 80 then 80 else (SUM (PUDriverCommission)) end AS [Commission2]
FROM FinalizedOrders
INNER JOIN FinalizedOrdersDriverSettlements ON FinalizedOrdersDriverSettlements.OrderNo = FinalizedOrders.OrderNo
INNER Join DriverSettlements ON DriverSettlements.SettlementNo = FinalizedOrdersDriverSettlements.Driver1SettlementNo
Where  DriverNumber =  4024 and settlementdate between '01-01-2015' and '01-31-2015' and OrderDate > '01-01-2015'
Group by CAST(FinalizedOrders.OrderDate AS DATE)
UNION ALL
SELECT Distinct (CAST(FinalizedOrders.OrderDate AS DATE)),(SUM (DELDriverCommission)) AS [Commission],
Case when (SUM (DELDriverCommission))< = 80 then 80 else (SUM (DELDriverCommission)) end AS [Commission2]
FROM FinalizedOrders
INNER JOIN FinalizedOrdersDriverSettlements ON FinalizedOrdersDriverSettlements.OrderNo = FinalizedOrders.OrderNo
INNER Join DriverSettlements ON DriverSettlements.SettlementNo = FinalizedOrdersDriverSettlements.Driver2SettlementNo
Where  DriverNumber =  4024 and settlementdate between '01-01-2015' and '01-31-2015' and OrderDate > '01-01-2015'
Group by CAST(FinalizedOrders.OrderDate AS DATE)
UNION ALL
SELECT Distinct (CAST (FinalizedOrders.OrderDate AS DATE)),(SUM (Driver3Commission)) AS [Commission],
Case when (SUM (Driver3Commission))< = 80 then 80 else (SUM (Driver3Commission)) end AS [Commission2]
FROM FinalizedOrders
INNER JOIN FinalizedOrdersDriverSettlements ON FinalizedOrdersDriverSettlements.OrderNo = FinalizedOrders.OrderNo
INNER Join DriverSettlements ON DriverSettlements.SettlementNo = FinalizedOrdersDriverSettlements.Driver3SettlementNo
Where  DriverNumber =  4024 and settlementdate between '01-01-2015' and '01-31-2015' and OrderDate > '01-01-2015'
Group by CAST(FinalizedOrders.OrderDate AS DATE)

Any help is appreciated,
Thanks,
0
Question by:W.E.B

LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 40571180
SELECT
[Day],
[PUDriverCommission], CASE WHEN [PUDriverCommission] <= 80 THEN 80 ELSE [PUDriverCommission] END AS [PUDriverCommission2],
[DELriverCommission], CASE WHEN [DELriverCommission] <= 80 THEN 80 ELSE [DELriverCommission] END AS [DELriverCommission2],
[Driver3Commission],  CASE WHEN [Driver3Commission]  <= 80 THEN 80 ELSE [Driver3Commission]  END AS [DrivereCommission2]
FROM (
SELECT CAST(FinalizedOrders.OrderDate AS date) AS [Day],
SUM (CASE WHEN DriverSettlements.SettlementNo = FinalizedOrdersDriverSettlements.Driver1SettlementNo
THEN PUDriverCommission ELSE 0 END) AS [PUDriverCommission],
SUM (CASE WHEN DriverSettlements.SettlementNo = FinalizedOrdersDriverSettlements.Driver2SettlementNo
THEN DELriverCommission ELSE 0 END) AS [DELDriverCommission],
SUM (CASE WHEN DriverSettlements.SettlementNo = FinalizedOrdersDriverSettlements.Driver3SettlementNo
THEN Driver3Commission ELSE 0 END) AS [Driver3Commission]
FROM FinalizedOrders
INNER JOIN FinalizedOrdersDriverSettlements ON FinalizedOrdersDriverSettlements.OrderNo = FinalizedOrders.OrderNo
INNER Join DriverSettlements ON DriverSettlements.SettlementNo IN (
FinalizedOrdersDriverSettlements.Driver1SettlementNo,
FinalizedOrdersDriverSettlements.Driver2SettlementNo,
FinalizedOrdersDriverSettlements.Driver3SettlementNo
)
WHERE  DriverNumber = 4024 and
settlementdate >= '01-01-2015' and
settlementdate < '02-01-2015' and
OrderDate > '01-01-2015'
GROUP BY CAST(FinalizedOrders.OrderDate AS date)
) AS derived
ORDER BY [Day]
0

Author Closing Comment

ID: 40571240
Thank you Scott
0

## Featured Post

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backupâ€¦
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month9 days, 10 hours left to enroll