dynamic sql to loop over fiscal years

thanks for reading.

i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?

the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY

problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.

again, thanks for reading ... and any help in advance.

SELECT count(*) as 'Data Points', '2001' as 'Experiment Year'
FROM tbl_experiment_data

WHERE start_date BETWEEN '9/30/2001' AND '10/01/2002'
and completion_date BETWEEN '9/30/2001' AND '10/01/2002'
and status = 'CaseClosed'

UNION

SELECT count(*) as 'Data Points', '2002' as 'Experiment Year'
FROM tbl_experiment_data

WHERE start_date BETWEEN '9/30/2002' AND '10/01/2003'
and completion_date BETWEEN '9/30/2002' AND '10/01/2003'
and status = 'CaseClosed'

UNION

...

expected output...

Data Points______ Experiment Year
32_____________ 2001
102____________ 2002
... ...Create a table called ExperimentYears, populate it with ExperimentYear char(4), YearStart datetime, YearEnd datetime. Then do just one SELECT similar to yours:

SELECT count(*) as [Data Points], ExperimentYear
from tbl_experiment_data ted
inner join ExperimentYears ey
on ted.start_date between ey.YearStart and ey.YearEnd
and ted.completion_date between ey.YearStart and ey.YearEnd
where ted.status = 'CaseClosed'
group by ey.ExperimentYear|||We use a separate calendar table. something like this:

create table FiscalCalendar
(FiscalYear int,
StartDate datetime,
EndDate datetime)

This should reduce your query to something like

select count(*), fc.FiscalYear
from tbl_experiment_data a, FiscalCalendar fc
where a.startdate between fc.startdate and fc.enddate
and a.enddate between fc.startdate and fc.enddate
group by fc.fiscalyear

Been a while since I messed with this, so experiment with this for a bit. As a curiosity, what happens to experiments that start in one fiscal year and end in the next?|||as i'm re-reading my post now i can see that maybe it wouldn't even work as is because i have the count(*) without a 'group by'

still, i hope these sorts of mistakes can be overlooked as i ask for help.

it also occurred to me just now that maybe i could use a user-defined function that returns the value of the year as redefined by the "year" range above.

that way i could rewrite the query like this...

==============
SELECT count(*) as 'Data Points', getFiscalYear(start_date) as 'Experiment Date'
FROM tbl_experiment_data

WHERE DATEPART(YEAR, start_date) = getFiscalYear(start_date)
and DATEPART(YEAR, completion_date) = getFiscalYear(completion_date) and status = 'CaseClosed'

GROUP BY getFiscalYear(start_date)

ORDER BY getFiscalYear(start_date)

==============

any comments? criticisms? other ideas?

thanks again for reading ... and your input

oh, great! i just noticed the responses now too. thank you. i'll try these ideas out.|||SELECT count(*) as 'Data Points',
year(dateadd(d, 92, StartDate))-1 as 'Experiment Year'
FROM tbl_experiment_data
WHERE status = 'CaseClosed'

The year(dateadd(d, 92, StartDate))-1 function returns the experiment year by addint 92 days (Oct +Nov +Dec) and then subtracting 1 from the year. Note that if you just subtracted days you would have to account for leap years.

You will need to decide what to do if an experiment starts in one year and ends in the next. Your original code would skip those instances entirely.