1 2 Previous Next 20 Replies Latest reply on Jan 9, 2013 11:54 AM by user8680179 Go to original post
      • 15. Re: SQL Query for IN,GroupBy and count(*)
        user8680179
        very-very thankx Purvesh K and chris227
        if number of rows in my Emp table is 40000000(4*10^7) then case-when-then statement is efficient??
        or in above two solution which one shall be faster.
        • 16. Re: SQL Query for IN,GroupBy and count(*)
          chris227
          One thing is still not clear and therefore the queries from Purvesh and me differ semantically.

          Is the count (*) >= 3 ment regarding to the time intervals or to the status = PRESENT or over all?

          In my query i assume it is overall.
          Purvesh assumes it is regarding to the time intervals AND the status present.

          If it is only on the time intervals, setting status of row 2 two NOT PRESENT would result in
          with
          EMP(STAFFNO, PRESENT_DATE, STATUS, DURATIONINHOUR) as (
          Select 1, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 8 from dual union all
          Select 1, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NOT PRESENT', 9 from dual union all
          Select 1, to_date('12/08/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NOT PRESENT', 9 from dual union all
          Select 1, to_date('12/13/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10 from dual union all
          Select 2, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10 from dual union all
          Select 2, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10 from dual union all
          Select 3, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 8 from dual union all
          Select 3, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 9 from dual
          )
           
          select
           staffno
          ,SUM(
            case status when 'PRESENT' then DURATIONINHOUR end
           )
          sum
          from 
          emp e
          group by
          staffno
          ,
          case
          when present_date
          between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
              AND to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
          then 1
          when present_date
          between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
              AND to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
          then 1
          else 0
          end
          having (
          count(*) >= 3
          and
          sum(case
          when present_date
          between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
              AND to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
          then 1
          when present_date
          between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
              AND to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
          then 2
          else 0
          end) > count(*)
          )
          
          STAFFNO SUM 
          1 18 
          Edited by: chris227 on 09.01.2013 03:22
          • 17. Re: SQL Query for IN,GroupBy and count(*)
            Purvesh K
            user8680179 wrote:
            very-very thankx Purvesh K and chris227
            if number of rows in my Emp table is 40000000(4*10^7) then case-when-then statement is efficient??
            or in above two solution which one shall be faster.
            I do not understand if you can get a better approach than the Case-When-Then. And also, never came across a performance issue that was due to Decision Control Statements.

            However, to check which of the solutions is faster, you will have to test it on actual data. It is very difficult (and also foolish) to say it without testing.
            1 person found this helpful
            • 18. Re: SQL Query for IN,GroupBy and count(*)
              user8680179
              @chrish
              The data which u specified in previous post, output shall be zero rows...
              count(*)>=3 means there should be at least 3 rows for status present in both date intervals...but there should be at least one rows in each date interval for status present

              Edited by: user8680179 on Jan 9, 2013 3:45 AM
              • 19. Re: SQL Query for IN,GroupBy and count(*)
                chris227
                Ok, then there no need to consider rows with status not present at all, but only those with status = PRESENT

                last-ditch attempt, now correct ;-), ughh, not my day i guess ...
                with
                EMP(STAFFNO, PRESENT_DATE, STATUS, DURATIONINHOUR) as (
                Select 1, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 8 from dual union all
                Select 1, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 9 from dual union all
                Select 1, to_date('12/08/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NOT PRESENT', 9 from dual union all
                Select 1, to_date('12/13/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10 from dual union all
                Select 2, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10 from dual union all
                Select 2, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10 from dual union all
                Select 3, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 8 from dual union all
                Select 3, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 9 from dual union all
                select 3, to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 9 from dual
                )
                 
                select
                 staffno
                ,SUM(
                  case status when 'PRESENT' then DURATIONINHOUR end
                 )
                sum
                from 
                emp e
                where
                status = 'PRESENT'
                group by
                staffno
                ,
                case
                when present_date
                between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                    AND to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                then 1
                when present_date
                between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                    AND to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                then 1
                else 0
                end
                having(
                count(*) >= 3
                and
                count(distinct case
                when present_date
                between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                    AND to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                then 1
                when present_date
                between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                    AND to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                then 2
                else 0
                end) = 2
                )
                Edited by: chris227 on 09.01.2013 03:56

                Edited by: chris227 on 09.01.2013 04:25
                correctd

                Edited by: chris227 on 09.01.2013 05:11
                corrected again and again ...
                1 person found this helpful
                • 20. Re: SQL Query for IN,GroupBy and count(*)
                  user8680179
                  yeah, there is no need to consider status 'Not Present'
                  1 2 Previous Next