Processing Data by Fiscal Year

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)

Processing Data by Fiscal Year

Author

Topic

LaurieCox

158 Posts

Posted - 2013-03-27 : 13:41:35

I have this table:


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

There are two pieces of data that I need to calculate for each Client for a given Fiscal Year (10/1 thru 9/30):

LastDateOfService

RequiredType

LastDateOfService should be self explanatory.

RequiredType can be 'All' or 'Minimal' depending on what type of services (determined by ScProcedureCode) a client has had in a given Fiscal Year:

If the Client has only had services like the following:

'H0002%'
'H0025%'
'H0031%'
'T1023%'
'T2011%'
'%HF%'

Then the RequiredType is Minimal else the RequiredType is All.

Given the TestData (at end of post) my expected results are:


ClientId    FiscalYear  RequiredType LastDateOfService
----------- ----------- -----------  -----------------------
25234       2013        Minimal      2013-02-05 09:45:00.000
56021       2013        All          2013-02-04 09:00:00.000
56110       2013        All          2013-03-12 14:00:00.000
57621       2013        All          2013-03-08 14:50:00.000
66697       2013        Minimal      2013-01-07 09:00:00.000
25234       2012        All          2012-06-25 00:30:00.000
56021       2012        All          2012-09-12 08:10:00.000
57621       2012        Minimal      2012-05-10 00:00:00.000
66697       2012        All          2011-11-15 00:00:00.000

I have actually solved this but I am wondering if there is a better way to do it than using the While loop.

Here is my current solution:


Create Table #Results
 (  ClientId int,
    FiscalYear int,
    RequiredType varchar(10),
    LastDateOfService datetime) 

declare @FiscalYear int = case when month(getdate()) < 10 
                               then year(getdate())
                               else year(getdate()) + 1 
                          end                         
declare @FiscalYearStart datetime 
declare @FiscalYearEnd datetime 

set @FiscalYear = @FiscalYear + 1 
--So that we can decrement at beginning of while loop
while @@ROWCOUNT > 0
begin
	set @FiscalYear = @FiscalYear - 1
	set @FiscalYearStart = '10/1/' + convert(varchar(4),@FiscalYear - 1)
	set @FiscalYearEnd = '9/30/' + convert(varchar(4),@FiscalYear) + ' 23:59'

	insert into #Results (ClientId,FiscalYear,RequiredType)
	select distinct s1.ScClientId as ClientId
		 , @FiscalYear as FiscalYear
		 , case when s2.ScClientId Is null then 'Minimal' else 'All' end as RequiredType
	 from  #TestData s1 left join
			(select ScClientId
			  from #TestData s 
			 where s.ScProcedureCode not like 'H0002%'
			   and s.ScProcedureCode not like 'H0025%'
			   and s.ScProcedureCode not like 'H0031%'
			   and s.ScProcedureCode not like 'T1023%'
			   and s.ScProcedureCode not like 'T2011%'
			   and s.ScProcedureCode not like '%HF%'  
			   and s.DateOfService between  @FiscalYearStart and @FiscalYearEnd
			  ) s2 on s2.ScClientId = s1.ScClientId
	where s1.DateOfService between  @FiscalYearStart and @FiscalYearEnd 
end 
-- I had calculated the LastDateOfService in the loop by addding this to the select:
--    , max(s1.DateOfService) over (partition by s1.ScClientId) as LastDateOfService
--But it caused the query to run for 1/2 hour where doing it this way reduces the time
--to under 5 minutes.
update res set LastDateOfService = x.LastDateOfService
  from #Results res cross apply
    (select max(s.DateOfService) as LastDateOfService
       from #TestData s
      where s.DateOfService between '10/1/' + convert(varchar(4),res.FiscalYear - 1) 
                                and '9/30/' + convert(varchar(4),res.FiscalYear) + ' 23:59'
        and s.ScClientId = res.ClientId
      group by s.ScClientId
      ) x

Here is the testdata:


Insert into #TestData
Select 'H0002',25234,'2013-02-05 09:45:00.000' union all
Select 'T1023',25234,'2012-12-01 11:00:00.000' union all
Select 'T1023',25234,'2012-06-25 00:30:00.000' union all
Select 'H0018',25234,'2012-06-25 00:00:00.000' union all
Select 'M0064',56021,'2013-02-04 09:00:00.000' union all
Select '90862',56021,'2012-11-12 13:10:00.000' union all
Select '90801AF',56021,'2012-09-12 08:10:00.000' union all
Select 'H0002',56021,'2012-04-20 00:00:00.000' union all
Select 'H2011',56110,'2013-03-12 14:00:00.000' union all
Select 'T1023',56110,'2013-02-21 05:15:00.000' union all
Select 'H0002',56110,'2013-02-05 16:55:00.000' union all
Select 'H0002',56110,'2013-02-05 10:30:00.000' union all
Select 'T1023',56110,'2013-02-04 01:45:00.000' union all
Select 'H0002HF',57621,'2013-03-08 14:50:00.000' union all
Select 'H0032',57621,'2012-12-28 12:15:00.000' union all
Select '90862',57621,'2012-12-28 11:15:00.000' union all
Select 'H2011',57621,'2012-11-27 09:30:00.000' union all
Select '90862',57621,'2012-11-27 08:47:00.000' union all
Select 'H0002',57621,'2012-05-10 00:00:00.000' union all
Select 'H0002',66697,'2013-01-07 09:00:00.000' union all
Select '90806',66697,'2011-11-15 00:00:00.000' union all
Select '90806',66697,'2011-11-08 00:00:00.000' union all
Select '90862',66697,'2011-11-03 14:30:00.000' union all
Select '90806',66697,'2011-11-01 00:00:00.000' union all
Select '90806',66697,'2011-10-18 00:00:00.000' union all
Select '90862',66697,'2011-10-06 15:30:00.000' union all
Select '90806',66697,'2011-10-05 00:00:00.000'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-27 : 16:43:42

Try this

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	dbo.TestData
)
SELECT		ClientID,
		FiscalYear,
		CASE
			WHEN SUM(SpecialProcedures) = COUNT(*) THEN 'Minimal'
			ELSE 'All'
		END AS RequiredType,
		MAX(DateOfService) AS LastDateOfService
FROM		cteSource
GROUP BY	ClientID,
		FiscalYear;

N 56°04'39.26"
E 12°55'05.63"

LaurieCox

158 Posts

Posted - 2013-03-28 : 09:24:45

Hi SwePeso,

That is perfect. I still have to check the results from actual data but besides having more columns than my test data the real data isn't much different. I am pretty sure I covered all test cases in my test data (e.g. different required types for fiscal years, only data for one fiscal year, etc).

What is real cool about your solution is time. My solution when run against 1227383 rows was taking around 5 minutes (give or take a couple of minutes). Yours runs in about 5 seconds.

I looked at common table expressions awhile back but never really figured them out. Can you point me to any good articles about how they work?

Though it isn't just understanding how the tool works (e.g. cte, cross apply, partitions) but being able to see how to apply the tools to a given problem. Not sure if there are any articles that help with that.

Anyway thanks again,

Laurie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-28 : 09:59:22

60 times faster? Ok, I guess.
A cte is nothing more than a statement level view. A very local view...

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources