如何使用以下查询将昨天收盘作为今天开盘？ [英] How can I bring yesterday closing as today opening using below query?

问题描述

如何使用以下查询将此数据添加到此表单中。请帮助一下

How can i bring my data in this form using below query. Kindly some one help please

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

我的尝试：

What I have tried:

<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]

推荐答案

项目] as 项目
内部 加入 [Snowman Logistics Limited

Item] as Item inner join [Snowman Logistics Limited

项目分类帐输入] ILE
Item.No_ = ILE。[Item No_]
其中 ILE。[发布日期]< = @ DateTo 和 ILE。[< span class =code-keyword> Primary Customer No_]
in （' VRNP000016'，' MUMP000056' ，' VRNP000040'，' MMBP000094'，' CHNP000279'）和 [项目编号_] （' B1001020'，' B1001163'）
）asp
group by
[Item No]，[Location Code]，
[商品名称]
，[发布日期]
订单 by [Item No]，[Posting Date ]

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]

一个简单您可以考虑的解决方案是（1）添加标识列，或（2）将时间作为日期时间戳'getdate（）'从系统中存储。



您可以使用WHERE recid = max（recid）或timestamp = max（timestamp）添加WHERE recid = max（recid）或timestamp = max（timestamp）来过滤下次早晨启动时的请求和一组条件，如果知道它是昨天，则添加日期过滤器。在某些情况下，如果您希望的值是当天的最后一条记录（总是！），那么您只需要求MAX（）一直保持单调的东西。



以上假装你创建了一个名为'recid'的身份字段，或者你的日期时间戳字段被命名为timestamp。

One simple solution that you may consider is either (1) add an identity column, or (2) store the time as a datetime stamp 'getdate()' from the system.

You can the request and set of criteria to filter you next-mornings startup with the addition of WHERE recid=max(recid) or timestamp=max(timestamp) and just add the date filter so if knows it's yesterday. Under certain circumstances, if the values you wish are the last record for the day (always!) then you only need to ask for the MAX() of something that is always monotonic.

The above is pretending you create an identity field named 'recid' or your datetime stamp field is named timestamp.

这篇关于如何使用以下查询将昨天收盘作为今天开盘？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！