Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Fiscal Year Quarters
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

3 Posts

Posted - 01/08/2013 :  13:27:36  Show Profile  Reply with Quote
I'm trying to write a sql statement that will take today's date and compare with an audited date to see if they missed an audit.

Our audit cycles are broken down into these 4 quarters.

Qtr 1 – May, Jun, Jul
Qtr 2 – Aug, Sep, Oct
Qtr 3 – Nov, Dec, Jan
Qtr 4 – Feb, Mar, Apr

It makes it difficult because of the year the audit was done. They may have done it last May but it hasn't been done this May yet. Also I'm not sure how to handle the 3rd quarter because January is included.

Here's what I've been working on so far. I'm trying to figure out the correct logic. I need to assign all the months to their respect quarters then evalute the year somehow with the current date.

DECLARE @AuditDate CHAR(20)
DECLARE @Quarter CHAR(20)
DECLARE @CurrentDate CHAR(20)

SET @AuditDate = '01/08/12'
SET @Quarter = '3RD QUARTER'
SET @CurrentDate = '11/08/12'

SELECT  AuditDate = CASE WHEN MONTH(@AuditDate) <> MONTH(@CurrentDate)
                         THEN 'MISSED'
                         ELSE 'AUDITED'

SELECT  AuditDate = CASE WHEN MONTH(@AuditDate) IN ( 5, 6, 7 )
                         THEN '1ST QUARTER'
                         WHEN MONTH(@AuditDate) IN ( 8, 9, 10 )
                         THEN '2ND QUARTER'
                         WHEN MONTH(@AuditDate) IN ( 11, 12, 1 )
                         THEN '3RD QUARTER'
                         WHEN MONTH(@AuditDate) IN ( 2, 3, 4 )
                         THEN '4TH QUARTER'

Flowing Fount of Yak Knowledge

2875 Posts

Posted - 01/08/2013 :  14:15:30  Show Profile  Reply with Quote
This may help. It will give you the start and end of each of your fiscal qtrs base on a given date. I'd make a CalendarTable that stores all these possibilities
DECLARE @DATE DATE = '20121201'

QtrStart = DATEADD(quarter,qtr-1,
,QtrEnd = DATEADD(quarter,qtr,
   VALUES (1),(2),(3),(4)


Everyday I learn something that somebody else already knew
Go to Top of Page

Starting Member

39 Posts

Posted - 01/09/2013 :  18:02:01  Show Profile  Reply with Quote
Create a calendar table, this is mine:

	[YYYYMM] [int] NULL,
	[FY] [smallint] NULL,
	[strMonth] [nvarchar](20) NULL,
	[strShort] [nvarchar](6) NULL,
	[CY] [smallint] NULL,
	[YYYYQQ] [int] NULL,
	[strYYYYQQ] [nvarchar](8) NULL,
	[strQuarter] [nvarchar](50) NULL,
	[Start_Date] [datetime] NULL,
	[End_Date] [datetime] NULL,
	[Cal_End_Date] [datetime] NULL,
	[Nbr_Weeks] [tinyint] NULL,
	[Productive_Days] [tinyint] NULL,
	[Total_Days] [tinyint] NULL,
	[BPM_Month] [nvarchar](3) NULL,
	[BPM_Year] [nvarchar](4) NULL,
	[QtrEndMonth] [bit] NOT NULL,
	[strYYYYMM] [nchar](6) NULL

The YYYYMM is the fiscal year fiscal period. my company's fiscal calendar starts in October, so January would be 201304, etc. The month is either 4 weeks or 5 weeks long, ending Fridays at 11:59:59 PM in the end date column, and on Saturday 00:00:00 in the start date column. quarters are 201301, with all the attributes seen above.

If the audit was between the start date and the end date, then it was done on time. . .


MS Access 20 years, SQL hack
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000