cancel
Showing results for 
Search instead for 
Did you mean: 

Usage of Control statement to dynamically manage Query iteration

Former Member
0 Kudos

Hi,

 

I have a scenario wherein the Query iteration needs to be controlled dynamically based on Input Parameter. I feel this scenario can be managed only byCursor but I am not sure about the usage of Control Statement within cursor to manage SQL iteration dynamically. Please suggest options other than Cursor in case you disagree.

Given scenario will provide you more clarity on my question.

 

Scenario:

 

In the given scenario Start Date/End Date will be provided by the Input Parameter. Consider Start Date/End Date is '20150105'/'20150315', then for each Month i.e Jan2015, Feb2015 and Mar2015 I need to get the ID numbers greater than the Maximum ID number from last month.

i.e. ID numbers for 20150101-20150131 should be greater than Maximum ID number from Dec2014, ID's of Feb2015 should be greater than Jan2015 and of Mar2015 should be greater than Feb2015. Also if same ID exist in multiple months then consider the ID only once when it appeared for the first month.

I can get Maximum ID number for Dec2014, Jan2015 and Feb2015 using Group By. Based on the Maximum ID and Input Months, I can get ID's greater than these prior months Max ID's for given date range.

 

Question:

 

How can I use Control Statement/Loop within the cursor that can dynamically control the SQL execution based on Input Parameter(
Number of months in given Date Range).

So if the Input Parameters is 20150105-20150315 then based on number of months, the Select Query should be executed 3 times to get ID's for each month greater than prior month Maximum ID's.

Finally how can I combine output of this 3 month in a single variable.

Any response will be much appreciated.

Regards

Randhir Jha

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

You've got a complex requirement here.

To make it easy to help you, please provide the minimal version of your problem with CREATE TABLE and INSERT STATEMENTS.

Also provide an example for the expected output.

- Lars

Former Member
0 Kudos

Hi Lars,

Thanks for your response.

Let me elaborate the requirement.

Step1) User will provide FromDate and ToDate through Input Parameter. Ex:

Input Parameter
FromTo
2015010520150315

Step2) Get Maximum ID from Transaction table for each Months given in the Input Parameter. Logic to get date to get Maximum ID is as follows:

First date will be (FromDate - 1 day) i.e 20150104, next date would be the last date of that month(i.e 20150131) and so on. Ex:

Maximum ID
MaxIDDate
7020150104
10120150131
19920150228

Step3) Get ID count greater than MaxID for each month.

Transaction Data
YearMonthID Number
20150140
20150130
201501 60
201501 101
20150280
201502150
201502 160
20150290
201502101
20150320
20150330
201503200
201503201
201503101
201503150
201503220

Final Output
YearMonthID Number > MaxID
201501101
201502150
201502160
201503200
201503201
201503220

Please let me know if you need further information to understand the requirement.

Regards

Randhir Jha

Former Member
0 Kudos

Hi,

You can use Windows function - LAG/LEAD in Scripted Calculation view to get above results.

Using window "lag" function, you can get the maximum id for each month based on year & month, starting from current month date range to rolling six month date range.

select year, month , max(id), lag(max(id) over(order by year, month),lag(month)(over( order by year, month)  from abc group by year, month

year     month      max(id)   last month max id        last month    

2014     04            400           ?                                  03

2014     05            500           400                              04

here date range will be from month 4 to month 6

Now you can do separate select like select year, month, id and do inner join with above result where id >last month max id and on year and month

I hope this may help...

Regards,

Kulwinder

Former Member
0 Kudos

Hi,

You can use Windows function - LAG/LEAD in Scripted Calculation view to get above results.

Using window "lag" function, you can get the maximum id for each month based on year & month, starting from current month date range to rolling six month date range.

select year, month , max(id), lag(max(id) over(order by year, month),lag(month)(over( order by year, month)  from abc group by year, month

year     month      max(id)   last month max id        last month     

2014     04            400           ?                                  03

2014     05            500           400                              04

here date range will be from month 4 to month 6

Now you can do separate select like select year, month, id and do inner join with above result where id >last month max id and on year and month

I hope this may help...

Regards,

Kulwinder

Former Member
0 Kudos

Thanks Kulwinder.

Lag function helped to achieve my requirement.

Windows function is an excellent feature which can make sql scripting simple even for the complex scenarios like this.

Regards

Randhir Jha

Answers (1)

Answers (1)

former_member185132
Active Contributor
0 Kudos

Cursors can be avoided by using nested selects. Try this code below.

I haven't used input parameters, but you can add them to the WHERE clause of the outer Select statement.


select id, date1 from nestedtest as A

where id >

(select max(id) from nestedtest

where month(date1) = month(add_months(A.date1,-1))

);

Former Member
0 Kudos

Hi Suhas,

Thanks for your reply.

I have already tried this option but the execution time for nested select with Millions of records is several minutes which is defintely not acceptable.

Regards

Randhir Jha