SQL Fiscal Year Quarters - SQL Server Forums

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

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Fiscal Year Quarters

Author

Topic

westerncj
Starting Member

3 Posts

Posted - 2013-01-08 : 13:27:36

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 @Flag AS CHAR(20)
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'
                    END

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'
                    END

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-08 : 14:15:30

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'

SELECT 
QtrStart = DATEADD(quarter,qtr-1,
					 DATEADD(q,DATEDIFF(quarter,0,@date),'18990501') 
				  )
,QtrEnd = DATEADD(quarter,qtr,
					 DATEADD(q,DATEDIFF(quarter,0,@date),'18990501') 
				  )
,qtr
FROM 
(
   VALUES (1),(2),(3),(4)
 )q(qtr)

Jim

Everyday I learn something that somebody else already knew

sportsguy
Starting Member

39 Posts

Posted - 2013-01-09 : 18:02:01

Create a calendar table, this is mine:


CREATE TABLE [dbo].[FISCAL_CALENDAR](
	[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
) ON [DATA]

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. . .

sportsguy

MS Access 20 years, SQL hack

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources