W.E.B
asked on
SQL Select Total
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.Orde rDate AS DATE)),(SUM (PUDriverCommission)) AS [Commission],
Case when (SUM (PUDriverCommission))< = 80 then 80 else (SUM (PUDriverCommission)) end AS [Commission2]
FROM FinalizedOrders
INNER JOIN FinalizedOrdersDriverSettl ements ON FinalizedOrdersDriverSettl ements.Ord erNo = FinalizedOrders.OrderNo
INNER Join DriverSettlements ON DriverSettlements.Settleme ntNo = FinalizedOrdersDriverSettl ements.Dri ver1Settle mentNo
Where DriverNumber = 4024 and settlementdate between '01-01-2015' and '01-31-2015' and OrderDate > '01-01-2015'
Group by CAST(FinalizedOrders.Order Date AS DATE)
UNION ALL
SELECT Distinct (CAST(FinalizedOrders.Orde rDate AS DATE)),(SUM (DELDriverCommission)) AS [Commission],
Case when (SUM (DELDriverCommission))< = 80 then 80 else (SUM (DELDriverCommission)) end AS [Commission2]
FROM FinalizedOrders
INNER JOIN FinalizedOrdersDriverSettl ements ON FinalizedOrdersDriverSettl ements.Ord erNo = FinalizedOrders.OrderNo
INNER Join DriverSettlements ON DriverSettlements.Settleme ntNo = FinalizedOrdersDriverSettl ements.Dri ver2Settle mentNo
Where DriverNumber = 4024 and settlementdate between '01-01-2015' and '01-31-2015' and OrderDate > '01-01-2015'
Group by CAST(FinalizedOrders.Order Date 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 FinalizedOrdersDriverSettl ements ON FinalizedOrdersDriverSettl ements.Ord erNo = FinalizedOrders.OrderNo
INNER Join DriverSettlements ON DriverSettlements.Settleme ntNo = FinalizedOrdersDriverSettl ements.Dri ver3Settle mentNo
Where DriverNumber = 4024 and settlementdate between '01-01-2015' and '01-31-2015' and OrderDate > '01-01-2015'
Group by CAST(FinalizedOrders.Order Date AS DATE)
Any help is appreciated,
Thanks,
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.Orde
Case when (SUM (PUDriverCommission))< = 80 then 80 else (SUM (PUDriverCommission)) end AS [Commission2]
FROM FinalizedOrders
INNER JOIN FinalizedOrdersDriverSettl
INNER Join DriverSettlements ON DriverSettlements.Settleme
Where DriverNumber = 4024 and settlementdate between '01-01-2015' and '01-31-2015' and OrderDate > '01-01-2015'
Group by CAST(FinalizedOrders.Order
UNION ALL
SELECT Distinct (CAST(FinalizedOrders.Orde
Case when (SUM (DELDriverCommission))< = 80 then 80 else (SUM (DELDriverCommission)) end AS [Commission2]
FROM FinalizedOrders
INNER JOIN FinalizedOrdersDriverSettl
INNER Join DriverSettlements ON DriverSettlements.Settleme
Where DriverNumber = 4024 and settlementdate between '01-01-2015' and '01-31-2015' and OrderDate > '01-01-2015'
Group by CAST(FinalizedOrders.Order
UNION ALL
SELECT Distinct (CAST (FinalizedOrders.OrderDate
Case when (SUM (Driver3Commission))< = 80 then 80 else (SUM (Driver3Commission)) end AS [Commission2]
FROM FinalizedOrders
INNER JOIN FinalizedOrdersDriverSettl
INNER Join DriverSettlements ON DriverSettlements.Settleme
Where DriverNumber = 4024 and settlementdate between '01-01-2015' and '01-31-2015' and OrderDate > '01-01-2015'
Group by CAST(FinalizedOrders.Order
Any help is appreciated,
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER