如何在SQL2008中使用SQL查询在此示例中获得开放和结束平衡 [英] how to get opening and closing balance in this example using SQL queries in SQL2008

问题描述

嗨frndz，



我在这个例子中尝试了一些cte，结果完全错了。请参阅我的示例

 声明  @ table   table  
（
 entrydate  datetime ， 
 openingbal 十进制（ 15 ， 3 ），
 credit  decimal （ 15 ， 3 ），
借记十进制（ 15 ， 3 ），
 closingbal 十进制（ 15 ，< span class =code-digit> 3 ）
）
  insert   into   @ table  
 选择 '   20 14-10-02 23：05：43.203'， 94000 。 000 ， 0 。 000 ， 900 。 000 ， 93100 。 000  
  union  
 选择 '  2014-10-03 23：05：43.203'， 0 。 000 ， 1000 。 000 ， 0 。 000 ， 0 。 000  
  union  
 选择 '  2014-10-03 23：06：43.203'， 0 。 000 ， 0 。 000 ， 500 。 000 ， 0 。 000  
  union  
 选择 '  2014-10-14 23：52：27.327'，< span class =code-digit> 4000 。 000 ， 0 。 000 ， 1500 。 000 ， 2500 。 000  
   -    select * from @table  
; 
   cte  as （
 选择 *，ROW_NUMBER（） over （ order   by  entrydate）摆脱   @ table  
）
， cte1  as （
  select  entrydate，cast（openingbal  as   decimal （ 15 ， 3 ））openbal，credit，debit，cast（closingbal  as   decimal （ 15 ， 3 ））closingbal，
 ROW_NUMBER（） over （ order   by  entrydate）rid 
 来自 cte 
  union  所有 
 选择 b.entrydate，cast（a.closingbal  as  十进制（ 15 ， 3 ））openingbal，b.credit，b。 debit，cast（（a.closingbal + b.credit）-b.debit  as   decimal （ 15 ， 3 ））closingbal 
，ROW_NUMBER（） over （ order   by  a.entrydate）rid 
 来自 cte a 
  join  cte1 b  on  b。 rid = a.rid + 1 
）
  select  entrydate，isnull（openingbal， 0 ）opensbal，isnull（credit， 0 ）CreditAmount 
，isnull（debit， 0 ）DebitAmount，isnull（closingbal，openingbal）closingbal，摆脱来自 cte1  order   by  entrydate，rid 

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

但是我想基于prev row closingbal生成开场白平衡这样的结果

 entryate opnbal cr dr closingbal 
 02-10-14 23:05 94000 0 900 93100 
 03-10-14 23:05 93100 1000 0 94100 
 03-10-14 23:06 94100 0 500 93600 
 14-10-14 23： 52 97600 0 1500 96100 

请帮助我。

解决方案

你走在正确的轨道上，但是cte1是错的。您需要选择第一行cte作为锚点。然后在计算正确的期初和期末余额时加入下一行。

 ... 
，cte1  as （
  select  entrydate，openbal，credit，debit，cast（openingbal + credit  -  debit  as   decimal （ 15 ， 3 ）） as  closingbal，摆脱
 来自 cte 其中 rid =  1  
  union  < span class =code-keyword> all  
  select  curr.entrydate，cast（curr.openingbal + prev.closingbal  as   decimal （ 15 ， 3 ）），curr.credit，curr.debit，
 cast （curr.openingbal + prev.closingbal + curr.credit  -  curr.debit  as   decimal （ 15 ， 3 ））  closingbal，curr.rid 
 来自 cte curr  inner   join  cte1上一页  curr.rid = prev.rid +  1  
）
 选择 * 
 来自 cte1 
 订单 按 entrydate 

与cte as（
选择*，ROW_NUMBER（）结束（按entrydate排序）摆脱#table 
），cte1 as（
 select entrydate，openbal，credit，debit，cast（openingbal + credit  - 借记作为十进制） （15,3））作为闭幕式，从cte摆脱
，其中rid = 1 
 union all 
 select curr.entrydate，cast（prev.closingbal as decimal（15,3）），curr.credit，curr.debit，
 cast（prev.closingbal + curr.credit  -  curr.debit如十进制（15,3））作为closingbal，curr.rid 
