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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Processing Data by Fiscal Year
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LaurieCox
Posting Yak Master

USA
131 Posts

Posted - 03/27/2013 :  13:41:35  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 03/27/2013 :  16:43:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 03/27/2013 17:17:57
Go to Top of Page

LaurieCox
Posting Yak Master

USA
131 Posts

Posted - 03/28/2013 :  09:24:45  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 03/28/2013 :  09:59:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
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.08 seconds. Powered By: Snitz Forums 2000