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!

Register Log in
  • 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

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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
Click to expand...

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!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SteveMe
  • Locked
  • Question
DTS Oracle Date defaulting to 20xx when it shouldn't
Replies
1
Views
32
SQLBill
SQLBill

Part and Inventory Search

Sponsor

Back
Top