Member 9313444 Ответов: 3

как получить баланс Открытия и закрытия в этом примере с помощью SQL-запросов в SQL2008

привет frndz,

Я попробовал некоторые cte в этом примере, и результат совершенно неправильный. плз смотрите мой пример

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
--------------------------------------------------------------------------
но я хочу, чтобы генерировать открывая бал, основанные на предыдущей строке closingbal такой 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

пожалуйста, помогите мне.

3 Ответов

Рейтинг:
22

Tomas Takac

Вы на правильном пути, но cte1 ошибается. Вам нужно выбрать первую строку cte в качестве якоря. Затем присоединитесь к следующей строке, рассчитывая правильные начальные и конечные балансы.

...
, 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

Member 9313444

Большое спасибо!

Рейтинг:
0

Ankit_serasiya 

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

Member 14172816

Последняя строка сумма начального баланса не учитывается в этом запросе

Рейтинг:
0

Member 14172816

Решение 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