While Lop in Place of Cursor

Question

While Lop in Place of Cursor

niladri.primalink

SSCommitted

Points: 1751
More actions
February 16, 2014 at 1:46 pm

#304430

Hi All,
I have a table called Table1 where I have five fields like Tableid, Processigndate, Amount, remainingCollectonCount and Frequency. All total I have more than 5Lacs records.
Now I need to fill up another table Called FutuecashFlow taking the records from Table1. There will be also five Columns like FutureCashflowid, Table1id, Processigndate, Amount.
Now the condition is that if the remainingCollectonCount =6 and the frequency is 12 then there will be the 6 entries in the futurecasflow table where the prcessign datae wille be addeed by 1 month.
For example Table1
Tableid, Processigndate Amount remainingCollectonCount Frequencu
1 2014-02-15 48 8 12
the future cash flow table the prcessing date column will be shown in the following way
Processigndate
2014-03-15
2014-04-15
2014-05-15
2014-06-15
2014-07-15
2014-09-15
2014-10-15
I do not to want to use cursor....is there any better way to solve?
Please help!!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Do you have sample data and table structures for us to use? This will significantly improve your chances of getting a viable answer.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

niladri.primalink (2/16/2014)
Hi All,
I have a table called Table1 where I have five fields like Tableid, Processigndate, Amount, remainingCollectonCount and Frequency. All total I have more than 5Lacs records.
Now I need to fill up another table Called FutuecashFlow taking the records from Table1. There will be also five Columns like FutureCashflowid, Table1id, Processigndate, Amount.
Now the condition is that if the remainingCollectonCount =6 and the frequency is 12 then there will be the 6 entries in the futurecasflow table where the prcessign datae wille be addeed by 1 month.
For example Table1
Tableid, Processigndate Amount remainingCollectonCount Frequencu
1 2014-02-15 48 8 12
the future cash flow table the prcessing date column will be shown in the following way
Processigndate
2014-03-15
2014-04-15
2014-05-15
2014-06-15
2014-07-15
2014-09-15
2014-10-15
I do not to want to use cursor....is there any better way to solve?
Please help!!!

Why did you skip 2014-08-15 in the example above?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

niladri.primalink SSCommitted Points: 1751 More actions · Answer 3

Hi Jeff,

I have forgot to mention it....Sorry for that.

if the remainingCollectonCount =8 and the frequency =12 then in the futurecashflow table the next 12 months will be added by 1 month. if the frequency =3 then processingdate in the futurecashflow table will be added with a jump of 3 months.

in this way I want to populate my futurecashflow table.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

niladri.primalink (2/16/2014)
Hi Jeff,
I have forgot to mention it....Sorry for that.
if the remainingCollectonCount =8 and the frequency =12 then in the futurecashflow table the next 12 months will be added by 1 month. if the frequency =3 then processingdate in the futurecashflow table will be added with a jump of 3 months.
in this way I want to populate my futurecashflow table.

So why didn't you skip 2014-03-15 for the original record of 1 2014-02-15 48 8 12?

I'm pretty sure this can easily be done without a cursor but I just don't understand your rules.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)