Home » SQL & PL/SQL » SQL & PL/SQL » date condition help
date condition help [message #215698] Tue, 23 January 2007 09:46 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Guys, I need help. I'm trying to capture results that are for a seven hour period of time plus the difference between
MAX_DC_DATE and MIN_DC_DATE. The problem is if there are results after midnight I'm not getting them with the date condition
in the WHERE clause. I'm not sure how to get around this simple problem. Any help would be greatly appreciated.

SELECT 
      RES1.pat_seq,
      RES1.Perform_Date_Time,
      RES1.perform_ddt,
      RES1.rv,
      ddt.tochar(RES1.MIN_DC_DATE),
      ddt.tochar(RES1.MAX_DC_DATE)
   FROM
      (SELECT /*+ ORDERED */ 
         p.pat_seq,
         MIN(pr.perform_ddt) OVER (PARTITION BY pr.pat_seq,pcql.class_name ORDER BY pr.pat_seq) MIN_DC_DATE,
         MAX(pr.perform_ddt) OVER (PARTITION BY pr.pat_seq,pcql.class_name ORDER BY pr.pat_seq) MAX_DC_DATE,
         pr2.perform_ddt,
         DDT.TOCHAR(pr2.perform_ddt,'MM-DD   HH24:MI') Perform_Date_Time,
         pcql.class_name||'-'||pcql.name2||'-'||pr2.result_value RV
      FROM
         p,
         patres pr,
         patres pr2,
         pcql
      WHERE
         pr.pat_seq = p.pat_seq AND
         pr2.pat_seq = p.pat_seq AND
         pr2.label_seq = pcql.label_seq AND
         (discharge_dt > TRUNC(SYSDATE-1) OR discharge_dt IS NULL) AND
         p.facility_id = 'A' AND
         p.dept_id IN ('CC','IF','IM','NI','IM','NF') AND
         (pr.label_seq IN (1514,1515) AND pr.result_value LIKE ('%Shthdc%')) AND
         pr2.label_seq IN (6879,6880,6881,9638,9761,9993)
      GROUP BY
         p.pat_seq,
         pr.result_value,
         pr.pat_seq,
         pr.perform_ddt,
         pr2.perform_ddt,
         pcql.class_name,
         pcql.name2,
         pr2.result_value) RES1
   WHERE
      DDT.TODATE(RES1.MIN_DC_Date) BETWEEN TRUNC((SYSDATE-1)) AND TO_CHAR(TRUNC((sysdate-1)+((23/24)+(59/1440)))) AND
      RES1.perform_ddt BETWEEN RES1.MIN_DC_Date AND (RES1.MIN_DC_Date+25200+(RES1.MAX_DC_DATE - RES1.MIN_DC_DATE))
   ORDER BY
      perform_ddt
Re: date condition help [message #215701 is a reply to message #215698] Tue, 23 January 2007 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
>TO_CHAR(TRUNC((sysdate-1)+((23/24)+(59/1440))))
The above relys/implies implict data type conversion
I'd use TO_DATE around the above.

>(RES1.MIN_DC_Date+25200+(RES1.MAX_DC_DATE - RES1.MIN_DC_DATE))
I'm not certain about data type conversion for the above.
Re: date condition help [message #215704 is a reply to message #215698] Tue, 23 January 2007 10:22 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I tried

TO_DATE(TRUNC((sysdate-1)+((23/24)+(59/1440))))

and it made no difference.

(RES1.MIN_DC_Date+25200+(RES1.MAX_DC_DATE - RES1.MIN_DC_DATE))

these are number data types..in the form of I believe UNIX dates (ie: 7562697980). Sorry I didn't mention that before.
Re: date condition help [message #215736 is a reply to message #215704] Tue, 23 January 2007 13:00 Go to previous messageGo to next message
mike7
Messages: 3
Registered: January 2007
Junior Member
It looks like the first part of the where statement is making sure that RES1.MIN_DC_Date is yesterday, right?

DDT.TODATE(RES1.MIN_DC_Date) BETWEEN TRUNC((SYSDATE-1)) AND TO_CHAR(TRUNC((sysdate-1)+((23/24)+(59/1440))))

means RES1.MIN_DC_Date has to be between yesterday at 12:00:00 AM and yesterday 11:59:00 PM. If that shouldn't be the case, just remove it. Does that fix it or am I misunderstanding your problem?

Thanks,

Mike
Re: date condition help [message #215739 is a reply to message #215698] Tue, 23 January 2007 13:28 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Correct. I'm want to pull only WHERE MIN_DC_DATE = YESTERDAY. However, some of the results (pr2.perform_ddt) from the proceedure run into the next day (today).


What I don't understand is if I use just the below WHERE clause I get all of the desired results and then some (more than the seven hours plus the difference between MAX_DC_DATE and MIN_DC_DATE. I get results that go into the next day(today)).
   WHERE
      RES1.MIN_DC_Date BETWEEN DDT.FROMDATE(TRUNC(SYSDATE-1)) AND DDT.FROMDATE(TRUNC((sysdate-1)+((23/24)+(59/1440))))

If I use the following WHERE clause I don't get the results that go into the next day (today)
   WHERE
      RES1.perform_ddt BETWEEN RES1.MIN_DC_Date AND (RES1.MIN_DC_Date+25200+(RES1.MAX_DC_DATE - RES1.MIN_DC_DATE))

[Updated on: Tue, 23 January 2007 14:28]

Report message to a moderator

Re: date condition help [message #215743 is a reply to message #215739] Tue, 23 January 2007 14:32 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
What about
where RES1.MIN_DC_Date >= trunc(sysdate-1)
  and RES1.MIN_DC_Date <  trunc(sysdate)

[Updated on: Tue, 23 January 2007 14:32]

Report message to a moderator

Re: date condition help [message #215748 is a reply to message #215743] Tue, 23 January 2007 14:53 Go to previous messageGo to next message
mike7
Messages: 3
Registered: January 2007
Junior Member
I don't understand the number format of the date part. It looks like 25200 is the number of seconds in 7 hours.

I think that the problem is in the second part of the where statement.

I bet that if you comment out the second part of the where statement (so that you will get records past midnight) and then select (RES1.MAX_DC_DATE - RES1.MIN_DC_DATE), you will see the problem.

So make it look like this just temporarily to study it:
SELECT RES1.pat_seq
      ,RES1.Perform_Date_Time
      ,RES1.perform_ddt
      ,RES1.rv
      ,ddt.tochar(RES1.MIN_DC_DATE)
      ,ddt.tochar(RES1.MAX_DC_DATE)
      ,(RES1.MAX_DC_DATE - RES1.MIN_DC_DATE) seconds_difference
FROM   (SELECT /*+ ORDERED */
         p.pat_seq
        ,MIN(pr.perform_ddt) OVER(PARTITION BY pr.pat_seq, pcql.class_name ORDER BY pr.pat_seq) MIN_DC_DATE
        ,MAX(pr.perform_ddt) OVER(PARTITION BY pr.pat_seq, pcql.class_name ORDER BY pr.pat_seq) MAX_DC_DATE
        ,pr2.perform_ddt
        ,DDT.TOCHAR(pr2.perform_ddt, 'MM-DD   HH24:MI') Perform_Date_Time
        ,pcql.class_name || '-' || pcql.name2 || '-' || pr2.result_value RV
        FROM   p
              ,patres pr
              ,patres pr2
              ,pcql
        WHERE  pr.pat_seq = p.pat_seq
        AND    pr2.pat_seq = p.pat_seq
        AND    pr2.label_seq = pcql.label_seq
        AND    (discharge_dt > TRUNC(SYSDATE - 1) OR discharge_dt IS NULL)
        AND    p.facility_id = 'A'
        AND    p.dept_id IN ('CC', 'IF', 'IM', 'NI', 'IM', 'NF')
        AND    (pr.label_seq IN (1514, 1515) AND pr.result_value LIKE ('%Shthdc%'))
        AND    pr2.label_seq IN (6879, 6880, 6881, 9638, 9761, 9993)
        GROUP  BY p.pat_seq
                 ,pr.result_value
                 ,pr.pat_seq
                 ,pr.perform_ddt
                 ,pr2.perform_ddt
                 ,pcql.class_name
                 ,pcql.name2
                 ,pr2.result_value) RES1
WHERE  DDT.TODATE(RES1.MIN_DC_Date) BETWEEN TRUNC((SYSDATE - 1)) AND TO_CHAR(TRUNC((SYSDATE - 1) + ((23 / 24) + (59 / 1440))))
-- AND    RES1.perform_ddt BETWEEN RES1.MIN_DC_Date AND (RES1.MIN_DC_Date + 25200 + (RES1.MAX_DC_DATE - RES1.MIN_DC_DATE))
ORDER  BY perform_ddt


I'm thinking that "seconds difference" will be negative for records past midnight. If so, You might need to change the RES1.MIN_DC_date and RES1.MAX_DC_DATE to dates before subtracting them?

As for the first part of the where clause, I think Joy_Division is on the right track. It can be much simpler. If they are not dates, you might try
trunc(to_date(RES1.MIN_DC, 'whatever the date format is')) = trunc(sysdate - 1)
It's just easier to read for me.


P.S. Just so I'm not confusing, I need to let you know that the above SQL is just to look for the problem and not the solution!

[Updated on: Tue, 23 January 2007 15:38]

Report message to a moderator

Re: date condition help [message #215749 is a reply to message #215743] Tue, 23 January 2007 14:53 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I guess I'm not explaining very clearly what's going on. I appologize.
MIN_DC_DATE and MAX_DC_DATE represent the first and last dates in UNIX format that a proceedure was completed. In this case the difference is 33 minutes (it's not a negative number). So I want to get all results for 'pr2.result_value' for at least seven hours starting with MIN_DC_DATE. But since there's a MAX_DC_DATE (indicating more than one proceedure) I want to add the difference between MAX_DC_DATE and MIN_DC_DATE and add to the seven hours so in total collect seven hours and 33 minutes worth of results from pr2.result_value. Some of the results from pr.result_value 'run' into the next day and it's these values that I'm not getting in the result set.
The first line of the WHERE clause is selecting all who had a proceedure performed between 0000 and 2359 yesterday and the second line is pulling, in this case, seven hours and 33 minutes worth of results from pr.result_value. I'm just not getting the results that go past 2359 to get the total of seven hours and 33 minutes.

WHERE
   RES1.MIN_DC_Date BETWEEN DDT.FROMDATE(TRUNC(SYSDATE-1)) AND DDT.FROMDATE(TRUNC((sysdate-1)+((23/24)+(59/1440)))) AND
   RES1.perform_ddt BETWEEN RES1.MIN_DC_Date AND (RES1.MIN_DC_Date+25200+(RES1.MAX_DC_DATE - RES1.MIN_DC_DATE))

[Updated on: Tue, 23 January 2007 18:30]

Report message to a moderator

Re: date condition help [message #216014 is a reply to message #215698] Wed, 24 January 2007 11:45 Go to previous message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I fixed the problem by using the following line in the WHERE clause:

      DDT.TODATE(RES1.perform_ddt) BETWEEN DDT.TODATE(RES1.MIN_DC_Date) AND (DDT.TODATE(RES1.MIN_DC_Date) + 7/24 + (DDT.TODATE(RES1.MAX_DC_DATE) - DDT.TODATE(RES1.MIN_DC_DATE)))

instead of
   RES1.perform_ddt BETWEEN RES1.MIN_DC_Date AND (RES1.MIN_DC_Date+25200+(RES1.MAX_DC_DATE - RES1.MIN_DC_DATE))



why did I have to convert to a date format? Can anyone think of an instance where this might not work or break?

Thanks all for looking,
Stan
Previous Topic: SQL Hint
Next Topic: Update all the retrieved records at once (meged)
Goto Forum:
  


Current Time: Fri Dec 02 12:10:55 CST 2016

Total time taken to generate the page: 0.13448 seconds