Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Q4/20xx-20xy

Status
Not open for further replies.

damipera

Technical User
Dec 1, 2005
134
GB
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.

Code:
'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
 
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
 
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
 
Just a 'shot in the dark'...
Code:
'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
 
dhookom said:
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
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
 
Kind of fun... :)
The whole long SQL may just be:

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top