Solved

# select current quarter in SQL query  based on company fiscal year

Posted on 2013-10-25
1,871 Views
Hello, experts,

How do I select the current quarter based on company fiscal year.  I defined the quarters as follow:

CASE
WHEN MONTH(getdate()) BETWEEN 6  AND 8  THEN 'Q1 - '
WHEN MONTH(getdate()) BETWEEN 9  AND 11  THEN 'Q2 - '
WHEN MONTH(getdate()) BETWEEN 12  AND 2  THEN 'Q3 - '
WHEN MONTH(getdate()) BETWEEN 3 AND 5 THEN 'Q4 - '
END as Qtr

I need passing the current Qtr to a query:

Select * from Margin where createdon =current Qtr

Thank you very much in advance.
0
Question by:sharon2011

LVL 65

Expert Comment

ID: 39600406
Dang dang dang dang dang.

I have an article there on Date Fun, Part One:  Build your own SQL calendar table to perform complex date expressions, which walks you through how to handle goofy-riffic calculations involving days by storing them in a table.

The 'Part Two' is how to handle fiscal years, which isn't ready for the world yet.
I take it you're dealing with crop year?

In your case, in what format is the createdon values?  Based on that we can kick out some T-SQL, and I'm guessing it'll be a nested CASE block based on what you already have, that handles the year component.
0

LVL 40

Accepted Solution

Kyle Abrahams earned 500 total points
ID: 39600437
does it need to be a query:

Most efficient way to do it would be to create a parameter:

@startMonth;

select
@startMonth =
WHEN MONTH(getdate()) BETWEEN 6  AND 8  THEN 6
WHEN MONTH(getdate()) BETWEEN 9  AND 11 THEN 9
WHEN MONTH(getdate()) BETWEEN 12  AND 2  THEN 12
WHEN MONTH(getdate()) BETWEEN 3  AND 5  THEN 3

``````select * from Margin
where
-- normal cases
(
month(createdon) between @startmonth and @startmonth + 2 and @startmonth != 12
and
year(createdon) = year(getdate)
)
OR
(
--year wrapping
@startmonth = 12
AND
(
-- It was created in decemeber and the year is based on where I'm at currently
month(createdon) = 12 and year(createdon) =  case when month(getdate()) in (1,2) then year(getdate()) - 1 else year(getdate()) end
Or
-- it was created in Jan/Feb and the year is based on where I'm at currently
month(createdon) in (1,2) and year(createdon) = case when month(getdate()) in (1,2) then year(getdate()) else year(getdate()) + 1 end
)

)
``````
0

Author Closing Comment

ID: 39601520
ged325,

I just made my sql query work beautifully.

Thank you very much
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question