×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Q4/20xx-20xy

Q4/20xx-20xy

Q4/20xx-20xy

(OP)
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 

RE: Q4/20xx-20xy

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:

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   ...           ....        ....     .....
.... 

---- Andy

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

RE: Q4/20xx-20xy

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

RE: Q4/20xx-20xy

Just a 'shot in the dark'...

CODE

'Q' + (
  CASE WHEN Datepart(q, CompletionDate) = 1 THEN 
    CAST(4 AS VARCHAR) + '/' + 
    CAST(Datepart(yyyy, DATEADD(yyyy, -1, 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 CompletionDate 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

RE: Q4/20xx-20xy

Quote (dhookom)

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Q4/20xx-20xy

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

RE: Q4/20xx-20xy

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

CODE

DECLARE @MyDate DATE

set @MyDate = DATEADD(MM, -3, CAST('2/14/2023' 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:
quarteryear
Q4/2022-2023

Assuming your 'offset' is -3 months from 'calendar' year

---- Andy

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close