Solved

SQL Select Total

Posted on 2015-01-26
2
219 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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