Как я могу принести вчерашнее закрытие как сегодняшнее открытие, используя приведенный ниже запрос?
Как я могу привести свои данные в эту форму, используя приведенный ниже запрос. Пожалуйста кто нибудь помогите пожалуйста
Date Product OpeningQty ArrivedQty IssuedQty ClosingQty 01-Oct-2015 A 100 10 12 098 01-Oct-2015 B 120 12 04 128 01-Oct-2015 C 140 14 06 148 01-Oct-2015 D 160 16 08 168 02-Oct-2015 A 098 18 00 116 02-Oct-2015 B 128 20 12 136 02-Oct-2015 C 148 00 00 148 02-Oct-2015 D 168 00 00 168 03-Oct-2015 A 116 00 00 116 03-Oct-2015 B 136 00 00 136 03-Oct-2015 C 148 22 14 156 03-Oct-2015 D 168 24 16 176
Что я уже пробовал:
<pre>--Inventory Ledger Developed by Gaurav for Baskin Robin SET NOCOUNT ON; SET ANSI_WARNINGS OFF; SET QUOTED_IDENTIFIER OFF; Declare @ReportType varchar(20) Set @ReportType='WithDimensions' Declare @DateFrom DateTime Set @DateFrom='2017-01-02' Declare @DateTo DateTime Set @DateTo='2017-01-03' Select [Posting Date], [Item No], [Item Name], SUM(Opening) as Opening , SUM([In Quantity]) as [In Quantity], -SUM([Out Quantity]) as [Out Quantity], SUM([Closing]) as [Closing] from( select ILE.[Posting Date], DATEDIFF(D,@DateFrom,@DateTo)Days,ILE.[Location Code]as [Location Code], ILE.[Primary Customer No_]as [Customer No], Item.No_ as [Item No], Item.[Description]+' '+Item.[Description 2] as [Item Name], ILE.[Location Code] as [Dimension1], Case when Item.Blocked=1 then 'Non Active' else 'Active' end as Status, case when cast(ILE.Quantity as Numeric(19,6))>0 and ILE.[Posting Date] between @DateFrom and @DateTo then cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [In Quantity], case when cast(ILE.Quantity as Numeric(19,6))<0 and ILE.[Posting Date] between @DateFrom and @DateTo then -cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Out Quantity], case when ILE.[Posting Date] < @DateFrom then cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Opening], case when ILE.[Posting Date] <= @DateTo then cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Closing], Item.[Base Unit of Measure] as [Base UOM] from [Snowman Logistics Limited$Item] as Item inner join [Snowman Logistics Limited$Item Ledger Entry] ILE on Item.No_=ILE.[Item No_] where ILE.[Posting Date] <=@DateTo and ILE.[Primary Customer No_] in('VRNP000016','MUMP000056','VRNP000040','MMBP000094','CHNP000279') and [Item No_]in('B1001020','B1001163') ) asp group by [Item No],[Location Code], [Item Name] ,[Posting Date] order by [Item No],[Posting Date]
CHill60
Проверьте функции опережения и запаздывания. У меня сейчас нет времени на пример, поэтому я просто оставлю этот комментарий
Gaurav Maggoo
Идеальный. Спасибо