Solved

SQL Select Total

Posted on 2015-01-26
2
208 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 69

Accepted Solution

by:
Scott Pletcher earned 500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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 Copy Database Wizard 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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

777 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