CODEWITHSUNDEEP
sqlsql-server-2008selectgroup-bysum
user2393886
user2393886

Reputation: 872

Joining issue with sql query

We are using shipworks application to manage our orders. With this, we have also developed a small tool which helps in generating different kind of reports based on shipworks data.

So, we are facing little problem with generating reports for sales by Customer. In this report, we are fetching following information:
1. Customer ID, BillFirstName, BillMiddleName, BillLastName, OrderTotal as total from Order table. 2. SalePrice from OrderItem table. Saleprice is the (quantity * unitprice). OrderItem table saves the products with quantity and unit price ordered in a particular order. We have relationship between Order and OrderItem table through OrderID. 3. Discount, Taxes and freight from OrderCharge table. Discount is the sum of all the amounts which have corresponding type column values (COUPON, PROMOTION DISCOUNT, REWARD, CREDIT, PROMOTION, FACEBOOK_FAN_REWARD, VOUCHER) and Taxes is the sum of amount which have corresponding type column values (TAX) and freight is sum of amount which have corresponding type column values (SHIPPING). We have relationship between OrderCharge and Order table through OrderID.

OverAll, we are fetching the above data against each customerID. We also pass some store condition and date condition to get data i.e order belongs to store as we passed and belongs to passed start and end date.

We have made the query for above as: 

select derived.CustomerID, derived.BillFirstName, derived.BillMiddleName,   
derived.BillLastName, SUM(oi.quantity*oi.UnitPrice) as saleprice, derived.freight,   
derived.tax, sum(o.OrderTotal) as total, derived.discount 
from (select o.CustomerID, o.BillFirstName, o.BillMiddleName, o.BillLastName
,SUM(oc.amount) as freight, SUM(oc2.amount) as tax, SUM(oc3.amount) as discount 
from [Order] o   
inner join OrderCharge oc on o.orderID = oc.orderID 
                         and oc.[Type] = 'SHIPPING'  
inner join OrderCharge oc2 on o.orderID = oc2.orderID 
                         and oc2.Type = 'TAX'  
inner join OrderCharge oc3 on o.orderID = oc3.orderID 
                         and (oc3.Type = 'COUPON' 
                              or oc3.Type = 'PROMOTION DISCOUNT' 
                              or oc3.Type = 'REWARD' 
                              or oc3.Type = 'CREDIT' 
                              or oc3.Type = 'PROMOTION' 
                              or oc3.Type = 'FACEBOOK_FAN_REWARD' 
                              or oc3.Type = 'VOUCHER')  
where o.StoreID IN(12005,17005,1005,20005,19005) 
and (o.OrderDate between '2013-11-01 00:00:00' and '2013-12-27 23:59:00') 

group by o.CustomerID, o.BillFirstName,  
o.BillMiddleName, o.BillLastName) as derived 

inner join [Order] o on o.CustomerID =  derived.CustomerID 

inner join orderItem oi on o.orderID = oi.orderID  

group by derived.CustomerID, derived.BillFirstName, derived.BillMiddleName,  
derived.BillLastName, derived.freight, derived.tax, derived.discount

The above query giving us right data but not summing the discount, freight and taxes. Can any one please suggest me other solution or tell me what is the wrong with the above query?

Waiting for reply

Upvotes: 1

Views: 114

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT o.CustomerID, o.BillFirstName, o.BillMiddleName, o.BillLastName, 
       SUM(B.saleprice) AS saleprice, SUM(A.freight) AS freight, 
       SUM(A.tax) AS tax, SUM(o.OrderTotal) AS total, SUM(A.discount) AS discount
FROM ORDER o   
INNER JOIN (SELECT orderID, 
                   SUM(CASE WHEN oc.TYPE = 'SHIPPING' THEN oc.amount ELSE 0 END) AS freight,  
                   SUM(CASE WHEN oc.TYPE = 'TAX' THEN oc.amount ELSE 0 END) AS tax, 
                   SUM(CASE WHEN oc.TYPE IN ('COUPON', 'PROMOTION DISCOUNT', 'REWARD', 'CREDIT', 'PROMOTION', 'FACEBOOK_FAN_REWARD', 'VOUCHER') THEN oc.amount ELSE 0 END) AS discount
            FROM OrderCharge oc GROUP BY oc.orderID 
           ) A ON o.orderID = A.orderID 
INNER JOIN (SELECT orderID, SUM(oi.quantity * oi.UnitPrice) AS saleprice 
            FROM OrderItem oi GROUP BY oi.orderID
           ) B ON o.orderID = B.orderID  
WHERE o.StoreID IN(12005,17005,1005,20005,19005) AND 
      o.OrderDate BETWEEN '2013-11-01 00:00:00' AND '2013-12-27 23:59:00'
GROUP BY o.CustomerID, o.BillFirstName, o.BillMiddleName, o.BillLastName

Upvotes: 0

Related Questions