Simulate Cursor with recursive join

rmurgia

INSERT RMTEST
SELECT CUR.RecCnt,CUR.NextRecCnt,CUR.PriorRecCnt,CUR.Seri alNum,CUR.CompDate,CUR.CompTime,CUR.MeterReading,
MeterReadingRev =
Cast(Case
When CUR.SerialNum <> PRI.SerialNum
Then IsNull(CUR.MeterReading,0)
When CUR.MeterReading = 0
--Then IsNull(TGT.MeterReadingRev,0)
Then Cast(TGT.RecCnt As Decimal(13,2))
Else IsNull(CUR.MeterReading,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.MeterReadingRev,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?

Dec 17 '08 #1

ck9663

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

-- CK

Dec 18 '08 #2

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.

Dec 21 '08 #3

ck9663

Sorry for the delay....

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


Happy Coding!

-- CK

Dec 22 '08 #4

rmurgia

Thank you and Happy New Year!!

Dec 31 '08 #5