[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1061
  • Last Modified:

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

Open in new window

0
corobv
Asked:
corobv
  • 2
2 Solutions
 
tomcatkevCommented:
First off, you have functions on both left and right arguments in your comparisons.  This means you need to have a function based index on TRUNC(PAT_ENC.APPT_TIME) for these filters to work efficiently.

I would have a different suggestion for

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 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)
AND
-- Suggestion for current Fiscal year
PAT_ENC_APPT_TIME between
add_months(TRUNC(CURRENT_DATE,'YYYY'),(case when to_char(current_date,'MM')>=6 then 6 else -6 end))
and
add_months(TRUNC(CURRENT_DATE,'YYYY'),(case when to_char(current_date,'MM')>=6 then 18 else 6 end))


-- Suggestion for previous Fiscal year
PAT_ENC_APPT_TIME between
add_months(TRUNC(CURRENT_DATE,'YYYY'),(case when to_char(current_date,'MM')>=6 then -6 else -18 end))
and
add_months(TRUNC(CURRENT_DATE,'YYYY'),(case when to_char(current_date,'MM')>=6 then 6 else -6 end))

Note that truncating to the year on the right argument also sets the time to 00:00, this is intended literally to check for dates between 00:00 hour July 1st with different years, and thus no need to truncate the time component off the left argument.
0
 
corobvAuthor Commented:
Thanks, this helps a lot.  I thought I was over complicating it.  The Current Fiscal Year works great, but the prior Fiscal year is pulling the whole year from 7/1/08 to 6/30/09.  I need it to be the same months and days as current year to date.  

So for today
Current F YTD
7/1/09 to 4/7/10

Prior F YTD
7/1/08 to 4/7/09

They want to be able to compare last year to this year to see if there was an increase or decrease in the number of appointments on a weekly basis.  And there are several reports that they would like to see this way.  
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
Try this

-- for current Fiscal year
PAT_ENC_APPT_TIME>=
add_months(TRUNC(CURRENT_DATE,'YYYY'),(case when to_char(current_date,'MM')>=6 then 6 else -6 end))
and PAT_ENC_APPT_TIME<CURRENT_DATE+1
 
-- for previous Fiscal year
PAT_ENC_APPT_TIME >=
add_months(TRUNC(CURRENT_DATE,'YYYY'),(case when to_char(current_date,'MM')>=6 then -6 else -18 end))
and PAT_ENC_APPT_TIME<ADD_MONTHS(CURRENT_DATE+1,-12)  
0
 
corobvAuthor Commented:
This simplified my query a great deal.   Tomcatkev helped me understand the TRUNC function better.  I'm going to go back and fix my other queries.  And what he has will work well for pulling the entire fiscal year both current and prior.  I can use this for fiscal year end reports.  
shajukg's solution works for a Fiscal YTD comparision of
current and previous fiscal years and both run very quickly.  Thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now