?
Solved

select current quarter in SQL query  based on company fiscal year

Posted on 2013-10-25
3
Medium Priority
?
2,251 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 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 41

Accepted Solution

by:
Kyle Abrahams earned 2000 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 Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

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 …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

850 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