1 2 Previous Next 22 Replies Latest reply: Jul 11, 2012 12:39 AM by Purvesh K Go to original post RSS
      • 15. Re: How to get the dates between Two dates excluding Saturaday and Sunday
        Frank Kulash
        Hi, Gurujothi,
        Gurujothi wrote:
        Hi Frank,
        Sorry for my fault,
        The following is my table description,

        CREATE TABLE  "DATES" 
        (     "FROMDATE" DATE, 
             "TODATE" DATE,
        "LEAVE_ID" Number(5)
        )
        /
        Insert into dates values('05-02-2012','05-05-2012',1);
        Thanks for posting the CREATE TABLE and INSERT statments.
        Unfortunately, they don't work on my system, so I still can't test anything. Those statements depend on NLS settings, so they may work on your system, but that doesn't help me. They may quit working on your system, too, when you don't expect it.
        Fromdate and todate are DATEs. Don't try to insert a VARCHAR2, such as '05-02-2012' into a DATE column. Use TO_DATE or a DATE literal instead.
        Create table holiday_dates(holidays date);
        
        insert into holiday_dates values('01-05-2012');
        Now when I used this query,
        select count(*) from (select dt
        from(
        select to_date(fromdate, 'DD-MM-YYYY') + rownum -1 dt 
        from dates 
        connect by level <= to_date(todate, 'DD-MM-YYYY') - to_date(fromdate, 'DD-MM-YYYY') + 1
        )
        where to_char(dt,'fmday') not in ('sunday','saturday') minus (select holidays from holiday_dates)) dual;
        /
        So you're not using any of the ideas in my query. Also, you're using TO_DATE on columns that are already DATEs.
        Count(*)
        64
        
        /
        Insert into dates values('01/05/2012','05/05/2012',2);
        /
        Now my table has 2 rows,
        select  *  from dates;
        
        
        FROMDATE     TODATE         LEAVE_ID
        01/05/2012     05/05/2012            1
        05/02/2012     05/05/2012            2
        Now when I used this query,
        select count(*) from (select dt
        from(
        select to_date(fromdate, 'DD-MM-YYYY') + rownum -1 dt 
        from dates 
        connect by level <= to_date(todate, 'DD-MM-YYYY') - to_date(fromdate, 'DD-MM-YYYY') + 1
        )
        where to_char(dt,'fmday') not in ('sunday','saturday') minus (select holidays from holiday_dates)) dual;
        The output is ,
        COUNT(*)
        1987
        Right. "level <= x" is the only CONNECT BY condition, and the table has more than 1 row, so the results might not be what you expect. The query I posted would return 67.
        How to get the output using the max(leave_id) like the following,
        select..........from... where leave_id=(select max(leave_id) from dates);
        where to add "WHERE" clause in the above query?
        I don't suggest using the query above.
        If you must use that query, where to add the new WHERE condition depends on what results you want, and why.

        Using the query I posted, you could add that condition to the main query, but it would be more efficient to apply that condition to the dates table first, before you did the CONNECT BY, like this:
        WITH     dates_minus          AS
        (
             SELECT     *
             FROM     dates
             WHERE     leave_id     = (
                                  SELECT  MAX (leave_id)
                                  FROM    dates
                              )
        )
        ,     cntr     AS
        (
             SELECT     LEVEL - 1     AS n
             ,     rownum          as r
             FROM     (
                       SELECT  MAX (todate - fromdate)     AS max_range
                       FROM     dates_minus
                  )
             CONNECT BY     LEVEL     <= max_range + 1
        )
        SELECT  COUNT (*)     AS total_work_days
        FROM           dates_minus      d
        JOIN           cntr           c  ON     c.n          <= d.todate - d.fromdate
        LEFT OUTER JOIN      holiday_dates     h  ON      h.holidays  = d.fromdate + c.n
        WHERE     TO_CHAR ( d.fromdate + c.n
                  , 'fmDay'
                  , 'NLS_DATE_LANGUAGE=ENGLISH'
                  )     NOT IN ('Saturday', 'Sunday')
        AND     h.holidays     IS NULL
        ;
        The first sub-query, dates_minus, just applies the new condition.
        The rest of the query is just what I posted earlier, except that all references to the dates table have been replaced by references to the filtered dates table, dates_minus.
        • 16. Re: How to get the dates between Two dates excluding Saturaday and Sunday
          Gurujothi
          Dear Frnak,
          Thank you your post,
          But I couldn't understand which you posted,
          I mentioned only 2 tables(DATES,HOLIDAY_DATES),
          But there is another table called "dates_minus"

          For what we are using that table here?
          Can you explain your query please?
          Thank you.
          • 17. Re: How to get the dates between Two dates excluding Saturaday and Sunday
            Frank Kulash
            Hi,
            Gurujothi wrote:
            Dear Frnak,
            Thank you your post,
            But I couldn't understand which you posted,
            I mentioned only 2 tables(DATES,HOLIDAY_DATES),
            But there is another table called "dates_minus"
            Dates_minus is actually a sub-query, not a table, but it's used like a table or a view.

            Cntr isn't a table, either. If you understand how cntr is generated, and how it functions after it is created, then you shouldn't have any problem understanding dates_minus. It is created and used much the same way.
            For what we are using that table here?
            Can you explain your query please?
            Dates_minus is a filtered version of the dates table; it contains only the rows from dates that have the highest leave_id.
            • 18. Re: How to get the dates between Two dates excluding Saturaday and Sunday
              679188
              Hi All,

              I want to subtract dates mentioned below in hours with excluding SUNDAY.

              10-May-2012 14:19:03 ( DD-Mon-YYYY HH24:MI:SS )

              15-May-2012 14:19:03 ( DD-Mon-YYYY HH24:MI:SS )


              Actually the difference is 120 Hours and after excluding sunday it should give 96 Hrs.

              After searching all the post now am able to find the difference in days after excluding sunday . But i need the difference in Hours after excluding SUNDAY .

              Below script in days after excluding SUNDAY.

              SELECT COUNT(*) FROM (SELECT ROWNUM RNUM FROM ALL_OBJECTS WHERE ROWNUM <= TO_DATE('15-may-2012') - TO_DATE('10-may-2012') + 1) WHERE TO_CHAR(TO_DATE('10-may-2012') + RNUM - 1, 'DY') NOT IN('SUN');

              OR

              select dt from(select to_date('10-05-2012', 'DD-MM-YYYY') + rownum -1 dt from dual connect by level <= to_date('15-05-2012 14:03:22', 'DD-MM-YYYY HH24:MI:SS') - to_date('10-05-2012 14:03:19', 'DD-MM-YYYY HH24:MI:SS') + 1) where to_char(dt,'fmday') not in ('sunday');

              Need yours Help to finalise the script .
              • 19. Re: How to get the dates between Two dates excluding Saturaday and Sunday
                sb92075
                user10748284 wrote:
                Hi All,

                I want to subtract dates mentioned below in hours with excluding SUNDAY.

                10-May-2012 14:19:03 ( DD-Mon-YYYY HH24:MI:SS )

                15-May-2012 14:19:03 ( DD-Mon-YYYY HH24:MI:SS )


                Actually the difference is 120 Hours and after excluding sunday it should give 96 Hrs.

                After searching all the post now am able to find the difference in days after excluding sunday . But i need the difference in Hours after excluding SUNDAY .
                days*24 = HOURS
                • 20. Re: How to get the dates between Two dates excluding Saturaday and Sunday
                  679188
                  Thanks for your quick reply .

                  If we will go with days then from 10-may-2012 to 15-may-2012 will be 5 days(excluding sunday) and by *24 its coming 120Hrs.

                  But if we will actually subtract date and time as below its coming 4 days (excluding sunday) .


                  (15-May-2012 14:19:03)-(10-May-2012 14:19:03) =0 weeks, 5 days, 0 hours, 0 minutes, and 0 seconds ( With Out Excluding SUNDAY*)

                  We can calculate the date difference in below link easily.

                  http://www.convertalot.com/date_difference_calculator.html

                  This the difference if we will calculate in days and *24 . So want to calculate in Hours directly .
                  • 21. Re: How to get the dates between Two dates excluding Saturaday and Sunday
                    679188
                    Hi All,

                    Please help me in this . I really pissed off with this requirement and not getting any solution .

                    Thanks
                    • 22. Re: How to get the dates between Two dates excluding Saturaday and Sunday
                      Purvesh K
                      Since the Question is already Answered and you are not OP. So, post your requirement in a New Question with the Create Table, Insert Script, your Desired Result and the Logic to arrive to the desired result.
                      1 2 Previous Next