Prior Fiscal YTD and Current Fiscal YTD

I have a customer that wants information pulled from different tables and date ranges pulled and summed in a one page report like this:

                                Clinic Data            OR Data            Hospital Data            ED Data
Date Range            Dep1   Dep2            OR1 OR2            Admits      Inpats      Seen
Last Week Tot
4 Week Avg
12 Week Avg
Cur Fiscal YTD
Prior Fiscal YTD

I'm going to use a data mart, that will be scheduled to run weekly on Sunday, then the report will be scheduled to run weekly on Monday.  I've figured out how to pull the first 3 date ranges.  I'm stuck however on the last 2. Our Fiscal Year is from Jul to Jun.  So Cur Fiscal Year is 7/09 to 6/10 and Prior Fiscal Year is 7/08 to 6/09.  So if the current date is 8/4/09 the for the Current Fiscal YTD it would be 7/1/09 to 8/3/09 and the Prior Fiscal YTD would be 7/1/08 to 8/3/08.  

 I'm thinking of something like this:

CURRENT FISCAL YTD

(CASE WHEN TRUNC(CURRENT_DATE, 'MM') >= 7 THEN
      (TRUNC(PAT_ENC.APPT_TIME)>=TO_DATE('07/01/'||TO_CHAR(TRUNC(CURRENT_DATE,'YYYY')), 'MM/DD/YYYY') AND
       TRUNC(PAT_ENC.APPT_TIME)<=TRUNC(CURRENT_DATE)-1)
      ELSE
      ((TRUNC(PAT_ENC.APPT_TIME)>=TO_DATE('07/01/'||TO_CHAR((TRUNC(CURRENT_DATE,'YYYY')-1)), 'MM/DD/YYYY') AND
       TRUNC(PAT_ENC.APPT_TIME)<=TRUNC(CURRENT_DATE)-1)
END)

PRIOR FISCAL YTD

(CASE WHEN TRUNC(CURRENT_DATE, 'MM') >= 7 THEN
      (TRUNC(PAT_ENC.APPT_TIME)>=TO_DATE('07/01/'||TO_CHAR(TRUNC(CURRENT_DATE,'YYYY')-1), 'MM/DD/YYYY') AND
       TRUNC(PAT_ENC.APPT_TIME)<=TO_DATE(TO_CHAR(TRUNC(TRUNC(CURRENT_DATE)-1, 'MM'))||
                                 TO_CHAR(TRUNC(TRUNC(CURRENT_DATE)-1, 'DD'))||
                                 TO_CHAR(TRUNC(TRUNC(CURRENT_DATE)-1, 'YYYY')), 'MMDDYYYY')
      )
      ELSE
      ((TRUNC(PAT_ENC.APPT_TIME)>=TO_DATE('07/01/'||TO_CHAR((TRUNC(CURRENT_DATE,'YYYY')-2)), 'MM/DD/YYYY') AND
        TRUNC(PAT_ENC.APPT_TIME)<=TO_DATE(TO_CHAR(TRUNC(TRUNC(CURRENT_DATE)-1, 'MM'))||
                                     TO_CHAR(TRUNC(TRUNC(CURRENT_DATE)-1, 'DD'))||
                                  TO_CHAR(TRUNC(TRUNC(CURRENT_DATE)-1, 'YYYY')-1), 'MMDDYYYY')
      )
END)

Am I even on the right track, and how do I add it to the where clause of the query?

Here is the code for the first 3 date ranges for the Clinic Data:


SELECT PAT_ENC.DEPARTMENT_ID, PAT_ENC.PAT_ENC_CSN_ID, 'Last Week' AS TimeLine, 'Clinic Census Data' AS MasterGroup, 1 AS LineCnt
FROM   CLARITY.PAT_ENC PAT_ENC INNER JOIN CLARITY.CLARITY_DEP CLARITY_DEP ON PAT_ENC.DEPARTMENT_ID=CLARITY_DEP.DEPARTMENT_ID
WHERE  PAT_ENC.ENC_TYPE_C<>2101 AND (TRUNC(PAT_ENC.APPT_TIME)>=TRUNC(CURRENT_DATE)-7 AND TRUNC(PAT_ENC.APPT_TIME)<=TRUNC(CURRENT_DATE)-1) AND CLARITY_DEP.RPT_GRP_THIRTEEN_C=1900 AND (PAT_ENC.APPT_STATUS_C=2 OR PAT_ENC.APPT_STATUS_C=6 OR PAT_ENC.APPT_STATUS_C=7)

UNION ALL

SELECT PAT_ENC.DEPARTMENT_ID, PAT_ENC.PAT_ENC_CSN_ID, '4 Week Avg', 'Clinic Census Data', .25
FROM   CLARITY.PAT_ENC PAT_ENC INNER JOIN CLARITY.CLARITY_DEP CLARITY_DEP ON PAT_ENC.DEPARTMENT_ID=CLARITY_DEP.DEPARTMENT_ID
WHERE  PAT_ENC.ENC_TYPE_C<>2101 AND (TRUNC(PAT_ENC.APPT_TIME)>=TRUNC(CURRENT_DATE)-28 AND TRUNC(PAT_ENC.APPT_TIME)<=TRUNC(CURRENT_DATE)-1) AND CLARITY_DEP.RPT_GRP_THIRTEEN_C=1900 AND (PAT_ENC.APPT_STATUS_C=2 OR PAT_ENC.APPT_STATUS_C=6 OR PAT_ENC.APPT_STATUS_C=7)

UNION ALL

SELECT PAT_ENC.DEPARTMENT_ID, PAT_ENC.PAT_ENC_CSN_ID, '12 Week Avg', 'Clinic Census Data', .0833
FROM   CLARITY.PAT_ENC PAT_ENC INNER JOIN CLARITY.CLARITY_DEP CLARITY_DEP ON PAT_ENC.DEPARTMENT_ID=CLARITY_DEP.DEPARTMENT_ID
WHERE  PAT_ENC.ENC_TYPE_C<>2101 AND (TRUNC(PAT_ENC.APPT_TIME)>=TRUNC(CURRENT_DATE)-84 AND TRUNC(PAT_ENC.APPT_TIME)<=TRUNC(CURRENT_DATE)-1) AND CLARITY_DEP.RPT_GRP_THIRTEEN_C=1900 AND (PAT_ENC.APPT_STATUS_C=2 OR PAT_ENC.APPT_STATUS_C=6 OR PAT_ENC.APPT_STATUS_C=7)

UNION ALL

Select allOpen in new window