## How to calculate finacial year in mssql 2000

 Author Message raghuldrag SSCrazy Group: General Forum Members Points: 2686 Visits: 573 Hi Friends,i have a table like below i want to display financial year count `Create table test(M1 vacrhar(20), M2 char(100))insert into test values ('2001-2002','APR')insert into test values ('2001-2002','JUN')insert into test values ('2002-2003','NOV')insert into test values ('2003-2004','APR')insert into test values ('2001-2002','JUN')insert into test values ('2003-2004','DEC')`if we gave the input from m1 ='2001-2002' and m2="APR' to m1='2003-2004' and m2='APR' i have to display no of financial year count (Apr-1999 to mar-2000) as one financial year kindly give me your suggestion twin.devil SSC-Insane Group: General Forum Members Points: 20449 Visits: 2949 A calendar table can help a great deal for such working and also some other calculations. Just type Calendar table on the search SSC and you will see tons of material. You can extend these tables with financial year columns of your need. It will help you a great deal in your current design and may answer your future queries as well. HappyGeek SSCoach Group: General Forum Members Points: 15375 Visits: 7591 Before you continue you may wish to consider the data types you have and what you are trying to achieve, The following may help in the right direction:`SELECT M1, M2, CAST(M2 + ' 01 ' + CAST(LEFT(M1, 4) as char(4)) as date) MonthStart,DATEPART(QUARTER, DATEADD(MONTH, -3, CAST(M2 + ' 01 ' + CAST(LEFT(M1, 4) as char(4)) as date))) FinQtr,CASEWHEN DATEPART(mm, CAST(M2 + ' 01 ' + CAST(LEFT(M1, 4) as char(4)) as date)) < 4 THEN DATEPART(YEAR, CAST(M2 + ' 01 ' + CAST(LEFT(M1, 4) as char(4)) as date))ELSE DATEPART(YEAR, CAST(M2 + ' 01 ' + CAST(RIGHT(M1, 4) as char(4)) as date))END FinYear FROM TESTORDER BY FinYear` ... drew.allen SSC Guru Group: General Forum Members Points: 67128 Visits: 17675 First, when working with dates, store them as dates. Part of the reason that you're having problems is that you aren't storing your dates as dates.The main issue that people run into when trying to solve this problem, is that they try to preserve information that is irrelevant for the problem at hand. Specifically, if you're looking at differences in years, anything more granular than a year is irrelevant.You also talk about inputs, but then your sample data is a table. I did a self join to get two dates to work with.`/* Step 1: Adjust the whole fiscal year to match the calendar year. DATEADD(MONTH, -3, Step 2: Find the difference in years between the two adjusted values.*/;WITH Test_Corrected AS( SELECT * FROM #Test CROSS APPLY ( VALUES( CAST('01 ' + RTRIM(M2) + ' ' + CASE WHEN M2 IN ('Jan', 'Feb', 'Mar') THEN RIGHT(M1, 4) ELSE LEFT(M1, 4) END AS DATE)) ) fy(fy_month_start))SELECT FromDate.fy_month_start AS FromDate, ToDate.fy_month_start AS ToDate, DATEDIFF(YEAR, DATEADD(MONTH, -3, FromDate.fy_month_start), DATEADD(MONTH, -3, ToDate.fy_month_start)), *FROM Test_Corrected FromDateINNER JOIN Test_Corrected ToDate ON FromDate.fy_month_start < ToDate.fy_month_startORDER BY FromDate.fy_month_start, ToDate.fy_month_start`Drew J. Drew AllenBusiness Intelligence AnalystPhiladelphia, PAHow to post data/code on a forum to get the best help.How to Post Performance ProblemsMake sure that you include code in the appropriate IFCode tags, e.g. [code=sql][/code]. You can find the IFCode tags under the INSERT options when you are writing a post. Jeff Moden SSC Guru Group: General Forum Members Points: 895219 Visits: 48196 +xraghuldrag - Sunday, December 17, 2017 10:47 PMHi Friends,i have a table like below i want to display financial year count `Create table test(M1 vacrhar(20), M2 char(100))insert into test values ('2001-2002','APR')insert into test values ('2001-2002','JUN')insert into test values ('2002-2003','NOV')insert into test values ('2003-2004','APR')insert into test values ('2001-2002','JUN')insert into test values ('2003-2004','DEC')`if we gave the input from m1 ='2001-2002' and m2="APR' to m1='2003-2004' and m2='APR' i have to display no of financial year count (Apr-1999 to mar-2000) as one financial year kindly give me your suggestionConsidering that neither your table nor your query makes any mention of either the year 1999 or 2000, please explain the logic you're using to convert the inputs to the desired output. --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs raghuldrag SSCrazy Group: General Forum Members Points: 2686 Visits: 573 +xdrew.allen - Monday, December 18, 2017 1:19 PMFirst, when working with dates, store them as dates. Part of the reason that you're having problems is that you aren't storing your dates as dates.The main issue that people run into when trying to solve this problem, is that they try to preserve information that is irrelevant for the problem at hand. Specifically, if you're looking at differences in years, anything more granular than a year is irrelevant.You also talk about inputs, but then your sample data is a table. I did a self join to get two dates to work with.`/* Step 1: Adjust the whole fiscal year to match the calendar year. DATEADD(MONTH, -3, Step 2: Find the difference in years between the two adjusted values.*/;WITH Test_Corrected AS( SELECT * FROM #Test CROSS APPLY ( VALUES( CAST('01 ' + RTRIM(M2) + ' ' + CASE WHEN M2 IN ('Jan', 'Feb', 'Mar') THEN RIGHT(M1, 4) ELSE LEFT(M1, 4) END AS DATE)) ) fy(fy_month_start))SELECT FromDate.fy_month_start AS FromDate, ToDate.fy_month_start AS ToDate, DATEDIFF(YEAR, DATEADD(MONTH, -3, FromDate.fy_month_start), DATEADD(MONTH, -3, ToDate.fy_month_start)), *FROM Test_Corrected FromDateINNER JOIN Test_Corrected ToDate ON FromDate.fy_month_start < ToDate.fy_month_startORDER BY FromDate.fy_month_start, ToDate.fy_month_start`DrewMy database is Ms sql 2000 so this with clause wont support raghuldrag SSCrazy Group: General Forum Members Points: 2686 Visits: 573 +xJeff Moden - Monday, December 18, 2017 2:30 PM+xraghuldrag - Sunday, December 17, 2017 10:47 PMHi Friends,i have a table like below i want to display financial year count `Create table test(M1 vacrhar(20), M2 char(100))insert into test values ('2001-2002','APR')insert into test values ('2001-2002','JUN')insert into test values ('2002-2003','NOV')insert into test values ('2003-2004','APR')insert into test values ('2001-2002','JUN')insert into test values ('2003-2004','DEC')`if we gave the input from m1 ='2001-2002' and m2="APR' to m1='2003-2004' and m2='APR' i have to display no of financial year count (Apr-1999 to mar-2000) as one financial year kindly give me your suggestionConsidering that neither your table nor your query makes any mention of either the year 1999 or 2000, please explain the logic you're using to convert the inputs to the desired output.Hi Jeff,My table contains from 2000-2001 financial year data like above it is a typo error "(Apr-1999 to mar-2000)" I just want to calculate financial year count based on my input M1 between '2001-2002' and '2003-2004' and M2 between 'Apr' and 'Jan'my financial year calculated from Apr to mar = 1 yearso my desired output isAccYear Financial Year count2001-2002 12002-2003 22003-2004 3 HappyGeek SSCoach Group: General Forum Members Points: 15375 Visits: 7591 +xraghuldrag - Monday, December 18, 2017 9:22 PM+xJeff Moden - Monday, December 18, 2017 2:30 PM+xraghuldrag - Sunday, December 17, 2017 10:47 PMHi Friends,i have a table like below i want to display financial year count `Create table test(M1 vacrhar(20), M2 char(100))insert into test values ('2001-2002','APR')insert into test values ('2001-2002','JUN')insert into test values ('2002-2003','NOV')insert into test values ('2003-2004','APR')insert into test values ('2001-2002','JUN')insert into test values ('2003-2004','DEC')`if we gave the input from m1 ='2001-2002' and m2="APR' to m1='2003-2004' and m2='APR' i have to display no of financial year count (Apr-1999 to mar-2000) as one financial year kindly give me your suggestionConsidering that neither your table nor your query makes any mention of either the year 1999 or 2000, please explain the logic you're using to convert the inputs to the desired output.Hi Jeff,My table contains from 2000-2001 financial year data like above it is a typo error "(Apr-1999 to mar-2000)" I just want to calculate financial year count based on my input M1 between '2001-2002' and '2003-2004' and M2 between 'Apr' and 'Jan'my financial year calculated from Apr to mar = 1 yearso my desired output isAccYear Financial Year count2001-2002 12002-2003 22003-2004 3Not entirely convinced that can be achieved as the actual year crosses the boundary of two financial years: you have not included sample data for Jan, Feb or Mar, so where would you expect that to sit within your result set? ... drew.allen SSC Guru Group: General Forum Members Points: 67128 Visits: 17675 +xraghuldrag - Monday, December 18, 2017 9:12 PM+xdrew.allen - Monday, December 18, 2017 1:19 PMFirst, when working with dates, store them as dates. Part of the reason that you're having problems is that you aren't storing your dates as dates.The main issue that people run into when trying to solve this problem, is that they try to preserve information that is irrelevant for the problem at hand. Specifically, if you're looking at differences in years, anything more granular than a year is irrelevant.You also talk about inputs, but then your sample data is a table. I did a self join to get two dates to work with.`/* Step 1: Adjust the whole fiscal year to match the calendar year. DATEADD(MONTH, -3, Step 2: Find the difference in years between the two adjusted values.*/;WITH Test_Corrected AS( SELECT * FROM #Test CROSS APPLY ( VALUES( CAST('01 ' + RTRIM(M2) + ' ' + CASE WHEN M2 IN ('Jan', 'Feb', 'Mar') THEN RIGHT(M1, 4) ELSE LEFT(M1, 4) END AS DATE)) ) fy(fy_month_start))SELECT FromDate.fy_month_start AS FromDate, ToDate.fy_month_start AS ToDate, DATEDIFF(YEAR, DATEADD(MONTH, -3, FromDate.fy_month_start), DATEADD(MONTH, -3, ToDate.fy_month_start)), *FROM Test_Corrected FromDateINNER JOIN Test_Corrected ToDate ON FromDate.fy_month_start < ToDate.fy_month_startORDER BY FromDate.fy_month_start, ToDate.fy_month_start`DrewMy database is Ms sql 2000 so this with clause wont supportYes, but SQL 2000 does support derived tables and a CTE and derived table are equivalent in this case. SQL 2000 also doesn't support the CROSS APPLY, but that was only used to prevent having to repeat the formulas. You should be able to figure out how to translate this information into something that will work in SQL 2000.Also, SQL 2000 is no longer supported. Why are you still on SQL 2000?Drew J. Drew AllenBusiness Intelligence AnalystPhiladelphia, PAHow to post data/code on a forum to get the best help.How to Post Performance ProblemsMake sure that you include code in the appropriate IFCode tags, e.g. [code=sql][/code]. You can find the IFCode tags under the INSERT options when you are writing a post. drew.allen SSC Guru Group: General Forum Members Points: 67128 Visits: 17675 +xHappyGeek - Monday, December 18, 2017 11:26 PM+xraghuldrag - Monday, December 18, 2017 9:22 PM+xJeff Moden - Monday, December 18, 2017 2:30 PM+xraghuldrag - Sunday, December 17, 2017 10:47 PMHi Friends,i have a table like below i want to display financial year count `Create table test(M1 vacrhar(20), M2 char(100))insert into test values ('2001-2002','APR')insert into test values ('2001-2002','JUN')insert into test values ('2002-2003','NOV')insert into test values ('2003-2004','APR')insert into test values ('2001-2002','JUN')insert into test values ('2003-2004','DEC')`if we gave the input from m1 ='2001-2002' and m2="APR' to m1='2003-2004' and m2='APR' i have to display no of financial year count (Apr-1999 to mar-2000) as one financial year kindly give me your suggestionConsidering that neither your table nor your query makes any mention of either the year 1999 or 2000, please explain the logic you're using to convert the inputs to the desired output.Hi Jeff,My table contains from 2000-2001 financial year data like above it is a typo error "(Apr-1999 to mar-2000)" I just want to calculate financial year count based on my input M1 between '2001-2002' and '2003-2004' and M2 between 'Apr' and 'Jan'my financial year calculated from Apr to mar = 1 yearso my desired output isAccYear Financial Year count2001-2002 12002-2003 22003-2004 3Not entirely convinced that can be achieved as the actual year crosses the boundary of two financial years: you have not included sample data for Jan, Feb or Mar, so where would you expect that to sit within your result set?Yes, it can work. I've been doing FY calculations since SQL 7.0. The underlying logic has not changed, I've just used non-SQL2000 syntax to make it shorter in what I posted above.Drew J. Drew AllenBusiness Intelligence AnalystPhiladelphia, PAHow to post data/code on a forum to get the best help.How to Post Performance ProblemsMake sure that you include code in the appropriate IFCode tags, e.g. [code=sql][/code]. You can find the IFCode tags under the INSERT options when you are writing a post.