كود SQL يمكن تنفيذه على قاعدة بيانات الشركة يظهر قائمة بمبيعات الأصناف خلال السنوات
كود PHP:
create view view_Sales_Qty_by_Item_by_Year
as
--***********************************************************************************
--view_Sales_Qty_by_Item_by_Year
--Created Jan 23, 2012 by Victoria Yudin - Flexible Solutions, Inc.
--For updates see http://victoriayudin.com/gp-reports/
--Returns total sales quantities fulfilled (invoices - returns) for each item by year
--Only posted invoices and returns are included
--Quantity is calculated by multiplying by QTYBSUOM column in case other UofM's are
-- used on transations
--Voided transations are excluded
--Item Description is taken from Inventory Item Maintenance for all inventory items
-- and from SOP line items for non-inventory items
--***********************************************************************************
SELECT
D.ITEMNMBR Item_Number, D.Item_Description,
sum(case when year(D.DOCDATE) = 2000 then D.Qty else 0 end) as [2000_Qty],
sum(case when year(D.DOCDATE) = 2001 then D.Qty else 0 end) as [2001_Qty],
sum(case when year(D.DOCDATE) = 2002 then D.Qty else 0 end) as [2002_Qty],
sum(case when year(D.DOCDATE) = 2003 then D.Qty else 0 end) as [2003_Qty],
sum(case when year(D.DOCDATE) = 2004 then D.Qty else 0 end) as [2004_Qty],
sum(case when year(D.DOCDATE) = 2005 then D.Qty else 0 end) as [2005_Qty],
sum(case when year(D.DOCDATE) = 2006 then D.Qty else 0 end) as [2006_Qty],
sum(case when year(D.DOCDATE) = 2007 then D.Qty else 0 end) as [2007_Qty],
sum(case when year(D.DOCDATE) = 2008 then D.Qty else 0 end) as [2008_Qty],
sum(case when year(D.DOCDATE) = 2009 then D.Qty else 0 end) as [2009_Qty],
sum(case when year(D.DOCDATE) = 2010 then D.Qty else 0 end) as [2010_Qty],
sum(case when year(D.DOCDATE) = 2011 then D.Qty else 0 end) as [2011_Qty],
sum(case when year(D.DOCDATE) = 2012 then D.Qty else 0 end) as [2012_Qty],
sum(D.Qty) Total_Qty
FROM
(SELECT SH.DOCDATE, SD.ITEMNMBR,
coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
CASE SD.SOPTYPE
WHEN 3 THEN SD.QTYFULFI*QTYBSUOM
WHEN 4 THEN SD.QUANTITY*QTYBSUOM*-1
END Qty
FROM SOP30200 SH
INNER JOIN
SOP30300 SD
ON SD.SOPNUMBE = SH.SOPNUMBE
AND SD.SOPTYPE = SH.SOPTYPE
LEFT OUTER JOIN
IV00101 I
ON I.ITEMNMBR = SD.ITEMNMBR
WHERE SH.VOIDSTTS = 0
AND SH.SOPTYPE IN (3,4)
AND SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%) D
GROUP BY D.ITEMNMBR, D.Item_Description
GO
GRANT SELECT ON view_Sales_Qty_by_Item_by_Year TO DYNGRP
طبعا استبدل القيمة XXXXXXXXXXXXXXX برقم الصنف الذي تود معرفته
هذا الكود من مدونة فيكتوريا يودين