April 22, 2016 at 8:35 am
I have the conditional split shown below. It works well for now, but when the fiscal year changes in October it won't. :unsure:
[Acct Year] >= DATEPART("yyyy",GETDATE())
[Acct Year] == DATEPART("yyyy",GETDATE()) - 1
Output is directed to either a PriorFY or a CurrFY file. Running the extract now(April 2016) correctly routes 2016 data to the CurrFY file and 2015 data to the PriorFY file. In October thru December 2016, [Acct Year] will be either 2016 or 2017. The 2017 data should be routed to the CurrFY file (which I believe it will be) and the 2016 data should be routed to the PriorFY file (which I don't think it will be).
So, There are 6 conditions I need to evaluate:
1. [Acct Year] = Current/Run Date
AND Current Month <10 (route to CurrFY ) FY 2016 data, run on April 2016
2. [Acct Year] = Current/Run Date
AND Current Month >=10 (route to PriorFY ) FY 2016 data, run in Nov 2016
3. [Acct Year] > Current/Run Date
AND Current Month <10 (route to Exceptions (this should never happen) ) FY 2017 data, run in April 2016
4. [Acct Year] > Current/Run Date
AND Current Month >=10 (route to CurrFY ) FY 2017 data, run in Nov 2016
5. [Acct Year] < Current/Run Date
AND Current Month <10 (route to PriorFY )FY 2015 data, run in April 2016
6. [Acct Year] < Current/Run Date
AND Current Month >=10 (route to Exceptions ) FY 2015 data, run in Nov 2016(unlikely)
So, here is how I have coded the conditional split expressions:
CurrFY: ([Acct Year] == DATEPART("yyyy",GETDATE()) && DATEPART("mm",GETDATE()) < 10) || ([Acct Year] > DATEPART("yyyy",GETDATE()) && DATEPART("mm",GETDATE()) >= 10)
PriorFY: ([Acct Year] == DATEPART("yyyy",GETDATE()) && DATEPART("mm",GETDATE()) >= 10) || ([Acct Year] < DATEPART("yyyy",GETDATE()) && DATEPART("mm",GETDATE()) < 10)
Exceptions: ([Acct Year] > DATEPART("yyyy",GETDATE()) && DATEPART("mm",GETDATE()) < 10) || ([Acct Year] < DATEPART("yyyy",GETDATE()) && DATEPART("mm",GETDATE()) >= 10)
Is this the correct approach? How can I test conditions 2 or 4?
Thanks for any advice.
April 22, 2016 at 8:51 am
What concerns me a little about your approach is that you seem to be deriving information about your source data based on the date/time the job runs.
Would it not be better to test a date from your source data (transaction date?) & derive the accounting year from that?
Once you have that, you can decide whether or not it is valid.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 22, 2016 at 9:10 am
Thanks, Phil.
[Acct Year] is determined in the T-SQL stored procedure that runs and loads the table being extracted. So that data coming in when this runs today has either 2015 or 2016 in the [Acct Year] column.
This extract is being used to provide another organization with our data as part of an affiliation agreement. They operate on a fiscal calendar where Oct 1 starts a new fiscal year.
So, when this runs in October, the [Acct Year] will have 2017 and 2016 (and slim possibility of 2015 depending on the date range they request).
When this runs today (through 2015-09-30) I want to put 2015 data in the Prior Year file and the 2016 data in the Current Year file.
On Oct 1, and after that, I want to put 2017 data in the Current year file and put 2016 data in the Prior Year file.
Without examining GETDATE, I'm not sure how to route the data properly. Unless I add a column to the table to indicate 'Curr', 'Prior','Other'? Although I think that would still be based on GETDATE in the T-SQL.
April 22, 2016 at 9:20 am
alicesql (4/22/2016)
Thanks, Phil.[Acct Year] is determined in the T-SQL stored procedure that runs and loads the table being extracted. So that data coming in when this runs today has either 2015 or 2016 in the [Acct Year] column.
This extract is being used to provide another organization with our data as part of an affiliation agreement. They operate on a fiscal calendar where Oct 1 starts a new fiscal year.
So, when this runs in October, the [Acct Year] will have 2017 and 2016 (and slim possibility of 2015 depending on the date range they request).
When this runs today (through 2015-09-30) I want to put 2015 data in the Prior Year file and the 2016 data in the Current Year file.
On Oct 1, and after that, I want to put 2017 data in the Current year file and put 2016 data in the Prior Year file.
Without examining GETDATE, I'm not sure how to route the data properly. Unless I add a column to the table to indicate 'Curr', 'Prior','Other'? Although I think that would still be based on GETDATE in the T-SQL.
So the period which defines [Acct Year] for you is different from the target 1/10–30/09 period, is that correct?
Does that not mean that when you see an Acct Year of 2016, for example, that you cannot be sure what the target year should be? Because, depending on when in 2016 the transaction took place, the target acct year might be 2015, 2016 or 2017?
Based on what you have written, I'm thinking that you'll potentially have problems around the end of September. Data loaded on 30/9 would be classified differently from the same data loaded on 1/10. Is that what you want?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 22, 2016 at 10:28 am
You're right.
If an extract is requested for the date range 2016-09-01 through 2016-09-30 ON 2016-10-01, the data will have 2016 as the [Acct Year] and will end up in the Prior Year file. Which is probably not the way to go.
(I say probably because I'm not certain what our affiliate will want)
If the date range of 2016-10-01 through 2016-10-31 is requested on 2016-11-01,data will flow correctly to the CurrYr file.
The table being extracted does not currently have a transaction date column(not specified by affiliate). But I can add one and not extract it to the flat file.
So then I could compare the [Acct Year) to DATEPART("yyyy",[GLDate])
when they are equal and the DATEPART("mm",[GLDate] <10, output to the CurrYr file
But, when I run a date range of 2015-09-01 through 2015-09-30 today(2016-04-22), I'll have Acct Year = 2015 and DATEPART("yyyy",[GLDate]) will be 2015 and that data will end up in the CurrYr file too, and it should go to the PriorYr file.
I'll need to work through the logic some more next week (another issue is demanding my attention right now).
To be continued....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply