Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004

    Question Unanswered: 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'


    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'



    expected output....

    Data Points______ Experiment Year
    32_____________ 2001
    102____________ 2002
    .... ....
    Last edited by sabushadi; 01-06-04 at 11:58.

  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    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

  3. #3
    Join Date
    Jan 2003
    Provided Answers: 11
    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?

  4. #4
    Join Date
    Jan 2004

    Re: dynamic sql to loop over fiscal years

    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.
    Last edited by sabushadi; 01-06-04 at 13:29.

  5. #5
    Join Date
    Jun 2003
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts