SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

westerncj
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 @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
Flowing Fount of Yak Knowledge

USA
2869 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'

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
Go to Top of Page

sportsguy
Starting Member

USA
38 Posts

Posted - 01/09/2013 :  18:02:01  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000