April 23, 2012 at 3:01 pm
Hi there,
I've been trying to solve this issue for a while.
I have been asked create a job that runs each night to load numbers into a column. The numbers represent Fiscal Years
01 Other Previous Fiscal year (Every other)
02 Current Fiscal Year - 2 (1 Apr 2010 - 31 Mar 2011)
03 Current Fiscal Year - 1 (1 Apr 2011 - 31 Mar 2012)
04 Current Fiscal Year (1 Apr 2012 - 31 Mar 2013)
05 Current Fiscal Year + 1 (1 Apr 2013 - 31 Mar 2014)
06 Other Fiscal Years (All other future years)
Fiscal years run from 1st April to 31st March as seen above in brackets. I've given which periods the numbers represent
I'm using a date table that has every date from 2004 - 2015. I have a FISCAL_YEAR column which has the format (12-13...which is this years fiscal) and a standard DATE column.
FISCAL_YEAR DATE
11-12 2012-03-27
11-12 2012-03-28
11-12 2012-03-29
11-12 2012-03-30
11-12 2012-03-31
12-13 2012-04-01 < NEW FISCAL
12-13 2012-04-02
12-13 2012-04-03
12-13 2012-04-04
I'd like 04 placed against dates between 1st April 2012 - 31st March 2013 as this is the current fiscal year. and 03 for last years dates etc...
If the job runs on the 1st April next year I would want this to be the new fiscal year and 04 placed for all dates
Please can anyone help me... Any help provided is much appreciated
Thanks
April 23, 2012 at 3:16 pm
I am having hard time visualizing how your expected result looks like.. Can you proivde a visual example on how it should be?
April 23, 2012 at 3:33 pm
i think this get you what you want. being that i guessed your fiscal year column is a char or varchar field it got ugly. if you convert it to an int with just the first year of the fiscal year the same case statement gets alot simpler since we dont convert every thing to int's first before adding or subtracting and comparing our values. you will have to modify it a bit to get exactly what you need in your update but this should get you pointed in the right direction.
DECLARE @CurFiscal CHAR(5)
SET @CurFiscal = (SELECT fiscal_year from Calendar where Date = DATEADD(dd,0,DATEDIFF(dd,0,GETDATE())))
SELECT DISTINCT CASE WHEN CAST(LEFT(@CurFiscal,2) AS INT) + 1 = CAST(LEFT(fiscal_year,2) AS INT) THEN 05
WHEN CAST(LEFT(@CurFiscal,2) AS INT) = CAST(LEFT(fiscal_year,2) AS INT) THEN 04
WHEN CAST(LEFT(@CurFiscal,2) AS INT) - 1 = CAST(LEFT(fiscal_year,2) AS INT) THEN 03
WHEN CAST(LEFT(@CurFiscal,2) AS INT) - 2 = CAST(LEFT(fiscal_year,2) AS INT) THEN 02
WHEN CAST(LEFT(@CurFiscal,2) AS INT) - 2 > CAST(LEFT(fiscal_year,2) AS INT) THEN 01
WHEN CAST(LEFT(@CurFiscal,2) AS INT) + 1 < CAST(LEFT(fiscal_year,2) AS INT) THEN 06 END,
fiscal_year
FROM Calendar
ORDER BY fiscal_year
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 23, 2012 at 3:40 pm
Hi ColdCoffee, does this help:
Date Fiscal_Year Identifier
30-Mar-11 11-12 03
31-Mar-11 11-12 03
01-Apr-11 12-13 04
02-Apr-11 12-13 04
03-Apr-11 12-13 04
04-Apr-11 12-13 04
05-Apr-11 12-13 04
06-Apr-11 12-13 04
07-Apr-11 12-13 04
08-Apr-11 12-13 04
Each night, the code should determine the current date and with that, ascertain which codes identifier codes to asign within the date table. The date table has every date from 2004 to 2015.
04 will always be the current Fiscal year. so If the actual date was 5 April 2012, then 04 would cover the dates within the date table from 1 Apr 2012 to 31 Mar 2013.
03 will be assigned to the last fiscal year which would be 1 Apr 2011 - 31 Mar 2012.
02 will be assigned to dates for Fiscal year 2 years ago (1 Apr 2010 - 31 Mar 2012)
Any date before 1 Apr 2010 will have 01 assigned to each row.
The current Fiscal year +1 will be for the dates 1 Apr 2013 - 31 Mar 2014 and 05 will be assigned to any row where the dates are between these dates.
To be honest, the Fiscal_Year field was only mentioned in case there was a way to do what I wanted with this field.
I've had a look at DateAdd and DateDiff along with using the DATE field but can't get it to work
Hope that helps
Thanks
April 23, 2012 at 4:13 pm
cidr (4/23/2012)
Hi ColdCoffee, does this help:
Date Fiscal_Year Identifier
30-Mar-11 11-12 03
31-Mar-11 11-12 03
01-Apr-11 12-13 04
02-Apr-11 12-13 04
03-Apr-11 12-13 04
04-Apr-11 12-13 04
05-Apr-11 12-13 04
06-Apr-11 12-13 04
07-Apr-11 12-13 04
08-Apr-11 12-13 04
Each night, the code should determine the current date and with that, ascertain which codes identifier codes to asign within the date table. The date table has every date from 2004 to 2015.
04 will always be the current Fiscal year. so If the actual date was 5 April 2012, then 04 would cover the dates within the date table from 1 Apr 2012 to 31 Mar 2013.
03 will be assigned to the last fiscal year which would be 1 Apr 2011 - 31 Mar 2012.
02 will be assigned to dates for Fiscal year 2 years ago (1 Apr 2010 - 31 Mar 2012)
Any date before 1 Apr 2010 will have 01 assigned to each row.
The current Fiscal year +1 will be for the dates 1 Apr 2013 - 31 Mar 2014 and 05 will be assigned to any row where the dates are between these dates.
To be honest, the Fiscal_Year field was only mentioned in case there was a way to do what I wanted with this field.
I've had a look at DateAdd and DateDiff along with using the DATE field but can't get it to work
Hope that helps
Thanks
i figured that was the way you wanted your output. for every date replace the distinct with the date column of your calender table. i dont know of any way to set a fiscal year on the server side so any date would have to be looked up from your calendar table. since you use a CHAR field (11-12 yields -1 otherwise) we need to convert to an int to do any math on it. i decided to take the first left 2 characters and convert them to integers since they increase predictably from year to year. since this will be an sql job setting a variable with the current physical year is possible.
i also looking at it realized i can set the physical year to an int out side of the case which would look as follows:
DECLARE @CurFiscal INT
SET @CurFiscal = (SELECT CAST(LEFT(fiscal_year,2) AS INT) from Calendar where Date = DATEADD(dd,0,DATEDIFF(dd,0,GETDATE())))
SELECT DISTINCT CASE WHEN @CurFiscal + 1 = CAST(LEFT(fiscal_year,2) AS INT) THEN '05'
WHEN @CurFiscal = CAST(LEFT(fiscal_year,2) AS INT) THEN '04'
WHEN @CurFiscal - 1 = CAST(LEFT(fiscal_year,2) AS INT) THEN '03'
WHEN @CurFiscal - 2 = CAST(LEFT(fiscal_year,2) AS INT) THEN '02'
WHEN @CurFiscal - 2 > CAST(LEFT(fiscal_year,2) AS INT) THEN '01'
WHEN @CurFiscal + 1 < CAST(LEFT(fiscal_year,2) AS INT) THEN '06' END,
fiscal_year
FROM Calendar
ORDER BY fiscal_year
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 23, 2012 at 4:39 pm
Take a look at this and see if you couldn't incorporate this into your calendar table. Using a computed column should dynamically update the identifier column each day. Due to using the getdate() function, you can't persist the column, so you can't index on it.
Also, I wrote this code in SQL Server 2005, using SQL Server 2008, you can use the DATE data type.
CREATE TABLE dbo.Calendar (
CalDate DATETIME,
FiscalYear AS YEAR(DATEADD(mm,9,CalDate)),
Identifier AS (CASE WHEN (YEAR(DATEADD(mm,9,CalDate)) - YEAR(DATEADD(mm, 9, GETDATE()))) < -2 THEN '01'
WHEN (YEAR(DATEADD(mm,9,CalDate)) - YEAR(DATEADD(mm, 9, GETDATE()))) = -2 THEN '02'
WHEN (YEAR(DATEADD(mm,9,CalDate)) - YEAR(DATEADD(mm, 9, GETDATE()))) = -1 THEN '03'
WHEN (YEAR(DATEADD(mm,9,CalDate)) - YEAR(DATEADD(mm, 9, GETDATE()))) = 0 THEN '04'
WHEN (YEAR(DATEADD(mm,9,CalDate)) - YEAR(DATEADD(mm, 9, GETDATE()))) = 1 THEN '05'
ELSE '06'
END)
);
WITH
e1(n) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b),
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b),
e8(n) AS (SELECT a.n FROM e4 a CROSS JOIN e4 b),
tally(n) AS (SELECT 0 UNION ALL SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e8)
INSERT INTO dbo.Calendar(CalDate)
SELECT TOP (DATEDIFF(dd,'20040101','20160101'))
DATEADD(dd,n,'20040101')
FROM
tally;
SELECT * FROM dbo.Calendar;
DROP TABLE dbo.Calendar;
April 23, 2012 at 5:27 pm
Lynn i continue to learn from you. my kludge looks awful compared to the datemath you did. {facepalm} for not realizing i could just add 9 months to get the correct fiscal year.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 23, 2012 at 5:30 pm
capn.hector (4/23/2012)
Lynn i continue to learn from you. my kludge looks awful compared to the datemath you did. {facepalm} for not realizing i could just add 9 months to get the correct fiscal year.
First, thank you for the compliment. Second, I must apologize. When I read your post I could not help but laugh. A good natured laugh, but a laugh.
April 23, 2012 at 5:53 pm
Lynn Pettis (4/23/2012)
capn.hector (4/23/2012)
Lynn i continue to learn from you. my kludge looks awful compared to the datemath you did. {facepalm} for not realizing i could just add 9 months to get the correct fiscal year.First, thank you for the compliment. Second, I must apologize. When I read your post I could not help but laugh. A good natured laugh, but a laugh.
dont apologize for laughing. the {facepalm} was meant to draw some humorous laughs.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 23, 2012 at 6:18 pm
capn.hector (4/23/2012)
Lynn Pettis (4/23/2012)
capn.hector (4/23/2012)
Lynn i continue to learn from you. my kludge looks awful compared to the datemath you did. {facepalm} for not realizing i could just add 9 months to get the correct fiscal year.First, thank you for the compliment. Second, I must apologize. When I read your post I could not help but laugh. A good natured laugh, but a laugh.
dont apologize for laughing. the {facepalm} was meant to draw some humorous laughs.
Wasn't the {facepalm} as much as you calling your code a kludge. I actually haven't looked at your code. I was working on mine when you posted yours. After posting mine I came home.
April 23, 2012 at 6:37 pm
Lynn Pettis (4/23/2012)
capn.hector (4/23/2012)
Lynn Pettis (4/23/2012)
capn.hector (4/23/2012)
Lynn i continue to learn from you. my kludge looks awful compared to the datemath you did. {facepalm} for not realizing i could just add 9 months to get the correct fiscal year.First, thank you for the compliment. Second, I must apologize. When I read your post I could not help but laugh. A good natured laugh, but a laugh.
dont apologize for laughing. the {facepalm} was meant to draw some humorous laughs.
Wasn't the {facepalm} as much as you calling your code a kludge. I actually haven't looked at your code. I was working on mine when you posted yours. After posting mine I came home.
went about the same route you did just on different info. i did the face palm once i figured out why you added 9 months. and i called it a kludge since im converting text to nums instead of the adding 9 months. of course if the OP cant change the calendar table and has to deal with the text field it might be the better way to do it.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 23, 2012 at 6:43 pm
What I like about my code is that I made the Identifier column a computed column in my test Calendar table. Makes the column dynamic, it updates itself when queried based on the current date. No need to run an update on a daily basis.
April 23, 2012 at 6:46 pm
Lynn Pettis (4/23/2012)
What I like about my code is that I made the Identifier column a computed column in my test Calendar table. Makes the column dynamic, it updates itself when queried based on the current date. No need to run an update on a daily basis.
after rereading the OP's post i also wonder why its not a yearly (midnight on xxxx-04-01) but as you said your dynamic option does get rid of the need for the update.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 24, 2012 at 10:08 am
Lynn Pettis 23/04/2012
Take a look at this and see if you couldn't incorporate this into your calendar table. Using a computed column should dynamically update the identifier column each day. Due to using the getdate() function, you can't persist the column, so you can't index on it.
capn.hector 23/04/2012
i also looking at it realized i can set the physical year to an int out side of the case which would look as follows:
Thanks for the help folks. Both scripts worked. I choose yours Lynn end, since there wasn't a variable. I need to think about my dates more.
Cheers
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply