问题描述
当我运行查询（@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!!
推荐答案
-----------------结果我得到
-----------------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中的一个查询中引入以下报告？有没有办法在一个查询中同时提供两个数据日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！