السلام عليكم ورحمة الله وبركاته
درسنا اليوم كيفية اظهار جرد المخازن في اكسل ويمكن ايضا اظهار مجموع الداخل والخارج والرصيد حسب كل مستودع على حدا والسكريبت المستخدم
SELECT CardCode AS كود, ProductName AS الاسم, GroupName AS المجموعة, StoreName AS المستودع, InPut AS الداخل, OutPut AS الخارج, Quantity AS الرصيد
FROM (SELECT SUM(dbo.Qry101.InPut) AS InPut, SUM(dbo.Qry101.OutPut) AS OutPut, SUM(dbo.Qry101.ItemQuantity) AS Quantity, dbo.TBL007.CardCode, dbo.TBL007.ProductName,
dbo.TBL007.LatinName, dbo.TBL007.Source, dbo.TBL007.GroupGuid, dbo.TBL007.DefaultUnit, dbo.TBL007.LatinName AS ItemLatinName, dbo.TBL007.Factor2, dbo.TBL007.Barcode3,
dbo.TBL007.Security AS ItemSecurity, dbo.TBL007.Barcode2, dbo.TBL007.Barcode, dbo.TBL007.Unit3, dbo.TBL007.Unit2, dbo.TBL007.Unit, dbo.TBL007.Factor3,
dbo.TBL007.AgentPrice2, dbo.TBL007.AgentPrice, 0 AS None, 0 AS None2, 0 AS None3, dbo.TBL007.AgentPrice3, dbo.TBL007.WholePrice, dbo.TBL007.WholePrice2,
dbo.TBL007.WholePrice3, dbo.TBL007.EndUserPrice, dbo.TBL007.EndUserPrice2, dbo.TBL007.EndUserPrice3, dbo.TBL007.StanderCost, dbo.TBL007.StanderCost2,
dbo.TBL007.StanderCost3, dbo.TBL007.Price5Item, dbo.TBL007.Price5Item2, dbo.TBL007.Price5Item3, dbo.TBL007.Price6Item, dbo.TBL007.Price6Item2, dbo.TBL007.Price6Item3,
dbo.TBL007.Price7Item, dbo.TBL007.Price7Item2, dbo.TBL007.Price7Item3, dbo.TBL007.Custom, dbo.TBL007.Custom2, dbo.TBL007.Custom3, dbo.TBL007.Custom4,
dbo.TBL007.Custom5, dbo.Qry101.GroupName, dbo.Qry101.GroupCode, dbo.Qry101.GroupLatinName, dbo.Qry101.ItemGroup, dbo.Qry101.StoreGuide, dbo.Qry101.StoreCode,
dbo.Qry101.StoreName, dbo.Qry101.Store, dbo.TBL007.MaxLimit, dbo.TBL007.MinLimit, im1.CardName AS ItemCategory01, im2.CardName AS ItemCategory02
FROM dbo.Qry101 LEFT OUTER JOIN
dbo.TBL007 ON dbo.Qry101.ProductGuide = dbo.TBL007.CardGuide LEFT OUTER JOIN
dbo.TBL081 AS im1 ON dbo.TBL007.Category01 = im1.CardGuide LEFT OUTER JOIN
dbo.TBL081 AS im2 ON dbo.TBL007.Category02 = im2.CardGuide
WHERE (dbo.Qry101.Posted = 1) AND (dbo.Qry101.StockProduct = 1) OR
(dbo.Qry101.Posted = 1) AND (dbo.TBL007.IsAssets = 1)
GROUP BY dbo.TBL007.ID, dbo.TBL007.DefaultCurrency, dbo.TBL007.CardGuide, dbo.TBL007.CardCode, dbo.TBL007.ProductName, dbo.TBL007.LatinName, dbo.TBL007.Source,
dbo.TBL007.GroupGuid, dbo.TBL007.DefaultUnit, dbo.TBL007.Security, dbo.TBL007.Factor2, dbo.TBL007.Barcode3, dbo.TBL007.Barcode2, dbo.TBL007.Barcode, dbo.TBL007.Unit3,
dbo.TBL007.Unit2, dbo.TBL007.Custom, dbo.TBL007.Custom2, dbo.TBL007.Custom3, dbo.TBL007.Custom4, dbo.TBL007.Custom5, dbo.TBL007.Unit, dbo.TBL007.Factor3,
dbo.TBL007.AgentPrice2, dbo.TBL007.AgentPrice, dbo.TBL007.AgentPrice3, dbo.TBL007.WholePrice, dbo.TBL007.WholePrice2, dbo.TBL007.WholePrice3, dbo.TBL007.EndUserPrice,
dbo.TBL007.EndUserPrice2, dbo.TBL007.EndUserPrice3, dbo.TBL007.StanderCost, dbo.TBL007.StanderCost2, dbo.TBL007.MaxLimit, dbo.TBL007.MinLimit, dbo.TBL007.Category01,
dbo.TBL007.Category02, im1.CardName, im2.CardName, dbo.TBL007.StanderCost3, dbo.Qry101.GroupName, dbo.Qry101.GroupCode, dbo.Qry101.GroupLatinName,
dbo.TBL007.Price5Item, dbo.TBL007.Price5Item2, dbo.TBL007.Price5Item3, dbo.TBL007.Price6Item, dbo.TBL007.Price6Item2, dbo.TBL007.Price6Item3, dbo.TBL007.Price7Item,
dbo.TBL007.Price7Item2, dbo.TBL007.Price7Item3, dbo.Qry101.ItemGroup, dbo.Qry101.StoreGuide, dbo.Qry101.StoreCode, dbo.Qry101.StoreName, dbo.Qry101.Store
HAVING (SUM(dbo.Qry101.ItemQuantity) <> 0)) AS a
ليست هناك تعليقات: