Date Query

SQL 2008

I have a simple query a them moment

select RDate, month(RDAte) Mth
from Table

This gives me output like the examples below

RDate                               Mth
01/05/2016 13:00:02           5
31/01/2016 14:25:26           1

The firm  am working for have a financial year from May to April of the next year.

I want to add what Financial Year the date is in to my quey and the month number in that financial year

Using the example from above, this would look like

RDate                                 Mth          FY               FYMth
01/05/2016 13:00:02              5          16/17               1
31/01/2016 14:25:26              1           15/16               9

Thanks
Mark WilsonBI DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
Try

declare @date0 as datetime
select @date0=cast('01/05/2016 13:00:02' as datetime) 
select month(@date0),case when month(@date0)>4 then right(cast(year(@date0) as varchar),2)+'/'+right(cast(year(dateadd(yy,1,@date0)) as varchar),2) else right(cast(year(dateadd(yy,-1,@date0)) as varchar),2)+'/'+right(cast(year(@date0) as varchar),2) end as FY, case when month(@date0)>4 then month(@date0)-4 else month(@date0)-4+12 end as FYMonth

select @date0=cast('6/01/2016 18:00:02' as datetime) 
select month(@date0),case when month(@date0)>4 then right(cast(year(@date0) as varchar),2)+'/'+right(cast(year(dateadd(yy,1,@date0)) as varchar),2) else right(cast(year(dateadd(yy,-1,@date0)) as varchar),2)+'/'+right(cast(year(@date0) as varchar),2) end as FY, case when month(@date0)>4 then month(@date0)-4 else month(@date0)-4+12 end as FYMonth

Open in new window

0
Olaf DoschkeSoftware DeveloperCommented:
Modula (%) operation helps here:
Select (5+8)%12; -- is 1
Select (1+8)%12; -- is 9

Open in new window


Also, if the fiscal year month is higher than the real month (which happens for Jan to Apr) your fiscal year is lastyear/thisyear, otherwise thisyear/nextyear.

Putting it together:
Declare @data as table (RDate datetime)

--your sample data (thanks for specifying!)
Insert Into @data values ('20160501 13:00:02'),('20160131 14:25:26')

--SQL2012 or newer
Select  RDate 
, Month(RDate) as Mth 
, Case When (Month(RDate)+8)%12 > Month(RDate) 
  Then Format(DateAdd(yy,-1,RDate),'yy')+'/'+Format(RDate,'yy')
  Else Format(RDate,'yy')+'/'+Format(DateAdd(yy,1,RDate),'yy')
  End as FY
,(Month(RDate)+8)%12 as FYMth From @data

--SQL2008R2 or older
Select  RDate 
, datepart(mm,RDate) as Mth 
, Case When (DatePart(mm,RDate)+8)%12 > DatePart(mm,RDate) 
  Then Right(DateName(yy,DateAdd(yy,-1,RDate)),2)+'/'+ Right(DateName(yy,RDate),2)
  Else Right(DateName(yy,RDate),2)+'/'+ Right(DateName(yy,DateAdd(yy,1,RDate)),2)
  End as FY
,(DatePart(mm,RDate)+8)%12 as FYMth From @data

Open in new window


Notice the comments. To me this is giving your expected results (thanks for specifying!).

Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WilsonBI DeveloperAuthor Commented:
Olaf - just tried running the query above

I have attached some of the results

For any months with April in the FYMonth = 0 instead of 12

Also for dates

2016-01-01 00:00:00.000
2016-02-01 00:00:00.000
2016-03-01 00:00:00.000
2016-04-01 00:00:00.000

The FY is 16/17 - should be 15/16 - it will be 16/17 from the 1st May onwards - FY May to April the next year

Thanks for your help
Example_Data.PNG
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HuaMin ChenProblem resolverCommented:
Hi Halif,
Did you try my example in above?

Now please try
select rdate,month(rdate) Mth,case when month(rdate)>4 then right(cast(year(rdate) as varchar),2)+'/'+right(cast(year(dateadd(yy,1,rdate)) as varchar),2) else right(cast(year(dateadd(yy,-1,rdate)) as varchar),2)+'/'+right(cast(year(rdate) as varchar),2) end as FY, case when month(rdate)>4 then month(rdate)-4 else month(rdate)-4+12 end as FYMonth
from table

Open in new window

0
Mark WilsonBI DeveloperAuthor Commented:
Sorry didn't see the comment above - will try it now - Thanks for the help
0
Olaf DoschkeSoftware DeveloperCommented:
It would help much more, if you extend the sample code. I just added a date for every month.

You're right about modulo resulting in 0 to 11 instead of 1 to 12, that just needs a minor adjustment:
Select ((5+7)%12)+1; -- is 1
Select ((1+7)%12)+1; -- is 9

Open in new window

The whole result must be shifted by 1, therefore the offset must not be  8, but 7.

I don't know what you did with my FY formula, but it works as needed for me, see here:
fiscal yearEven the previous code was only wrong about April 2016 in regard of FY, just because of the modulo error.
fiscalerr.PNGSo you must have copied or adapted the code wrongly, if you got 16/17 for January to March, too.

Full code:
Declare @data as table (RDate datetime)

--your sample data (thanks for specifying!)
Insert Into @data values 
('20150801 00:00:00'),
('20150901 00:00:00'),
('20151001 00:00:00'),
('20151101 00:00:00'),
('20151201 00:00:00'),
('20160101 00:00:00'),
('20160201 00:00:00'),
('20160301 00:00:00'),
('20160401 00:00:00'),
('20160501 00:00:00'),
('20160601 00:00:00'),
('20160701 00:00:00')

--SQL2012 or newer
Select  RDate 
, Month(RDate) as Mth 
, Case When ((Month(RDate)+7)%12)+1 > Month(RDate) 
  Then Format(DateAdd(yy,-1,RDate),'yy')+'/'+Format(RDate,'yy')
  Else Format(RDate,'yy')+'/'+Format(DateAdd(yy,1,RDate),'yy')
  End as FY
,((Month(RDate)+7)%12)+1 as FYMth From @data

--SQL2008R2 or older
Select  RDate 
, datepart(mm,RDate) as Mth 
, Case When ((DatePart(mm,RDate)+7)%12)+1 > DatePart(mm,RDate) 
  Then Right(DateName(yy,DateAdd(yy,-1,RDate)),2)+'/'+ Right(DateName(yy,RDate),2)
  Else Right(DateName(yy,RDate),2)+'/'+ Right(DateName(yy,DateAdd(yy,1,RDate)),2)
  End as FY
,((DatePart(mm,RDate)+7)%12)+1 as FYMth From @data

Open in new window


Bye, Olaf.
0
Scott PletcherSenior DBACommented:
Another possible method:

Declare @data as table (RDate datetime)

--your sample data (thanks for specifying!)
Insert Into @data values ('20160501 13:00:02'),('20160131 14:25:26')

--all versions of SQL2005+:
SELECT RDate, month(RDAte) Mth,
    CAST(fy - 2000 AS varchar(2)) + '/' + CAST(fy + 1 - 2000 AS varchar(2)) AS FY,
    ABS(DATEDIFF(MONTH, CAST(fy AS varchar(4)) + '0501', RDate)) + 1 AS FYMth
FROM @data
CROSS APPLY (
    SELECT YEAR(RDate) - CASE WHEN MONTH(RDate) < 5 THEN 1 ELSE 0 END AS fy
) AS assign_alias_names
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.