Year Month and difference dynamic value

suryapkakani · Post by **suryapkakani** » Mon Oct 12, 2009 1:24 pm

Hi,

I have one of the source field coming as an year month ( eg. 200909). The requirement for me is to pass this as parameter to one of my query in the loop and get all the values of this month and all the 39 months prior to this month. How should i approach to get the difference in year months value.

chulett · Post by **chulett** » Mon Oct 12, 2009 1:37 pm

So, you need to do this in a SQL query? And you need all data in this date range or you need a list of the Year/Month values between them? Examples would be nice, btw.

suryapkakani · Post by **suryapkakani** » Mon Oct 12, 2009 1:50 pm

chulett wrote:So, you need to do this in a SQL query? And you need all data in this date range or you need a list of the Year/Month values between them? Examples would be nice, btw. ...

I have a query which is within a query as below

SELECT ief.frst_srvc_day,ief.erlst_process_day
FROM
#SrcSchema#.inpatient_event_fact ief
WHERE
TO_CHAR(ief.frst_srvc_day, 'YYYYMM') = #YrMoNbr#

I have Year Month Number coming as a parameter to this query. I want to get data for this month and all the 39 months prior to this. I know that I can include some logic on query to fetch the entire 39 months of data, but in our case the data from source is roughly 180 million records, and i want to process on a month to month basis, to have restartability. Now how should i get the difference in year month so that only 39 months are to be considered.

chulett · Post by **chulett** » Mon Oct 12, 2009 2:14 pm

So, you need to run this query 39 times, once for each Y/M combination going back that far, right? Sounds like you may already have a looping Sequence in place and just need a delimited list for use in the Start Loop stage? Or do you actually need something else?

suryapkakani · Post by **suryapkakani** » Mon Oct 12, 2009 2:17 pm

chulett wrote:So, you need to run this query 39 times, once for each Y/M combination going back that far, right? Sounds like you may already have a looping Sequence in place and just need a delimited list for use in the Start Loop stage? Or do you actually need something else?

I need the logic..of how to get the 39 months, from a given yearmonth value.

chulett · Post by **chulett** » Mon Oct 12, 2009 2:54 pm

You do realize, do you not, that you just repeated yourself without explaining anything or answering any of the questions asked, yes?

suryapkakani · Post by **suryapkakani** » Mon Oct 12, 2009 3:01 pm

chulett wrote:You do realize, do you not, that you just repeated yourself without explaining anything or answering any of the questions asked, yes?

Hi Chulett,

Sorry for not explaining in detail. I have the delimited list of data upon which I am making the loop. This is implemented for the historical load. Going across the incremental, I want to make use of the same loop, but it should be dynamic in nature ie the given parameter value and all the previous 39 months to the given year month value.

chulett · Post by **chulett** » Mon Oct 12, 2009 10:04 pm

Here's the thing... maybe I'm being a little dense today but I still have absolutely no idea what exactly it is you are trying to accomplish. I was hoping someone else would jump in here and show some insight, solve your problem, but that doesn't look like it's going to happen.

So... it seems like you've got the historical portion of this covered, I assume that's because your 'list' is hard-coded. Now, the conundrum is the going-forward incremental which is, as you put it 'dynamic in nature'. In order to help with that, we're going to need a much better idea what it is you need.

* How often will you be running this incremental process - monthly?
* Does your 'given parameter value', your starting point, change appropriately each month? 200909, 200910, etc.
* From the starting value, do you need to generate a delimited list going back 39 months each time?
--> 200909,200908,200907, ... 200606
--> 200910,200909,200908, ... 200607
(etc)

Or does the list need to go in the other direction or does it really not matter? Or is what you need completely different? If we can come out of this with a clear understanding of your need then perhaps a solution would be forthcoming as well. If not... [shrug]