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?
|