SQL query for sales history report
I am trying to create a sql query to view sales history grouped by item and customer per period.
I did not get the result I was looking for so I grouped it by sales orders first, and noticed that even by grouping in this method, I was not getting the correct result. Attached is the query I have, sorry if it's not formated well.
SELECT salesorderdetails.orderno,
salesorderdetails.stkcode,
sum(qtyinvoiced) as qtyinvoiced,
sum(salesorderdetails.qtyinvoiced) as qty,
sum(salesorderdetails.qtyinvoiced*salesorderdetails.unitprice) as dollaramount,
salesorderdetails.unitprice,
salesorders.debtorno,
salesorders.branchcode,
salesorders.fromstkloc,
stockmaster.longdescription,
CASE WHEN debtortrans.prd='".$CurrentPeriod."' THEN SUM(salesorderdetails.qtyinvoiced*salesorderdetails.unitprice) ELSE '0' END AS CurrDollarAmount,
CASE WHEN debtortrans.prd='".$CurrentPeriod."' THEN sum(salesorderdetails.qtyinvoiced) ELSE '0' END AS CurrQty,
SUM(CASE WHEN debtortrans.prd='".$CurrentPeriod."'-1 THEN (salesorderdetails.qtyinvoiced*salesorderdetails.unitprice) ELSE '0' END) AS MinusOneMonthDollarAmount,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-1 THEN sum(salesorderdetails.qtyinvoiced) ELSE '0' END AS MinusOneMonthQty,
SUM( CASE WHEN debtortrans.prd='".$CurrentPeriod."'-2 THEN (salesorderdetails.qtyinvoiced*salesorderdetails.unitprice) ELSE '0' END) AS MinusTwoMonthDollarAmount,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-2 THEN sum(salesorderdetails.qtyinvoiced) ELSE '0' END AS MinusTwoMonthQty,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-3 THEN SUM(salesorderdetails.qtyinvoiced*salesorderdetails.unitprice) ELSE '0' END AS MinusThreeMonthDollarAmount,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-3 THEN sum(salesorderdetails.qtyinvoiced) ELSE '0' END AS MinusThreeMonthQty,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-4 THEN SUM(salesorderdetails.qtyinvoiced*salesorderdetails.unitprice) ELSE '0' END AS MinusFourMonthDollarAmount,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-4 THEN sum(salesorderdetails.qtyinvoiced) ELSE '0' END AS MinusFourMonthQty,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-5 THEN SUM(salesorderdetails.qtyinvoiced*salesorderdetails.unitprice) ELSE '0' END AS MinusFiveMonthDollarAmount,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-5 THEN sum(salesorderdetails.qtyinvoiced) ELSE '0' END AS MinusFiveMonthQty,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-6 THEN SUM(salesorderdetails.qtyinvoiced*salesorderdetails.unitprice) ELSE '0' END AS MinusSixMonthDollarAmount,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-6 THEN sum(salesorderdetails.qtyinvoiced) ELSE '0' END AS MinusSixMonthQty,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-7 THEN SUM(salesorderdetails.qtyinvoiced*salesorderdetails.unitprice) ELSE '0' END AS MinusSevenMonthDollarAmount,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-7 THEN sum(salesorderdetails.qtyinvoiced) ELSE '0' END AS MinusSevenMonthQty,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-8 THEN SUM(salesorderdetails.qtyinvoiced*salesorderdetails.unitprice) ELSE '0' END AS MinusEightMonthDollarAmount,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-8 THEN sum(salesorderdetails.qtyinvoiced) ELSE '0' END AS MinusEightMonthQty,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-9 THEN SUM(salesorderdetails.qtyinvoiced*salesorderdetails.unitprice) ELSE '0' END AS MinusNineMonthDollarAmount,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-9 THEN sum(salesorderdetails.qtyinvoiced) ELSE '0' END AS MinusNineMonthQty,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-10 THEN SUM(salesorderdetails.qtyinvoiced*salesorderdetails.unitprice) ELSE '0' END AS MinusTenMonthDollarAmount,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-10 THEN sum(salesorderdetails.qtyinvoiced) ELSE '0' END AS MinusTenMonthQty,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-11 THEN SUM(salesorderdetails.qtyinvoiced*salesorderdetails.unitprice) ELSE '0' END AS MinusElevenMonthDollarAmount,
CASE WHEN debtortrans.prd='".$CurrentPeriod."'-11 THEN sum(salesorderdetails.qtyinvoiced) ELSE '0' END AS MinusElevenMonthQty,
stockmaster.subcatid,
debtorsmaster.name,
custbranch.broker,
custbranch.salessupport,
custbranch.salesman,
debtortrans.prd
FROM salesorderdetails
INNER JOIN salesorders
ON salesorders.orderno=salesorderdetails.orderno
INNER JOIN custbranch
ON salesorders.branchcode=custbranch.branchcode
INNER JOIN stockmaster
ON salesorderdetails.stkcode=stockmaster.stockid
INNER JOIN debtorsmaster
ON debtorsmaster.debtorno=salesorders.debtorno
INNER JOIN debtortrans
ON salesorderdetails.orderno=debtortrans.order_ AND type='10'
WHERE salesorderdetails.completed='1'
GROUP BY salesorderdetails.orderno
ORDER BY stkcode
The end result I'm trying to achieve is to view how much of one item is sold to a customer in a given period summing up all the sales orders that got invoiced.
Any help is appreciated
Regards,
Felix
|