Gaurav Maggoo Ответов: 1

Как я могу привести ниже отчет в одном запросе в SQL ? есть ли способ привести обе даты данных в один запрос?


Когда я запускаю запрос на (@Date From '2018-06-01') и( @Date To='2018-06-01')
-----------------------------
IF OBJECT_ID('TEMPDB..#Temp')IS NOT NULL 
DROP TABLE #Temp

Declare @DateFrom DateTime
Set @DateFrom='2018-06-01'
Declare @DateTo DateTime
Set @DateTo='2018-06-01'

Select @DateFrom Date,
 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],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE1.[Posting Date]<@DateFrom) as [Opening Qty],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE.[Posting Date]<=@DateTo) as [Closing Qty],ILE.[Posting Date][Posting Date],
ILE.[Item No_] Product,
   ROW_NUMBER() OVER (Partition BY [Item No_] Order by ILE.[Posting Date]) Row_Num
into #Temp
 from [Snowman Logistics Limited$Item Ledger Entry]ILE where ILE.[Posting Date]<=@DateTo and  ILE.[Primary Customer No_]in('MMBP000094 ') 
 
  Select
  Date
  ,Sum([Opening Qty])[Opening Qty],
   Sum([In Quantity])[In Quantity],
   Sum([Out Quantity])[Out Quantity],
   Sum([Closing Qty])[Closing Qty],Sum([Opening Qty]+[In Quantity])[For Billing] 
   from #Temp Group by Date

-----------------Результат я получаю
DateFrom	(Opening)	(In Quantity)	(Out Quantity)	(Closing Qty)	 (For Billing)
2018-06-01 	1556	       802.	             0.            	2358.        1556

--------
Теперь я снова запускаю запрос на с разными датами как
(@Date From '2018-06-02') and( @Date To='2018-06-02')
Точно так же когда я меняю больше дат вчера закрытие становится открытием даты которую я поставил
Date	   Opening Qty	In Quantity	Out Quantity	Closing Qty	For Billing
2018-06-02	2358	   1443	            1095	        2706	2358

Есть ли способ, с помощью которого я могу получить данные в одной таблице вместо того, чтобы каждый раз запускать запрос? Смогу ли я получить от 1 до 30 за один раз образец ниже
Я хочу такой результат
Date		     Opening	  In	    Out  Closing
01-06-18		1556	  802	     0	    2358
02-06-18		2358	  1443     1095	    2706
03-06-18		2706	   0	     0      2706
04-06-18		2706	   0	    416	    2790
05-06-18		2790	  792       291	    2791


Что я уже пробовал:

Пожалуйста, ознакомьтесь с моим вопросом аренды
Заранее спасибо!!

CHill60

У вас есть некоторые примеры данных для [Snowman Logistics Limited$Item Ledger Entry?
Какой тип данных является [Snowman Logistics Limited$Item Ledger Entry.Количество?

CHill60

Почему вы не используете функцию LAG для определения предыдущего количества закрытия? Нет никакой необходимости в этих переменных даты

Gaurav Maggoo

Я попробовал функцию LAG /LEAD на самом деле работает, но она только перемещает значения вверх вниз логически это не работает

Gaurav Maggoo

Я попробовал ниже запрос (проверить в комментариях), добавив дни. Используя его , я получаю правильные данные, но мне нужна альтернатива этого запроса, так как в течение 30 дней запрос будет слишком длинным .

1 Ответов

Рейтинг:
2

Gaurav Maggoo

<pre>IF OBJECT_ID('TEMPDB..#Temp')IS NOT NULL 
DROP TABLE #Temp
IF OBJECT_ID('TEMPDB..#Temp1')IS NOT NULL 
DROP TABLE #Temp1

Declare @DateFrom DateTime
Set @DateFrom='2018-06-01'
Declare @DateTo DateTime
Set @DateTo='2018-06-01'
Select *  Into #Temp from(
Select @DateFrom DateFrom,@DateTo DateTo, 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],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE1.[Posting Date]<@DateFrom) as [Opening Qty],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE.[Posting Date]<=@DateTo) as [Closing Qty]
--into #Temp
 from [Snowman Logistics Limited$Item Ledger Entry]ILE where ILE.[Posting Date]<=@DateTo and  ILE.[Primary Customer No_]in('MMBP000094 ') 

 Union all
 Select @DateFrom+1 DateFrom,@DateTo+1 DateTo,
 case when cast(ILE.Quantity as Numeric(19,6))>0 and ILE.[Posting Date] between @DateFrom +1 and @DateTo +1
 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 +1 and  @DateTo +1
then -cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Out Quantity],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE1.[Posting Date]< @DateFrom +1) as [Opening Qty],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE.[Posting Date]<= @DateTo +1) as [Closing Qty]
 from [Snowman Logistics Limited$Item Ledger Entry]ILE where ILE.[Posting Date]<=@DateTo +1 and  ILE.[Primary Customer No_]in('MMBP000094 ')
  Union all
 Select @DateFrom+2 DateFrom,@DateTo+2 DateTo,
 case when cast(ILE.Quantity as Numeric(19,6))>0 and ILE.[Posting Date] between @DateFrom +2 and @DateTo +2
 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 +2 and  @DateTo +2
then -cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Out Quantity],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE1.[Posting Date]< @DateFrom +2) as [Opening Qty],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE.[Posting Date]<= @DateTo +2) as [Closing Qty]
 from [Snowman Logistics Limited$Item Ledger Entry]ILE where ILE.[Posting Date]<=@DateTo +2 and  ILE.[Primary Customer No_]in('MMBP000094 ')
 Union all
 Select @DateFrom+3 DateFrom,@DateTo+3 DateTo,
 case when cast(ILE.Quantity as Numeric(19,6))>0 and ILE.[Posting Date] between @DateFrom +3 and @DateTo +3
 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 +3 and  @DateTo +3
then -cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Out Quantity],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE1.[Posting Date]< @DateFrom +3) as [Opening Qty],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE.[Posting Date]<= @DateTo +3) as [Closing Qty]
 from [Snowman Logistics Limited$Item Ledger Entry]ILE where ILE.[Posting Date]<=@DateTo +3 and  ILE.[Primary Customer No_]in('MMBP000094 ')
 ) asp 
 
  Select
  DateFrom , DateTo
  ,Sum([Opening Qty])[Opening Qty],
   Sum([In Quantity])[In Quantity],
   Sum([Out Quantity])[Out Quantity],
   Sum([Closing Qty])[Closing Qty],Sum([Opening Qty]+[In Quantity])[For Billing] 

   from #Temp Group by   DateFrom , DateTo order by  DateFrom , DateTo