January 26, 2015 at 12:41 pm
Hi,
I don't get the distinct date in the below query. Anything wrong in this query. Also may i short this query? If yes, Please advise me.
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)
Thanks
January 26, 2015 at 12:56 pm
Why do you believe that you need to use DISTINCT? You already have a GROUP BY.
Are you seeing the same date 3 times? If so, that's normal because you're "concatenating" data sets using UNION ALL.
A better way to do this would be by normalizing the tables, but that's just speculation and a wild guess as I don't know your tables.
January 26, 2015 at 1:29 pm
shasivashok (1/26/2015)
Hi,I don't get the distinct date in the below query. Anything wrong in this query. Also may i short this query? If yes, Please advise me.
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)
Thanks
Use table aliases:
SELECT Distinct
(CAST(fo.OrderDate AS DATE)),
(SUM (PUDriverCommission)) AS [Commission],
Case when (SUM (PUDriverCommission))< = 80 then 80 else (SUM (PUDriverCommission)) end AS [Commission2]
FROM FinalizedOrders fo
INNER JOIN FinalizedOrdersDriverSettlements ds ON ds.OrderNo = fo.OrderNo
INNER Join DriverSettlements s ON s.SettlementNo = ds.Driver1SettlementNo
Where DriverNumber = 4024
and settlementdate between '01-01-2015' and '01-31-2015' and OrderDate > '01-01-2015'
Group by CAST(fo.OrderDate AS DATE)
UNION ALL
SELECT Distinct
(CAST(fo.OrderDate AS DATE)),
(SUM (DELDriverCommission)) AS [Commission],
Case when (SUM (DELDriverCommission))< = 80 then 80 else (SUM (DELDriverCommission)) end AS [Commission2]
FROM FinalizedOrders fo
INNER JOIN FinalizedOrdersDriverSettlements ds ON ds.OrderNo = fo.OrderNo
INNER Join DriverSettlements s ON s.SettlementNo = ds.Driver2SettlementNo
Where DriverNumber = 4024
and settlementdate between '01-01-2015' and '01-31-2015' and OrderDate > '01-01-2015'
Group by CAST(fo.OrderDate AS DATE)
UNION ALL
SELECT Distinct
(CAST (fo.OrderDate AS DATE)),
(SUM (Driver3Commission)) AS [Commission],
Case when (SUM (Driver3Commission))< = 80 then 80 else (SUM (Driver3Commission)) end AS [Commission2]
FROM FinalizedOrders fo
INNER JOIN FinalizedOrdersDriverSettlements ds ON ds.OrderNo = fo.OrderNo
INNER Join DriverSettlements s ON s.SettlementNo = ds.Driver3SettlementNo
Where DriverNumber = 4024
and settlementdate between '01-01-2015' and '01-31-2015' and OrderDate > '01-01-2015'
Group by CAST(fo.OrderDate AS DATE)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 26, 2015 at 1:38 pm
There are other ways, this is possibly the simplest. You will need to plug in missing table aliases to disambiguate columns:
SELECT --Distinct
CAST(fo.OrderDate AS DATE),
SUM(s1.PUDriverCommission) AS [Commission1],
SUM(s2.DELDriverCommission) AS [Commission2],
SUM(s3.Driver3Commission) AS [Commission3],
Case when SUM(s1.PUDriverCommission) <= 80 then 80 else SUM(s1.PUDriverCommission) end AS [Commission2_1],
Case when SUM(s2.DELDriverCommission) <= 80 then 80 else SUM(s2.DELDriverCommission) end AS [Commission2_2],
Case when SUM(s3.Driver3Commission) <= 80 then 80 else SUM(s3.Driver3Commission) end AS [Commission2_3]
FROM FinalizedOrders fo
INNER JOIN FinalizedOrdersDriverSettlements ds ON ds.OrderNo = fo.OrderNo
LEFT JOIN DriverSettlements s1 ON s1.SettlementNo = ds.Driver1SettlementNo
LEFT JOIN DriverSettlements s2 ON s2.SettlementNo = ds.Driver2SettlementNo
LEFT JOIN DriverSettlements s3 ON s3.SettlementNo = ds.Driver3SettlementNo
WHERE DriverNumber = 4024
AND settlementdate between '01-01-2015' and '01-31-2015'
and OrderDate > '01-01-2015'
Group by CAST(fo.OrderDate AS DATE)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply