SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to calculate finacial year in mssql 2000


How to calculate finacial year in mssql 2000

Author
Message
raghuldrag
raghuldrag
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2432 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
twin.devil
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18643 Visits: 2940
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
HappyGeek
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12761 Visits: 6342
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



...
drew.allen
drew.allen
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56532 Visits: 14834
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>Wink
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)

Group: General Forum Members
Points: 788957 Visits: 45931
raghuldrag - Sunday, December 17, 2017 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


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

RBAR 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 problems
How to post performance problems
Forum FAQs
raghuldrag
raghuldrag
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2432 Visits: 573
drew.allen - Monday, December 18, 2017 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>Wink
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


My database is Ms sql 2000 so this with clause wont support
raghuldrag
raghuldrag
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2432 Visits: 573
Jeff Moden - Monday, December 18, 2017 2:30 PM
raghuldrag - Sunday, December 17, 2017 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


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
HappyGeek
HappyGeek
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12761 Visits: 6342
raghuldrag - Monday, December 18, 2017 9:22 PM
Jeff Moden - Monday, December 18, 2017 2:30 PM
raghuldrag - Sunday, December 17, 2017 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


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


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?

...
drew.allen
drew.allen
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56532 Visits: 14834
raghuldrag - Monday, December 18, 2017 9:12 PM
drew.allen - Monday, December 18, 2017 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>Wink
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


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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
drew.allen
drew.allen
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56532 Visits: 14834
HappyGeek - Monday, December 18, 2017 11:26 PM
raghuldrag - Monday, December 18, 2017 9:22 PM
Jeff Moden - Monday, December 18, 2017 2:30 PM
raghuldrag - Sunday, December 17, 2017 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


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


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?

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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search