Simulate Cursor with recursive join

[rmurgia]

INSERT RMTEST
SELECT CUR.RecCnt,CUR. NextRecCnt,CUR. PriorRecCnt,CUR .SerialNum,CUR. CompDate,CUR.Co mpTime,CUR.Mete rReading,
MeterReadingRev =
Cast(Case
When CUR.SerialNum <> PRI.SerialNum
Then IsNull(CUR.Mete rReading,0)
When CUR.MeterReadin g = 0
--Then IsNull(TGT.Mete rReadingRev,0)
Then Cast(TGT.RecCnt As Decimal(13,2))
Else IsNull(CUR.Mete rReading,0)
End As Decimal(13,2))
FROM TmpSAMNP2060 CUR
LEFT JOIN TmpSAMNP2060 NXT
ON CUR.NextRecCnt = NXT.RecCnt
LEFT JOIN TmpSAMNP2060 PRI
ON CUR.PriorRecCnt = PRI.RecCnt
LEFT JOIN RMTest TGT
ON CUR.PriorRecCnt = TGT.RecCnt
WHERE CUR.GrpNum = '187'

The results are as follows:
Prior
RecCnt RecCnt SerialNum MeterReading MeterReadingRev
572683 572682 K9465300065 320484.00 320484.00
572684 572683 K9465300065 .00 NULL
572685 572684 K9465300065 .00 NULL
572686 572685 K9465300065 .00 NULL
572687 572686 K9465300065 .00 NULL
572688 572687 K9465300065 .00 NULL

The program should check if the MeterReading is zero and if it is, make the MeterReadingRev the same as the prior non-zero meter reading field, so in this case all of the records should have 320484.00 in the Meter Reading Rev field. Note that the code which does this was commented out: Then IsNull(TGT.Mete rReadingRev,0) and replaced with: Then Cast(TGT.RecCnt As Decimal(13,2)) to see if the RecCnt field was being populated on the TGT file and it is not. Does anyone have any ideas?

[ck9663]

Is this one time run? or will it be executed multiple times depending on "WHERE CUR.GrpNum = '187'"

-- CK

[rmurgia]

CK,

The idea is to take a table with the following data:

Serial # Call Date Meter Reading
K9465300065 10/01/2008 1000
K9465300065 11/01/2008 1500
K9465300065 12/01/2008 0
K9465300065 01/01/2009 2000

Each row represents a service call. Ultimately the goal is to determine the number of copies by subtracting the prior meter reading from the current meter reading. In the case of the 11/01/2008 call, we would subtract 1000 from 1500 to get a total of 500 copies. The problem is that sometimes the meter reading is not entered, so we want to use the prior entered meter reading as in 12/01/2008. We created a new field, Meter Reading Rev which will be the same as the Meter Reading except when the meter reading is 0, in which it will use the 11/01/2008 meter reading. It will only read through the table one time.

[ck9663]

Sorry for the delay....

Here's what I got. You may just use the technique. I just created a new field for you.

set nocount on

declare @ServiceCall table (Serial varchar(20), CallDate SmallDateTime, MeterReading int)

insert into @ServiceCall values ('K9465300065', '10/01/2008', 1000)
insert into @ServiceCall values ('K9465300065', '11/01/2008', 1500)
insert into @ServiceCall values ('K9465300065', '12/01/2008', 0)
insert into @ServiceCall values ('K9465300065', '01/01/2009', 0)
insert into @ServiceCall values ('K9465300065', '02/01/2009', 1200)
insert into @ServiceCall values ('K9465300065', '03/01/2009', 0)


select * from @ServiceCall

select serial, CallDate, MeterReading,
NewMeterReading = (select top 1 meterreading from @ServiceCall s2 where s1.serial = s2.serial and s1.calldate > s2.calldate and s2.meterreading > 0 order by calldate desc )
from @ServiceCall s1

Happy Coding!

-- CK

[rmurgia]

Thank you and Happy New Year!!