Please start any new threads on our new site at http://forums.sqlteam.com. 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
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Fiscal Year With Mutable Boundaries
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LaurieCox
Posting Yak Master

USA
158 Posts

Posted - 08/21/2013 :  13:07:11  Show Profile  Reply with Quote

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.

Edited by - LaurieCox on 08/21/2013 14:18:55
  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.11 seconds. Powered By: Snitz Forums 2000