Q4/20xx-20xy

[damipera]

Hi guys, I've got the SQL code below. When the CompletionDate is 25/12/2022 the output is Q3/2022-2023, which is what I want.

But when the CompletionDate is 14/02/2023, the output is Q4/2023-2024. How do I make it so that the output is Q4/2022-2023? Thanks for any insight guys.

'Q' + (
  CASE WHEN Datepart(q, CompletionDate) = 1 THEN CAST(4 AS VARCHAR) + '/' + CAST(
    Datepart(yyyy, CompletionDate) AS Varchar
  ) + '-' + CAST(
    Datepart(
      yyyy, 
      DATEADD(yyyy, 1, CompletionDate)
    ) AS Varchar
  ) WHEN Datepart(q, CompletionDate) = 2 THEN CAST(1 AS VARCHAR) + '/' + CAST (
    Datepart(yyyy, CompletionDate) AS Varchar
  ) + '-' + CAST(
    Datepart(
      yyyy, 
      DATEADD(yyyy, 1, CompletionDate)
    ) AS Varchar
  ) WHEN Datepart(q, CompletionDate) = 3 THEN CAST(2 AS VARCHAR) + '/' + CAST(
    Datepart(yyyy, CompletionDate) AS Varchar
  ) + '-' + CAST (
    Datepart(
      yyyy, 
      DATEADD(yyyy, 1, CompletionDate)
    ) AS Varchar
  ) WHEN Datepart(q, CompletionDate) = 4 THEN CAST(3 AS VARCHAR) + '/' + CAST(
    Datepart(yyyy, CompletionDate) AS Varchar
  ) + '-' + CAST(
    Datepart(
      yyyy, 
      DATEADD(yyyy, 1, CompletionDate)
    ) AS Varchar
  ) END
) AS quarteryear

 

[Andrzejek]

Looks to me you are dealing with some Fiscal (?) Years that do not start on Jan 1 and end on Dec 31

If so, I would have a small table:

[PRE]
FiscalYears
ID START_DATE END_DATE QUARTER FY
1 7/1/2022 9/30/2022 1 2022-2023
2 10/1/2022 1/31/2023 2 2022-2023
3 2/1/2023 3/31/2023 3 2022-2023
4 4/1/2023 6/30/2023 4 2022-2023
5 ... .... .... .....
....[/PRE]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

First, I would expect you to provide the specifications prior to the code so we could understand the logic. In your case (without looking at the code) I would probably find the quarter of a date minus 6 months. Maybe even store a value of -6 in a table so it's easy to change without modify code when your switch to a regular calendar year.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Just a 'shot in the dark'...

'Q' + (
  CASE WHEN Datepart(q, CompletionDate) = 1 THEN 
    CAST(4 AS VARCHAR) + '/' + 
    CAST(Datepart(yyyy, DATEADD(yyyy, [highlight #FCE94F]-1[/highlight], CompletionDate)) AS Varchar + '-' + 
    CAST(Datepart(yyyy, CompletionDate) AS Varchar )
...

Another guess here - if you subtract 3 months (3 months here is a guess) from your [tt]CompletionDate[/tt] you should get its 'Quarter' and 'first year'. And you can just add 1 to 'first year' to get the 'second year'.
No complicated SQL required.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Maybe even store a value of -6 in a table so it's easy to change without modify code when your switch to a regular calendar year.

One of the best tips you could ever get IMNSHO!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[dhookom]

Thanks Skip. That's high praise coming from you. I actually implemented something similar about 25 years ago in a factory with three work shifts and needing to change the shift that began the production day.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Kind of fun...
The whole long SQL may just be:

DECLARE @MyDate DATE

set @MyDate = DATEADD(MM, [highlight #FCE94F]-3[/highlight], CAST('[red]2/14/2023[/red]' AS DATE));

SELECT 'Q' + CAST(Datepart(Q, @MyDate) AS Varchar) + '/' + 
    CAST(Datepart(yyyy, @MyDate) AS Varchar)       + '-' +
    CAST(Datepart(yyyy, @MyDate) + 1 AS Varchar)
    AS quarteryear

You get:[tt]
quarteryear
Q4/2022-2023
[/tt]
Assuming your 'offset' is -3 months from 'calendar' year

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson