Joining issue with sql query

Question

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

Maybe its better to use " like" for the compariosn of the oc.TYPE columns with the String literals. — Diversity
– Diversity, Commented Dec 30, 2013 at 7:27
Hi! Thanks for your reply. we have also tried your suggestion but it still giving us same result. — user2393886
– user2393886, Commented Dec 30, 2013 at 7:31
No, it just giving us only value of amount column instead of summing them. — user2393886
– user2393886, Commented Dec 30, 2013 at 7:33
Say exactly, if you sum that what value will you get? and now what value you getting? — Vignesh Kumar A
– Vignesh Kumar A, Commented Dec 30, 2013 at 7:35

Saharsh Shah · Accepted Answer · 2013-12-30 11:40:42Z

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

Collectives™ on Stack Overflow

Joining issue with sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related