来自cte curr inner join cte1 prev on curr.rid = prev.rid + 1 
）
 select * 
来自cte1 
按entrydate排序

解决方案4

 SELECT * FROM @table AS mainDtl OUTER APPLY（SELECT SUM（subDtl.openingbal）+ SUM（subDtl.credit）-SUM（subDtl.debit）关闭FROM @table AS subDtl WHERE subDtl.entrydate< = mainDtl.entrydate）Data 

hi frndz,

I tried some cte in this example and the result is totally wrong. plz see my example

declare @table table
(
entrydate datetime,
openingbal decimal(15,3),
credit decimal(15,3),
debit decimal(15,3),
closingbal decimal(15,3)
)
insert into @table
select '2014-10-02 23:05:43.203',	94000.000,	0.000,	900.000	,93100.000
union
select '2014-10-03 23:05:43.203',	0.000	,1000.000	,0.000,	0.000
union
select '2014-10-03 23:06:43.203',	0.000,	0.000,	500.000	,0.000
union
select '2014-10-14 23:52:27.327',	4000.000	,0.000	,1500.000	,2500.000
--select * from @table
 ;
with cte as(
select *,  ROW_NUMBER()over (order by entrydate)rid from @table
)
, cte1 as(
select entrydate ,cast (openingbal as decimal(15,3)) openingbal,credit,debit, cast (closingbal as decimal(15,3))closingbal ,
 ROW_NUMBER()over (order by entrydate)rid
from  cte
union all
select  b.entrydate ,cast (a.closingbal as decimal(15,3))  openingbal ,b.credit,b.debit,cast ((a.closingbal+b.credit) -b.debit as decimal(15,3)) closingbal
 ,ROW_NUMBER()over (order by a.entrydate) rid
from cte a
join cte1 b on  b.rid=a.rid+1
)
select  entrydate ,isnull(openingbal,0)openingbal,isnull(credit,0) CreditAmount
,isnull(debit,0) DebitAmount,isnull(closingbal,openingbal)closingbal ,rid from cte1 order by entrydate,rid 

--------------------------------------------------------------------------
but i want to generate the opening bal based on prev row closingbal like this restult

entryate	opnbal	  cr	   dr	closingbal
02-10-14 23:05  94000      0      900   93100
03-10-14 23:05  93100   1000        0   94100
03-10-14 23:06  94100      0      500   93600
14-10-14 23:52  97600      0     1500   96100 

plz help me.

解决方案

You are on the right track, but cte1 is wrong. You need to select the first row of cte as your anchor. Then join next row while calculating the right opening and closing balances.

...
, cte1 as(
  select entrydate, openingbal, credit, debit, cast(openingbal + credit - debit as decimal(15,3)) as closingbal, rid
  from cte where rid = 1
  union all
  select curr.entrydate, cast(curr.openingbal + prev.closingbal as decimal(15,3)), curr.credit, curr.debit, 
    cast(curr.openingbal + prev.closingbal + curr.credit - curr.debit as decimal(15,3)) as closingbal, curr.rid
  from cte curr inner join cte1 prev on curr.rid = prev.rid + 1
)
select *
from cte1
order by entrydate

with cte as(
select *,  ROW_NUMBER()over (order by entrydate)rid from #table
), cte1 as(
  select entrydate, openingbal, credit, debit, cast(openingbal + credit - debit as decimal(15,3)) as closingbal, rid
  from cte where rid = 1
  union all
  select curr.entrydate, cast(prev.closingbal as decimal(15,3)), curr.credit, curr.debit, 
    cast(prev.closingbal + curr.credit - curr.debit as decimal(15,3)) as closingbal, curr.rid
  from cte curr inner join cte1 prev on curr.rid = prev.rid + 1
)
select *
from cte1
order by entrydate

Solution 4

SELECT * FROM @table AS mainDtl OUTER APPLY (SELECT SUM(subDtl.openingbal)+SUM(subDtl.credit)-SUM(subDtl.debit) closing FROM @table AS subDtl WHERE subDtl.entrydate<=mainDtl.entrydate)Data

这篇关于如何在SQL2008中使用SQL查询在此示例中获得开放和结束平衡的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！