如何在SQL中的一个查询中引入以下报告？有没有办法在一个查询中同时提供两个数据日期？ [英] How do I bring below report in one query in SQL ? is there a way to bring both date of data in one query?

问题描述

当我运行查询（@Date From'2018-06-01'）和（@Date To ='2018-06-01'）

-------- ---------------------

When I run query on (@Date From '2018-06-01') and( @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

---------- -------结果我得到

-----------------Result I get

DateFrom	(Opening)	(In Quantity)	(Out Quantity)	(Closing Qty)	 (For Billing)
2018-06-01 	1556	       802.	             0.            	2358.        1556

--------

现在我再次运行查询与众不同日期为

--------
Now i again run query on with different dates as

(@Date From '2018-06-02') and( @Date To='2018-06-02')

同样当我更改更多日期昨天关闭成为我放的日期开始

Similarly when I change more dates Yesterdays closing becomes Opening of date which I put

Date	   Opening Qty	In Quantity	Out Quantity	Closing Qty	For Billing
2018-06-02	2358	   1443	            1095	        2706	2358

我可以通过哪种方式在一个表中获取数据，而不是每次都运行查询。我可以一次获得1到30个样本下面

我想要这样的结果

Is there as way by which I can get data in one table instead of running query every time. Will I be able to get 1 to 30 in one time Sample below
I want result like this

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

我的尝试：



请审核我的问题租约

在此先感谢!!

What I have tried:

Kindly review my question lease
Thanks in Advance!!

推荐答案

项目分类帐输入] as ILE1 其中 ILE1。[Entry No _] = ILE。[Entry No_] 和 ILE1。[Item No _] = ILE。[Item No_]
和 ILE1。[Document No_] = ILE。[文件编号] 和 ILE1。[发布日期]< @DateFrom） as [作品ening数量]，
（选择 SUM（ILE1。[数量]）来自 [Snowman Logistics]限制

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

项目分类帐输入] as ILE1 其中 ILE1。[条目号_] = ILE。[输入No_] 和 ILE1。[Item No _] = ILE。[Item No_]
和 ILE1。[文件编号_] = ILE。[文件编号] 和 ILE。[发布日期]< = @ DateTo） [结算数量]，ILE。[发布日期 ] [发布日期]，
ILE。[Item No_] Product，
ROW_NUMBER（） OVER （分区 BY [Item No_] 订单 by ILE。[发布日期]）Row_Num
进入 #Temp
来自 [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

项目分类帐条目] ILE 其中 ILE。[发布日期]< = @ DateTo 和 ILE。[主要客户编号_ ] in （' MMBP000094'）

选择
日期
，Sum（[Opening]数量]）[开放数量]，
总和（[在数量]）[在数量]，
总和（[出数量]）[出数量]，
总和（[结算数量]）[结算数量]，总和（[开仓数量] + [在数量]）[用于结算]
来自 #Temp 组 按 日期

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

-----------------结果我得到

-----------------Result I get

DateFrom	(Opening)	(In Quantity)	(Out Quantity)	(Closing Qty)	 (For Billing)
2018-06-01 	1556	       802.	             0.            	2358.        1556

--------

现在我再次使用不同的日期运行查询

--------
Now i again run query on with different dates as

(@Date From '2018-06-02') and( @Date To='2018-06-02')

同样当我更改更多日期时昨天收盘成为开盘日期我放

Similarly when I change more dates Yesterdays closing becomes Opening of date which I put

Date	   Opening Qty	In Quantity	Out Quantity	Closing Qty	For Billing
2018-06-02	2358	   1443	            1095	        2706	2358

有没有我的方式可以在一个表中获取数据，而不是每次都运行查询。我可以一次获得1到30个样本下面

我想要这样的结果

Is there as way by which I can get data in one table instead of running query every time. Will I be able to get 1 to 30 in one time Sample below
I want result like this

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

我的尝试：



请审核我的问题租约

先谢谢!!

What I have tried:

Kindly review my question lease
Thanks in Advance!!

这篇关于如何在SQL中的一个查询中引入以下报告？有没有办法在一个查询中同时提供两个数据日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！