vikas_nm
asked on
Updating multiple records from the previous row
Hello Experts,
I have attached an excel for the sample data and my requirements.
With Kind Regards
Book2.xlsx
I have attached an excel for the sample data and my requirements.
With Kind Regards
Book2.xlsx
maybe this:
(case when closing is not null then closing else opening end) as closingBalance
just add this into your select...
(case when closing is not null then closing else opening end) as closingBalance
just add this into your select...
ASKER
What I want in the output is that if the there is no closing balance for the date then the op balance for the same date will become closing balance
ASKER
i want a view for the same.
Hi,
It might be a better idea to have the backup of your original table before trying to run the code.
Assumptions i have taken:
1) The DivisionID, Opening & Closing are Integer
2) The LedgerId is same in all the records
3) The data is clustered index on LedgerId, DivisionId, ClosingDate (This will make sure the ordering is proper during the calculation)
Now try the below code:
It might be a better idea to have the backup of your original table before trying to run the code.
Assumptions i have taken:
1) The DivisionID, Opening & Closing are Integer
2) The LedgerId is same in all the records
3) The data is clustered index on LedgerId, DivisionId, ClosingDate (This will make sure the ordering is proper during the calculation)
Now try the below code:
declare @DivisionId int, @Opening int, @Closing int
select @DivisionId = 0, @Opening = 0, @Closing = 0
update myTable
set Opening = case when isnull(opening,0) = 0 then @opening else opening end,
Closing = case when isnull(closing,0) = 0 then @closing else closing end,
@opening = case when DivisionId = @DivisionId then @closing else 0 end,
@opening = case when isnull(opening,0) = 0 then @opening else opening end,
@closing = case when isnull(closing,0) = 0 then @opening else closing end,
@Divisionid = divisionid
select * from myTable
ASKER
I think you got it wrong. The table data will remain the same. I would like a view for the data from the table as mentioned in the xl output section.
I am afraid, in a single direct SQL statement it is not possible as per my knowledge.
Yes you can create a multi-line table valued function. I have written the below code considering that you will call this function by passing the LedgerId.
And then call the function like this:
Yes you can create a multi-line table valued function. I have written the below code considering that you will call this function by passing the LedgerId.
create function dbo.retTable(@LedgerId Int)
returns @table table (LedgerId int, DivisionId int, ClosingDate date, opening int, closing int)
as
begin
declare @DivisionId int, @Opening int, @Closing int
select @DivisionId = 0, @Opening = 0, @Closing = 0
insert into @table
select * from myTable where LedgerId = @LedgerId
update @table
set Opening = case when isnull(opening,0) = 0 then @opening else opening end,
Closing = case when isnull(closing,0) = 0 then @closing else closing end,
@opening = case when DivisionId = @DivisionId then @closing else 0 end,
@opening = case when isnull(opening,0) = 0 then @opening else opening end,
@closing = case when isnull(closing,0) = 0 then @opening else closing end,
@Divisionid = divisionid
return
end
And then call the function like this:
select * from dbo.retTable(101)
ASKER
can you use a common table expression and make a view
I am not sure for the current case.
ASKER
can somebody please help me on this its very urgent...
is this what you want?
with b as (
select 101 ledgerid, 1 divisionid, '1-Apr-11' Closingdate, 10001 opening, null closing
union all select 101, 1, '2-Apr-11', null, null
union all select 101, 1, '3-Apr-11', 2500, null
union all select 101, 1, '4-Apr-11', null, 4250
union all select 101, 1, '5-Apr-11', null, null
union all select 101, 1, '6-Apr-11',null,60000
)
select ledgerid, divisionid, Closingdate, opening, closing,
ISNULL(opening, pre_closing) opening2,
isnull(ISNULL(closing, opening),pre_closing) closing2
from (
select
*,
(select top 1 isnull(closing, opening) from b b2 where b1.ledgerid=b2.ledgerid and b1.divisionid=b2.divisionid and b2.closingdate<b1.closingdate and isnull(b2.closing, b2.opening) is not null order by Closingdate desc) pre_closing
from b b1
) x
ledgerid divisionid Closingdate opening closing opening2 closing2
101 1 1-Apr-11 10001 NULL 10001 10001
101 1 2-Apr-11 NULL NULL 10001 10001
101 1 3-Apr-11 2500 NULL 2500 2500
101 1 4-Apr-11 NULL 4250 2500 4250
101 1 5-Apr-11 NULL NULL 4250 4250
101 1 6-Apr-11 NULL 60000 4250 60000
Good one HainCurt,
Just got reminded about the self joining and written below code to provide the result:
Just got reminded about the self joining and written below code to provide the result:
select x.ledgerid, x.divisionid, x.closingdate, isnull(x.opening,x.preclosing) new_opening,
isnull(isnull(x.closing,x.opening), preclosing) new_closing
from (
select a.*, (select top 1 isnull(closing,opening) from t1 b
where a.ledgerid = b.ledgerid and a.divisionid = b.divisionid
and b.closingdate < a.closingdate and isnull(b.closing,b.opening) is not null
order by b.closingdate desc) preclosing
from t1 a) x
ASKER
In the above code provide by kurt & rajeevnandanmishra the criteria used is ISNULL. what if instead of ISNULL i want to use '0'
Sorry for the late reply.
Sorry for the late reply.
Hi,
Below code can be used (or some variation in the HainCurt code, i am not good at CTE):
But you might need to consider the case if, there is actually opening/closing is going to be zero.
Below code can be used (or some variation in the HainCurt code, i am not good at CTE):
select x.ledgerid, x.divisionid, x.closingdate,
(case when isnull(x.opening,0) <> 0 then x.opening else x.preclosing end ) new_opening,
(case when isnull(x.closing,0) <> 0 then x.closing
when isnull(x.opening,0) <> 0 then x.opening
else preclosing end) new_closing
from (
select a.*, (select top 1 case when isnull(closing,0) = 0 then opening else closing end from t2 b
where a.ledgerid = b.ledgerid and a.divisionid = b.divisionid
and b.closingdate < a.closingdate and
(isnull(b.closing,0) <> 0 or isnull(b.opening,0)<>0)
order by b.closingdate desc) preclosing
from t2 a) x
But you might need to consider the case if, there is actually opening/closing is going to be zero.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot
having problems understanding this...