Solved

select current quarter in SQL query  based on company fiscal year

Posted on 2013-10-25
3
1,945 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
3 Comments
 
LVL 65

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Required 3 118
Need help with a Stored Proc on Sql Server 2012 4 35
find SQL job run average duration 24 57
Assigning Database Principals to Database Roles 3 19
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 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