Solved

select current quarter in SQL query  based on company fiscal year

Posted on 2013-10-25
3
2,011 Views
Last Modified: 2013-10-25
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
Comment
Question by:sharon2011
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 66

Expert Comment

by:Jim Horn
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

by:
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
    )
 
  
)

Open in new window

0
 

Author Closing Comment

by:sharon2011
ID: 39601520
ged325,

I just made my sql query work beautifully.

Thank you very much
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question