473,320 Members | 1,914 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Simulate Cursor with recursive join

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
4 2560
ck9663
2,878 Expert 2GB
Is this one time run? or will it be executed multiple times depending on "WHERE CUR.GrpNum = '187'"

-- CK
Dec 18 '08 #2
rmurgia
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
2,878 Expert 2GB
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
rmurgia
63
Thank you and Happy New Year!!
Dec 31 '08 #5

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

Similar topics

1
by: Bernard Drolet | last post by:
Hi, I have a recursive table "Objectnew" with the columns "objectref" (primary key), objectname, objectid, and parentobjectid Recursive Relation: objectid ----< Parentobjectid I have the...
2
by: Christian | last post by:
Hello, As the subject says it, is there a way to simulate a special keypress in JS ? In my case, on loading an HTML page, i'd like to set the cursor at the end of the input text of an <input...
1
by: teddysnips | last post by:
SQL Server 2000 I have a stored procedure that uses an extended SPROC to send an email notification to my customers when a document is distributed. However, the SPROC has an unexpected side...
4
by: Nicolas Vigier | last post by:
Hello, I have in my python script a function that look like this : def my_function(arg1, arg2, opt1=0, opt2=1, opt3=42): if type(arg1) is ListType: for a in arg1: my_function(a, arg2,...
25
by: Mike MacSween | last post by:
Regular viewers may want to turn off now. This will be an orchestral management system. Musicians and other staff being booked/paid for jobs. A job may contain other jobs, e.g: World Tour...
20
by: Gary Manigian | last post by:
I have 2 tables, one-to-many, that contain bills of material(BOMs): tblBOM: lngBOMID (PK) strAssemblyPartNo strDescription tblBOMDetail: lngBOMDetailID (PK) lngBOMID (FK)
1
by: klalonde | last post by:
I was researching opinions on using cursors in stored procedures and found a thread http://www.thescripts.com/forum/thread143091.html discussing (generally) why not to use them. I took a...
2
by: sebastien.abeille | last post by:
Hello, I would like to create a minimalist file browser using pyGTK. Having read lot of tutorials, it seems to me that that in my case, the best solution is to have a gtk.TreeStore containing...
2
by: Martin Marcher | last post by:
Hello, I'm playing around with os.walk and I made up del_tree(path) which I think is correct (in terms of the algorithm, but not as python wants it :)). As soon as some directory is deleted...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.