Fiscal Year With Mutable Boundaries

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

SQL Server 2008 Forums

Transact-SQL (2008)

Fiscal Year With Mutable Boundaries

Author

Topic

LaurieCox

158 Posts

Posted - 2013-08-21 : 13:07:11

Edit: because there was an error in my solution. Missed changing one of the GETDATE() to @CurrentDate for testing purposes

This is a continuation of this topic. The original topic looked at service records divided up by fiscal year* for a given client and determined if they fell into one of two required types (all or minimal) depending on the type of services. Please read the op in that topic for a complete description of the original problem.

*Our fiscal year runs from Oct 1st thru Sept 30th. So 2012/10/01 thru 2013/09/30 is in fiscal year 2013.

SwePeso came back with a perfect solution that ran about 60 times faster than mine. But now a wrinkle has been added to the way fiscal year is calculated.

The change I need to make is that we want to look at least 90 days of services to determine required status for a given Fiscal Year.

When we switch to fiscal year 2014 on 2013-10-01 the current code will only look at services from October on (of which there will most likely be none).

So (for example) if the current date was 2013-10-20 I would count any services that happened between 2013-07-22 and 2013-09-30 (inclusive) both toward fiscal year 2013 and fiscal year 2014 in determining required status.

I have come up with a solution (see below) that I am currently testing but I would appreciate some constructive feedback or suggestions of a better way to do it.

My current solution (see end of post for test data and expected results depending on the value of @CurrentDate):


drop table TestResultsFiscalYear

declare @CurrentDate date
Select @CurrentDate = '2013/10/21' 
select @CurrentDate,DATEADD(dd,-90,@CurrentDate)

;WITH cteSource(ClientID, DateOfService, SpecialProcedures, FiscalYear)
AS (
	SELECT	ScClientID,
		DateOfService,
		CASE
			WHEN ScProcedureCode LIKE 'H0002%' THEN 1
			WHEN ScProcedureCode LIKE 'H0025%' THEN 1
			WHEN ScProcedureCode LIKE 'H0031%' THEN 1
			WHEN ScProcedureCode LIKE 'T1023%' THEN 1
			WHEN ScProcedureCode LIKE 'T2011%' THEN 1
			WHEN ScProcedureCode LIKE '%HF%' THEN 1
			ELSE 0
		END AS SpecialProcedures,
		DATEPART(YEAR, DATEADD(MONTH, 3, DateOfService)) AS FiscalYear
	FROM	TestDataFiscalYear
	union
	select ScClientid
	     , DateOfService
		 , CASE
			WHEN ScProcedureCode LIKE 'H0002%' THEN 1
			WHEN ScProcedureCode LIKE 'H0025%' THEN 1
			WHEN ScProcedureCode LIKE 'H0031%' THEN 1
			WHEN ScProcedureCode LIKE 'T1023%' THEN 1
			WHEN ScProcedureCode LIKE 'T2011%' THEN 1
			WHEN ScProcedureCode LIKE '%HF%' THEN 1
			ELSE 0
		   END AS SpecialProcedures
		 --, DATEPART(Year,GETDATE()) + 1 as FiscalYear  though this will be correct when in production
		 , DATEPART(Year,@CurrentDate) + 1 AS FiscalYear
	FROM	TestDataFiscalYear
    where datepart(month,@CurrentDate) in (10,11,12)
      and DateOfService >= DATEADD(dd,-90,@CurrentDate)
)
SELECT	ClientID,
		FiscalYear,
		CASE
			WHEN SUM(SpecialProcedures) = COUNT(*) THEN 'Minimal'
			ELSE 'All'
		END AS RequiredType,
		MAX(DateOfService) AS LastDateOfService
into TestResultsFiscalYear
FROM		cteSource
GROUP BY	ClientID,
		FiscalYear;
		
select @CurrentDate as CurrentDate,* from TestResultsFiscalYear	 order by ClientID, FiscalYear

Test table with test data and expected results (plug CurrentDate from result into @CurrentDate above to get results show.


Create Table TestDataFiscalYear 
 (ScProcedureCode varchar(10),
  ScClientId int,
  DateOfService datetime)

Data Set 1:
delete from TestDataFiscalYear
insert TestDataFiscalYear
select 'T1023QJ', 65006, '2013-06-10 00:00:00.000' union all
select 'H0031', 65006, '2013-06-12 00:00:00.000' union all
select 'T1023QJ', 65006, '2013-06-30 00:00:00.000' union all
select 'T1023QJ', 65006, '2013-07-07 00:00:00.000' union all
select 'T1023QJ', 65006, '2013-07-29 00:00:00.000' union all
select '90801AF', 65006, '2013-08-01 00:00:00.000' union all
select 'H0002QJ', 65006, '2013-08-04 00:00:00.000' union all
select 'H0031', 65006, '2013-08-05 00:00:00.000' union all
select 'T1023QJ', 65006, '2013-08-14 00:00:00.000' union all
select 'T1023QJ', 65006, '2013-09-15 00:00:00.000' union all
select 'H0031', 65006, '2013-09-21 00:00:00.000' union all
select 'T1023QJ', 65006, '2013-10-07 00:00:00.000' union all
select 'H0031', 65006, '2013-10-24 00:00:00.000'

Results:

CurrentDate ClientID    FiscalYear  RequiredType LastDateOfService
----------- ----------- ----------- ------------ -----------------------
2013-10-20  65006       2013        All          2013-09-21 00:00:00.000
2013-10-20  65006       2014        All          2013-10-24 00:00:00.000


CurrentDate ClientID    FiscalYear  RequiredType LastDateOfService
----------- ----------- ----------- ------------ -----------------------
2013-10-31  65006       2013        All          2013-09-21 00:00:00.000
2013-10-31  65006       2014        Minimal      2013-10-24 00:00:00.000

Data Set 2
delete from TestDataFiscalYear
insert TestDataFiscalYear
select 'T1023QJ', 65006, '2013-06-10 00:00:00.000' union all
select 'H0031', 65006, '2013-06-12 00:00:00.000' union all
select 'T1023QJ', 65006, '2013-06-30 00:00:00.000' union all
select 'T1023QJ', 65006, '2013-07-07 00:00:00.000' union all
select 'T1023QJ', 65006, '2013-07-22 00:00:00.000'

Results:

CurrentDate ClientID    FiscalYear  RequiredType LastDateOfService
----------- ----------- ----------- ------------ -----------------------
2013-10-20  65006       2013        Minimal      2013-07-22 00:00:00.000
2013-10-20  65006       2014        Minimal      2013-07-22 00:00:00.000

The last service happened just within the 90 day window so we still want to have a record for this client/2014 fiscal year


CurrentDate ClientID    FiscalYear  RequiredType LastDateOfService
----------- ----------- ----------- ------------ -----------------------
2013-10-21  65006       2013        Minimal      2013-07-22 00:00:00.000

The last service has now happened more than 90 days ago in a different fiscal year therefore we do not even want a record for this client/2014 fiscal year.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources