July 15, 2006 at 5:27 pm
Hi All,
I have a Book table like:
ExpireMonth RefID Amount
200401 A000 130.30
200402 A000 200.20
....
200412 A000 90.30
200401 A001 73.00
200402 A001 111.00
200405 A001 20.00
...
200412 A001 100.00
This is one year data for the RefID A001 and A002 in my book table. In the above sample: for the A001, the month 200403 and 200404 have been lost. In this case, I want show these two months' amount with the value in same month but in A000.
My question is how many ways we can implement this, i know we can use the EXISTS clause, any other solution? ie. Join?
Thanks
Micror
July 17, 2006 at 6:29 am
here is the join:
select
A000.Expiremonth,
case when all.refid is null then A000.refid else all.refid end as refid,
case when all.amount is null then A000.amount else all.amount end as amount
from Booktable A000 left join booktable all
on A000.expiremonth = all.expiremonth
where A000.refid = A000
B
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply