将期末余额作为下个月的期初余额 [英] Taking the closing balance as the opening balance of the follwing month

问题描述

我想让当前月份的收盘余额让我们说3月份的sx或者水晶报告4月份的期初余额是如何进行的。请帮助解决问题。下面的脚本是我用来获得余额的脚本：

 选择< /跨度> StockNumber，InventoryName，YEAR1，MONTH1，RECEIVE_FROM_PO，RECEIVE_XFER，RECEIVE_ADJ，
 _RETURN，调整，ISSUE，PT_ISSUE，TRANSFER，SUM（ABS（RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + _RETURN））
  -  SUM （ABS（ADJUSTMENT + ISSUE + PT_ISSUE + TRANSFER））
  as  BALANCE，BALANCE  as  OPENING_BAL 
 进入 final2 
 来自 dbo.drug_summary $ 
 组 <跨度类= 代码关键字> BY  StockNumber，InventoryName，YEAR1，MONTH1，RECEIVE_FROM_PO，RECEIVE_XFER，RECEIVE_ADJ，
 _RETURN，调整，ISSUE ，PT_ISSUE，TRANSFER，BALANCE 

解决方案

GROUP BY StockNumber，InventoryName，Year1 ，MONTH1，RECEIVE_FROM_PO，RECEIVE_XFER，RECEIVE_ADJ，
_RETURN，调整，ISSUE，PT_ISSUE，TRANSFER，BALANCE

例如，

表格inv [库存]

----

 VouNo int 
 VouDt datetime 
 InvId int 
数量数字（12,2）[+如果向内...  - 如果向外] 

所以，

查询就像下面

 选择 VouDt，InvId，
（选择 sum（数量）来自 inv 其中 VouDt< i.VouDt） as   OpnQty ，
 sum（Qty） as  数量，
（选择 sum（数量）来自 inv 其中 VouDt< i.VouDt） -  sum（Qty） as   ClsQty  
 来自 Inv  as  i 
 其中 voudt> = '  2012-04-01' 和 vouDt< = '  2012-04-30'   此处的过滤日期为2012年4月 
  group   by  InvId，VouDt 

Happy Coding！

：）

I want to make the closing balance of the current month lets say of March to be the opening balance of April in sql or crystal reports how to i go about it. Help need the solution agently please. the script below is the one i have used to get the balance:

select StockNumber,InventoryName,Year1,Month1,RECEIVE_FROM_PO,RECEIVE_XFER,RECEIVE_ADJ,
_RETURN,ADJUSTMENT,ISSUE,PT_ISSUE,TRANSFER, SUM(ABS(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + _RETURN))
- SUM(ABS(ADJUSTMENT + ISSUE + PT_ISSUE + TRANSFER))
 as BALANCE, BALANCE as OPENING_BAL
into final2
from dbo.drug_summary$
GROUP BY StockNumber,InventoryName,Year1,Month1,RECEIVE_FROM_PO,RECEIVE_XFER,RECEIVE_ADJ,
_RETURN,ADJUSTMENT,ISSUE,PT_ISSUE,TRANSFER,BALANCE

解决方案

GROUP BY StockNumber,InventoryName,Year1,Month1,RECEIVE_FROM_PO,RECEIVE_XFER,RECEIVE_ADJ, _RETURN,ADJUSTMENT,ISSUE,PT_ISSUE,TRANSFER,BALANCE

example,
A table inv [Inventory]
----

VouNo int
VouDt datetime
InvId int
Qty Numeric(12,2)   [+ if inward... - if Outward]

so,
Query is like below

select VouDt, InvId, 
(select sum(Qty) from inv where VouDt< i.VouDt) as OpnQty,
sum(Qty) as Qty,
(select sum(Qty) from inv where VouDt< i.VouDt)- sum(Qty) as ClsQty
from Inv as i
where voudt >= '2012-04-01' and vouDt<='2012-04-30' -- filter date here it's for april-2012
group by InvId,VouDt

Happy Coding!
:)

这篇关于将期末余额作为下个月的期初余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！