December 17, 2017 at 10:47 pm
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
December 17, 2017 at 11:39 pm
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.
December 18, 2017 at 3:45 am
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,
CASE
WHEN 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 TEST
ORDER BY FinYear
...
December 18, 2017 at 1:19 pm
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, <datefield>)
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 FromDate
INNER JOIN Test_Corrected ToDate
ON FromDate.fy_month_start < ToDate.fy_month_start
ORDER BY FromDate.fy_month_start, ToDate.fy_month_start
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 18, 2017 at 2:30 pm
raghuldrag - 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 yearkindly give me your suggestion
Considering 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 Moden
Change is inevitable... Change for the better is not.
December 18, 2017 at 9:12 pm
drew.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, <datefield>)
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 FromDate
INNER JOIN Test_Corrected ToDate
ON FromDate.fy_month_start < ToDate.fy_month_start
ORDER BY FromDate.fy_month_start, ToDate.fy_month_startDrew
My database is Ms sql 2000 so this with clause wont support
December 18, 2017 at 9:22 pm
Jeff Moden - Monday, December 18, 2017 2:30 PMraghuldrag - 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 yearkindly give me your suggestion
Considering 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 year
so my desired output is
AccYear Financial Year count
2001-2002 1
2002-2003 2
2003-2004 3
December 18, 2017 at 11:26 pm
raghuldrag - Monday, December 18, 2017 9:22 PMJeff Moden - Monday, December 18, 2017 2:30 PMraghuldrag - 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 yearkindly give me your suggestion
Considering 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 year
so my desired output isAccYear Financial Year count
2001-2002 1
2002-2003 2
2003-2004 3
Not 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?
...
December 19, 2017 at 9:23 am
raghuldrag - Monday, December 18, 2017 9:12 PMdrew.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, <datefield>)
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 FromDate
INNER JOIN Test_Corrected ToDate
ON FromDate.fy_month_start < ToDate.fy_month_start
ORDER BY FromDate.fy_month_start, ToDate.fy_month_startDrew
My database is Ms sql 2000 so this with clause wont support
Yes, 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 Allen
Business Intelligence Analyst
Philadelphia, PA
December 19, 2017 at 9:26 am
HappyGeek - Monday, December 18, 2017 11:26 PMraghuldrag - Monday, December 18, 2017 9:22 PMJeff Moden - Monday, December 18, 2017 2:30 PMraghuldrag - 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 yearkindly give me your suggestion
Considering 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 year
so my desired output isAccYear Financial Year count
2001-2002 1
2002-2003 2
2003-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 Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply