on 05-13-2015 4:31 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Lars,
Thanks for your response.
Let me elaborate the requirement.
Step1) User will provide FromDate and ToDate through Input Parameter. Ex:
Input Parameter | |
From | To |
20150105 | 20150315 |
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 | |
MaxID | Date |
70 | 20150104 |
101 | 20150131 |
199 | 20150228 |
Step3) Get ID count greater than MaxID for each month.
Transaction Data | ||
Year | Month | ID Number |
2015 | 01 | 40 |
2015 | 01 | 30 |
2015 | 01 | 60 |
2015 | 01 | 101 |
2015 | 02 | 80 |
2015 | 02 | 150 |
2015 | 02 | 160 |
2015 | 02 | 90 |
2015 | 02 | 101 |
2015 | 03 | 20 |
2015 | 03 | 30 |
2015 | 03 | 200 |
2015 | 03 | 201 |
2015 | 03 | 101 |
2015 | 03 | 150 |
2015 | 03 | 220 |
Final Output | ||
Year | Month | ID Number > MaxID |
2015 | 01 | 101 |
2015 | 02 | 150 |
2015 | 02 | 160 |
2015 | 03 | 200 |
2015 | 03 | 201 |
2015 | 03 | 220 |
Please let me know if you need further information to understand the requirement.
Regards
Randhir Jha
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
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
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))
);
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.