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

x
?
Solved

SQL Select Total

Posted on 2015-01-26
2
Medium Priority
?
254 Views
Last Modified: 2015-01-26
Hello,
Can you please help,
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
Comment
Question by:W.E.B
2 Comments
 
LVL 70

Accepted Solution

by:
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

by:W.E.B
ID: 40571240
Thank you Scott
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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.

879 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