Monzer Osama | 24-Jan-2016 09:24 AM | كود لإظهار كمية مبيعات الأصناف خلال السنوات كود 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 برقم الصنف الذي تود معرفته هذا الكود من مدونة فيكتوريا يودين |