By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Simulate Cursor with recursive join

P: 63
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
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
Is this one time run? or will it be executed multiple times depending on "WHERE CUR.GrpNum = '187'"

-- CK
Dec 18 '08 #2

P: 63
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
Expert 2.5K+
P: 2,878
Sorry for the delay....

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

Expand|Select|Wrap|Line Numbers
  1. set nocount on
  2.  
  3. declare @ServiceCall table (Serial varchar(20), CallDate SmallDateTime, MeterReading int)
  4.  
  5. insert into @ServiceCall values ('K9465300065', '10/01/2008', 1000)
  6. insert into @ServiceCall values ('K9465300065', '11/01/2008', 1500)
  7. insert into @ServiceCall values ('K9465300065', '12/01/2008', 0)
  8. insert into @ServiceCall values ('K9465300065', '01/01/2009', 0)
  9. insert into @ServiceCall values ('K9465300065', '02/01/2009', 1200)
  10. insert into @ServiceCall values ('K9465300065', '03/01/2009', 0)
  11.  
  12.  
  13. select * from @ServiceCall
  14.  
  15. select serial, CallDate, MeterReading,
  16. 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 )
  17. from @ServiceCall s1
  18.  
  19.  

Happy Coding!

-- CK
Dec 22 '08 #4

P: 63
Thank you and Happy New Year!!
Dec 31 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.