Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SQL query for sales history report
09-16-2016, 07:14 AM,
#1
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
Reply
09-16-2016, 12:01 PM, (This post was last modified: 09-16-2016, 12:02 PM by phil.)
#2
RE: SQL query for sales history report
Why not use the sales analysis reporting? Of course sales analysis only records actual sales and sales orders don't come into it. Each time invoices or credit notes are created through whatever means a summary sales analysis table is maintained.
http://www.weberp.org/weberp/doc/Manual/...esAnalysis
Phil Daintree
webERP Admin
Logic Works Ltd
http://www.logicworks.co.nz
Reply
09-16-2016, 06:18 PM,
#3
RE: SQL query for sales history report
I think there are errors in your sql which may or may not be causing the problems, but will prevent it from working anyway. You have lines of the form:

debtortrans.prd='".$CurrentPeriod."'-1

I think this should read:

debtortrans.prd='" . ($CurrentPeriod - 1) . "'

Hope this helps,
Tim
Reply
09-17-2016, 02:50 AM,
#4
RE: SQL query for sales history report
Thanks Phil,
I just looked at the report you suggested and it works pretty well. I just need the column totals to be separated by period instead of summed up. I'll study it and see if I can get it to work exactly how I need it to.

Thanks Tim,
This is along the lines of the kind of advice I'm looking for. I'm just learning php and sql as I go so any steer in the right direction is helpful.
I made the change you suggested, but the problem still remains.

The main issue I'm having is that the invoice totals seem to be multiplied by the number of line items there are. I'll see what I can learn from the report Phil suggested and go from there.

Regards,
Felix
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)