Solved

select current quarter in SQL query  based on company fiscal year

Posted on 2013-10-25
3
1,871 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Get Duration of last Status Update 4 35
Help with t-sql finding "latest" record in table 10 36
Help Required 2 33
SQL Server 2012 r2 - Varible Table 3 24
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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