0

I've been at this question for a bit and I'm currently stuck:

Image Of Question With Example

This is my current attempt:

SQL Code

I only took an image of a small portion of the code since the general idea can be understood within the few case statements. It's turning out to be way too messy and I think theirs an easier way to go about it. An answer isn't necessarily needed but some guidance would help since I'd like to try it out for myself. Thank you!

GMB
  • 216,147
  • 25
  • 84
  • 135
NoobieDev
  • 1
  • Apologies! Thought that by adding the tags I'd be able to get some more help. As for the images, there was a handful of text/code and thought an image would be easier. – NoobieDev Oct 07 '19 at 22:01
  • 1
    It's useless if you use SQL Server, but someone gave you solution in MySQL. Also with text, people can just highlight and copy the code. No one is going to write those code by hand. In the end, you will not receive help from people. – Eric Oct 07 '19 at 22:22

1 Answers1

0

are you still struggling with this one?

    Create function fiscalYearSummary (@OrderDate Date, @StartFiscalYearFromGivenMonth int)
    returns nvarchar(20)as

    begin 
        declare @MonthOfYear int
        declare @Quarter int

        set @MonthOfYear = datepart(MONTH, @OrderDate) 
        set @Quarter =  (((((@MonthOfYear  - (@StartFiscalYearFromGivenMonth  )) + 12) % 12) + 1) / 4) + 1

        return  case @Quarter when 1 then 'Quarter I' when 2 then 'Quarter II' when 3 then 'Quarter III' when 4 then 'Quarter IV' else 'Error' end  
    end

This would be used in a select

select dbo.fiscalYearSummary('20190101',1) -- January with the first month of the fiscal year January.
select dbo.fiscalYearSummary('20190401',4) -- April with the first month of the fiscal year April.
select dbo.fiscalYearSummary('20191231',1) -- December with the first month of the fiscal year January

I'm sure it can be done better, but this should get you started.

I get the Month from the passed in date (1 to 12)
Subtract @StartFiscalYearFromGivenMonth to drag it back by that many months.
Add 12 (a year) to make it positive, mod it with 12 to get it in the range 0 to 11, then add 1 to make that 1 to 12.
I divide the result by 4 to get the quarter - 0 to 3, then add 1 again, into range 1 to 4.
then a simple case returns the quarter.

I hope this helps

Tony Cullen
  • 1