14 Replies Latest reply on Nov 5, 2010 8:59 AM by andyschwarz

    how to count the number of Fridays and Saturdays between two dates

    khalidoracleit
      Hi every one ... If we want to count the number of Fridays and Saturdays between two dates, how would we do that ? !

      Dates are ( 11-Feb-2010) to (19-May-2010)

      how to do it in SQL

      Edited by: khalidoracleit on Jul 28, 2010 5:51 AM
        • 1. Re: how to count the number of Fridays and Saturdays between two dates
          Karthick2003
          You can do this
          with t
          as
          (
          select sysdate-100 start_date, sysdate end_date
            from dual
          )
          select count(*)
            from (select to_char(start_date + (level-1), 'fmday') dt
                    from t
                  connect by level <= end_date-start_date+1)
           where dt in ('friday','saturday')
          • 2. Re: how to count the number of Fridays and Saturdays between two dates
            Z?
            Or...
            WITH some_dates AS (SELECT     TRUNC(TO_DATE(:p_date1), 'mm') + LEVEL - 1 dt
                              FROM       DUAL
                              CONNECT BY LEVEL <= :p_date1 - :p_date2)
            SELECT COUNT(*)
            FROM   some_dates
            WHERE  TRUNC(dt) - TRUNC(dt, 'iw') IN(4,5);
            Cheers

            Ben
            • 3. Re: how to count the number of Fridays and Saturdays between two dates
              737905
              try this:
              SQL> ed
              Wrote file afiedt.buf
              
                1  SELECT TO_CHAR(dat,'DY'),COUNT(*) FROM
                2  (select TO_DATE('11-Feb-2010') + num dat from
                3  (SELECT level - 1 num
                4  FROM dual
                5  CONNECT BY level <= ABS((TO_DATE('11-Feb-2010') - TO_DATE('19-May-2010'))-1)))
                6  WHERE TO_CHAR(dat,'DY') IN ('FRI','SAT')
                7* GROUP BY TO_CHAR(dat,'DY')
              SQL> /
              
              TO_CHAR(D   COUNT(*)
              --------- ----------
              FRI               14
              SAT               14
              
              SQL> 
              Edited by: AP on Jul 28, 2010 6:01 AM
              • 4. Re: how to count the number of Fridays and Saturdays between two dates
                781735
                Here goes
                with t as
                (
                select to_date('11-FEB-2010','DD-MON-YYYY')+ level-1 dt from dual
                connect by level <= to_date('19-MAY-2010','DD-MON-YYYY') - to_date('11-FEB-2010','DD-MON-YYYY')+1
                )
                select count(case when TRIM(to_char(dt,'DAY')) = 'FRIDAY' then dt else null end) as FRIDAY_COUNT,
                 count(case when TRIM(to_char(dt,'DAY')) = 'SATURDAY' then dt else null end) as SATURDAY_COUNT
                 from t;
                Thanks,
                Andy
                • 5. Re: how to count the number of Fridays and Saturdays between two dates
                  Aketi Jyuuzou
                  select
                   (next_day(to_date('20100519','yyyymmdd'),'金')-7
                   -next_day(to_date('20100211','yyyymmdd')-1,'金'))/7+1
                  +(next_day(to_date('20100519','yyyymmdd'),'土')-7
                   -next_day(to_date('20100211','yyyymmdd')-1,'土'))/7+1
                  as "金土の日数"
                  from dual;
                  
                  金土の日数
                  ----------
                          28
                  • 7. Re: how to count the number of Fridays and Saturdays between two dates
                    698658
                    Aketi,
                    could You please elaborate a little with your solutions.
                    It's pure magic for me .
                    Regards.
                    Greg
                    • 8. Re: how to count the number of Fridays and Saturdays between two dates
                      Aketi Jyuuzou
                      I am not good at English :-(
                      My first language is Japanese.Second language is English.

                      Although I am going to explain it,
                      I hope that SQL expert of this forum whose first language is English will explain this solution.

                      This solutin is using Uekisan.

                      I call this solution "Uekisan method" B-)
                      Ueki is Japanese language.

                      If you remember "Tabibitosan method" Tabibitosan method tutorial by Aketi Jyuuzou
                      Tabibitosan and Uekisan are math problems.

                      http://yslibrary.cool.ne.jp/sansub0301.html
                      http://www.manabinoba.com/index.cfm/4,1487,73,html?year=2002

                      In Japanese -> English dictionary,
                      "Ueki" means "garden tree".

                      My homepage uses "Uekisan method"
                      http://www.geocities.jp/oraclesqlpuzzle/5-18.html
                      http://www.geocities.jp/oraclesqlpuzzle/7-42.html

                      *******************************************************************************
                      I explain SQL logic ;-)

                      next_day(to_date('20100519','yyyymmdd'),'金')-7
                      is the most nearly Friday which is equal or less than '2010-05-19'
                      It is '2010-05-14'

                      next_day(to_date('20100211','yyyymmdd')-1,'金')
                      is the most nearly Friday which is equal or greater than '2010-02-11'
                      It is '2010-02-12'

                      And then please imagine two Ueki at very huge calendar.
                      One of Ueki exists '2010-02-12'
                      The another exists '2010-05-14'

                      And another Ueki exist between '2010-02-12' and '2010-05-14' of each Friday.

                      Then How many Ueki exists?
                      It is derived by (date '2010-05-14' - date '2010-02-12')/7+1
                      • 9. Re: how to count the number of Fridays and Saturdays between two dates
                        764456
                        SQL> select count(*) fridays_and_saturdays
                          2    from dual
                          3   where to_char(to_date('11-Feb-2010', 'DD-MON-YYYY') + level, 'D') between 6 and 7
                          4   connect by level <= TO_DATE('19-MAY-2010','DD-MON-YYYY') - TO_DATE('11-FEB-2010','DD-MON-YYYY')
                          5  /
                        
                        FRIDAYS_AND_SATURDAYS
                        ---------------------
                                           28
                        Edited by: Stepanyan Alexey on 29.07.2010 4:30
                        • 10. Re: how to count the number of Fridays and Saturdays between two dates
                          Sven W.
                          I would consider all solutions that try to build a table for all the days and then filter on that table as wrong.
                          Just imagine what happens when the distance between both dates is very large.
                          Then the connect by dual is a very slow operation. It donsn't scale very well.
                          There are much better ways, as Aketi already showed.
                          • 11. Re: how to count the number of Fridays and Saturdays between two dates
                            Solomon Yakobson
                            Sven W. wrote:
                            I would consider all solutions that try to build a table for all the days and then filter on that table as wrong.
                            Just imagine what happens when the distance between both dates is very large.
                            Then the connect by dual is a very slow operation. It donsn't scale very well.
                            There are much better ways, as Aketi already showed.
                            Really? And how large distance between both dates can be. Even if we take min possible date of 1/1/4712 BC and max possible date of 12/31/9999 AD the difference will be 5373484 days. And on my old laptop it takes just 3 seconds:
                            SQL> set timing on
                            SQL> select count(*) from (select level from dual connect by level <= 5373484)
                              2  /
                            
                              COUNT(*)
                            ----------
                               5373484
                            
                            Elapsed: 00:00:03.07
                            SQL> 
                            SY.
                            • 12. Re: how to count the number of Fridays and Saturdays between two dates
                              Sven W.
                              But 3 seconds is like ages for a database. Especially compared to a small date-Math operation which results in something like
                              SQL> select trunc(5373484/7)+2 from dual;
                              
                              TRUNC(5373484/7)+2
                              ------------------
                                          767642
                              
                              Elapsed: 00:00:00.02
                              SQL>
                              I left out the data-math but so did you.

                              We all know scenarios where a little time spent is multiplied because this time then is spent for each row of a larger table and then it slows down heavily.
                              • 13. Re: how to count the number of Fridays and Saturdays between two dates
                                785682
                                hi,
                                check this one


                                SELECT COUNT (*), TO_CHAR (date_colun_name, 'DAY')
                                FROM table_name WHERE RTRIM (LTRIM (TO_CHAR (date_colun_name, 'DAY'), ' ')) IN ('FRIDAY', 'SATURDAY')
                                and date_colun_name between strt_date and end_date
                                GROUP BY TO_CHAR (date_colun_name, 'DAY')
                                • 14. Re: how to count the number of Fridays and Saturdays between two dates
                                  andyschwarz
                                  some nice coding here, I'm still amazed with what some people can do with "connect by". But I agree with some statements here that this can take "time", and to be honest, it's funny to see it working, but if you do not have a computer, just a calendar and some paper, would you go for "counting" so there must be a better solution?

                                  The best working math in here is done by Aketi Jyuuzou, who writes so good English that I wonder why he still insists that he doesn't ;-)

                                  Anyhow I "translated" that code to English, and I really like that math. Math is math and data is data.
                                  ALTER SESSION SET NLS_DATE_LANGUAGE='ENGLISH';
                                  
                                  WITH my_dates AS (
                                  SELECT to_date('20100211','yyyymmdd') start_date,to_date('20100519','yyyymmdd') end_date FROM DUAL
                                  UNION ALL
                                  SELECT to_date('20100211','yyyymmdd') start_date,to_date('20100214','yyyymmdd') end_date FROM DUAL
                                  UNION ALL
                                  SELECT to_date('20100211','yyyymmdd') start_date,to_date('20100213','yyyymmdd') end_date FROM DUAL
                                  UNION ALL
                                  SELECT to_date('20100211','yyyymmdd') start_date,to_date('20100212','yyyymmdd') end_date FROM DUAL
                                  )
                                  ------
                                  SELECT to_char(start_date,'DD.MM.YYYY') start_date,to_char(end_date,'DD.MM.YYYY') end_date,
                                         to_char(start_date,'DAY') start_weekday,to_char(end_date,'DAY') end_weekday,
                                         end_date-start_date day_difference,
                                         --
                                         (next_day(end_date,'FRIDAY')-7
                                         -next_day(start_date -1,'FRIDAY'))/7+1
                                         +(next_day(end_date,'SATURDAY')-7
                                         -next_day(start_date -1,'SATURDAY'))/7+1 as count_of_fr_and_sat
                                  FROM my_dates;
                                  
                                  START_DATE END_DATE   START_WEEKDAY                        END_WEEKDAY                          DAY_DIFFERENCE         COUNT_OF_FR_AND_SAT    
                                  ---------- ---------- ------------------------------------ ------------------------------------ ---------------------- ---------------------- 
                                  11.02.2010 19.05.2010 THURSDAY                             WEDNESDAY                            97                     28                     
                                  11.02.2010 14.02.2010 THURSDAY                             SUNDAY                               3                      2                      
                                  11.02.2010 13.02.2010 THURSDAY                             SATURDAY                             2                      2                      
                                  11.02.2010 12.02.2010 THURSDAY                             FRIDAY                               1                      1                      
                                  -- andy