5 Replies Latest reply: Nov 20, 2012 12:18 AM by jeneesh RSS

    Using case condition for date count of distinct values is not matching

    user555994
      Hello PL/SQL Gurus/experts,

      I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production version
      I have following two tables

      TT3 -
      create table TT3(FeesCntlr,CommCntlr,LatePayCntlr,Name,Age) as select
      1,11,21,'Mike',25 from dual union all select
      2,12,22,'Clark',26 from dual union all select
      4,17,27,'Ussan',28 from dual union all select
      5,13,21,'Linda',29 from dual union all select
      6,14,24,'Obrek',35 from dual union all select
      7,15,25,'Batty',45 from dual union all select
      8,16,26,'Nicky',38 from dual;
      TT4
      drop table TT4;
      create table TT4(TRNID,BlockID,FeesCntlr,CommCntlr,LatePayCntlr,BookDate) as select
      221,625,1,11,21,20121101 from dual union all select
      223,625,2,12,22,20121101 from dual union all select
      224,625,1,11,21,20121101 from dual union all select
      225,627,4,17,27,20111001 from dual union all select
      226,628,5,13,21,20120701 from dual union all select
      227,628,6,14,24,20120701 from dual union all select
      334,628,7,15,25,20120701 from dual union all select
      339,629,8,16,26,20120701 from dual union all select
      393,629,1,11,21,20120701 from dual union all select
      432,629,2,12,22,20120701 from dual union all select
      347,629,1,11,21,20120701 from dual union all select
      556,629,4,17,27,20120701 from dual union all select
      558,629,5,13,21,20120701 from dual union all select
      974,629,6,14,24,20120701 from dual union all select
      976,629,7,15,25,20120701 from dual union all select
      980,629,8,16,26,20120701 from dual union all select
      1223,650,2,12,22,20110415 from dual union all select
      1224,650,1,11,21,20110415 from dual union all select
      1225,650,4,17,27,20110415 from dual union all select
      1226,650,5,13,21,20110415 from dual union all select
      1227,650,6,14,24,20110415 from dual union all select
      1334,650,7,15,25,20110415 from dual union all select
      1339,710,8,16,26,20120115 from dual union all select
      1393,710,1,11,21,20120115 from dual union all select
      1432,710,2,12,22,20120115 from dual union all select
      1347,710,1,11,21,20120115 from dual union all select
      1556,710,4,17,27,20120115 from dual union all select
      1558,711,5,13,21,20111231 from dual union all select
      1974,711,6,14,24,20111231 from dual union all select
      1976,711,7,15,25,20111231 from dual ;
      Now if i am using the following DML's for year 2012 then result is as following -
      SQL> select t3.Name,t3.age,count(t4.TRNID),count(distinct(BlockID)) from
        2  tt3 t3,tt4 t4
        3  WHERE     t3.feescntlr = t4.feescntlr
        4           AND t3.commcntlr = t4.commcntlr
        5           AND t3.latepaycntlr = t4.latepaycntlr
        6    AND t4.bookdate between 20120101 and 20121120
        7  GROUP BY t3.name, t3.age;
      
      NAME         AGE COUNT(T4.TRNID) COUNT(DISTINCT(BLOCKID))
      ----- ---------- --------------- ------------------------
      Mike          25              12                        3
      Mike          27              12                        3
      Batty         45               4                        2
      Clark         26               6                        3
      Linda         29               4                        2
      Nicky         38               6                        2
      Obrek         35               4                        2
      Ussan         28               4                        2
      
      8 rows selected.
      Now if i am using the following DML's for year 2011 then result is as following -
      SQL> select t3.Name,t3.age,count(t4.TRNID),count(distinct(BlockID)) from
        2  tt3 t3,tt4 t4
        3  WHERE     t3.feescntlr = t4.feescntlr
        4           AND t3.commcntlr = t4.commcntlr
        5           AND t3.latepaycntlr = t4.latepaycntlr
        6    AND t4.bookdate between 20110101 and 20111120
        7  GROUP BY t3.name, t3.age;
      
      NAME         AGE COUNT(T4.TRNID) COUNT(DISTINCT(BLOCKID))
      ----- ---------- --------------- ------------------------
      Mike          25               2                        1
      Mike          27               2                        1
      Batty         45               2                        1
      Clark         26               2                        1
      Linda         29               2                        1
      Obrek         35               2                        1
      Ussan         28               4                        2
      
      7 rows selected.
      But using the case condition if i use the following dml then results are not matching -
      SQL> select Name,Age,sum(Trn),sum(CurYrOrdr) "2011 Order", sum(LastYrOrdr) "2012 Order"
        2  from
        3  (
        4  select t3.Name,t3.age,count(t4.TRNID) Trn,(case when t4.bookdate between 20110101 and 20111231 
      then count (distinct(BlockID)) else 0 end) CurYrOrdr,
        5  (case when t4.bookdate between 20120101 and 20121120 then count (distinct(BlockID)) else 0 end)
       LastYrOrdr
        6   from
        7  tt3 t3,tt4 t4
        8  WHERE     t3.feescntlr = t4.feescntlr
        9           AND t3.commcntlr = t4.commcntlr
       10           AND t3.latepaycntlr = t4.latepaycntlr
       11    AND t4.bookdate between 20110101 and 20121120
       12  GROUP BY t3.name, t3.age,t4.bookdate
       13  )
       14  group by Name,Age order by Name,Age;
      
      NAME         AGE   SUM(TRN) 2011 Order 2012 Order
      ----- ---------- ---------- ---------- ----------
      Batty         45          8          2          2
      Clark         26          8          1          3
      Linda         29          8          2          2
      Mike          25         14          1          3
      Mike          27         14          1          3
      Nicky         38          6          0          2
      Obrek         35          8          2          2
      Ussan         28          8          2          2
      
      8 rows selected.
      As the expected output i am expecting is -
      ----------------------------------
      
      Expected Output -
      NAME         AGE      "2012 TRNID"       "2011 TRNID"     "2012 ORDERID"     "2011 ORDERID"
      ----- ---------- --------------- ----------------------------- ---------- ------------
      Mike          25        12          2               3          1
      Mike          27        12              2          3          1
      Batty         45        4               2              2          1
      Clark         26        6               2              3          1
      Linda         29        4               2              2          1
      Nicky         38        6               2              2          1
      Obrek         35        4               2              2          1
      Ussan         28        4               4              2          2
      I sincerely thanks to all of you in advance for inputs/feedback as i am struggling with this from some time now.
      Kindly help.
        • 1. Re: Using case condition for date count of distinct values is not matching
          jeneesh
          Like thsi? (Not tested for your data)
          select t3.Name,t3.age,
                 sum(case when t4.bookdate between 20120101 and 20121120 
                                then  1
                             else 0
                        end) cnt_tran_2012,
                 sum(case when t4.bookdate between 20110101 and 20111120 
                                then  1
                             else 0
                        end) cnt_tran_2011,
                 count(distinct 
                        case when t4.bookdate between 20120101 and 20121120 
                                then  BlockID
                             else null
                        end) count_dis_blck_2012,
                 count(distinct 
                        case when t4.bookdate between 20110101 and 20111120 
                                then  BlockID
                             else null
                        end) count_dis_blck_2011       
          from   tt3 t3,tt4 t4
          WHERE     t3.feescntlr = t4.feescntlr
          AND t3.commcntlr = t4.commcntlr
          AND t3.latepaycntlr = t4.latepaycntlr
          --AND t4.bookdate between 20110101 and 20111120
          GROUP BY t3.name, t3.age;
          Between, your sample data and expected outputs are not matching - In your data there is only one mike (Age 25), but in the expected output, thre are two Mikes..!!

          Edited by: jeneesh on Nov 19, 2012 1:23 PM
          • 2. Re: Using case condition for date count of distinct values is not matching
            Manik
            where is Mike age 27 coming from???

            Cheers,
            Manik.
            • 3. Re: Using case condition for date count of distinct values is not matching
              Marwim
              Hello,
              AND t4.bookdate between 20110101 and 20111120
              Your values from the 2011 query only selects values until Nov 20th, your query for both years takes the complete year. When I compare the results with the complete year 2011 I cannot see a wrong result.
              SELECT t3.Name
                ,t3.age
                ,COUNT(t4.TRNID)
                ,COUNT(DISTINCT(BlockID))
              FROM tt3 t3
                ,tt4 t4
              WHERE t3.feescntlr      = t4.feescntlr
                  AND t3.commcntlr    = t4.commcntlr
                  AND t3.latepaycntlr = t4.latepaycntlr
                  AND t4.bookdate BETWEEN 20120101 AND 20121120
              GROUP BY t3.name
                ,t3.age;
              SELECT t3.Name
                ,t3.age
                ,COUNT(t4.TRNID)
                ,COUNT(DISTINCT(BlockID))
              FROM tt3 t3
                ,tt4 t4
              WHERE t3.feescntlr      = t4.feescntlr
                  AND t3.commcntlr    = t4.commcntlr
                  AND t3.latepaycntlr = t4.latepaycntlr
                  AND t4.bookdate BETWEEN 20110101 AND 20111120
              GROUP BY t3.name
                ,t3.age;
              
              SELECT  name
                     ,age
                     ,SUM(trn)
                     ,SUM(CurYrOrdr) "2011 Order"
                     ,SUM(LastYrOrdr) "2012 Order"
              FROM    (
                  SELECT  t3.Name
                         ,t3.age
                         ,COUNT(t4.TRNID) Trn
                         ,(CASE
                              WHEN t4.bookdate BETWEEN 20110101 AND 20111231
                              THEN COUNT (DISTINCT(BlockID))
                              ELSE 0
                           END
                           ) CurYrOrdr
                         ,(CASE
                              WHEN t4.bookdate BETWEEN 20120101 AND 20121120
                              THEN COUNT (DISTINCT(BlockID))
                              ELSE 0
                           END
                           ) LastYrOrdr
                  FROM    tt3 t3
                         ,tt4 t4
                  WHERE   t3.feescntlr    = t4.feescntlr
                  AND     t3.commcntlr    = t4.commcntlr
                  AND     t3.latepaycntlr = t4.latepaycntlr
                  AND     t4.bookdate BETWEEN 20110101 AND 20121120
                  GROUP BY t3.name
                          ,t3.age
                          ,t4.bookdate
                  )
              GROUP BY Name
                      ,Age
              ORDER BY Name
                      ,Age;
              gives
              NAME  AGE COUNT(T4.TRNID) COUNT(DISTINCT(BLOCKID))
              ----- --- --------------- ------------------------
              Batty  45               2                        2 
              Clark  26               3                        3 
              Linda  29               2                        2 
              Mike   25               6                        3 
              Nicky  38               3                        2 
              Obrek  35               2                        2 
              Ussan  28               2                        2 
              
              
              NAME  AGE COUNT(T4.TRNID) COUNT(DISTINCT(BLOCKID))
              ----- --- --------------- ------------------------
              Batty  45               2                        2 
              Clark  26               1                        1 
              Linda  29               2                        2 
              Mike   25               1                        1 
              Obrek  35               2                        2 
              Ussan  28               2                        2 
              
              
              NAME  AGE SUM(TRN) 2011 Order 2012 Order
              ----- --- -------- ---------- ----------
              Batty  45        4          2          2 
              Clark  26        4          1          3 
              Linda  29        4          2          2 
              Mike   25        7          1          3 
              Nicky  38        3          0          2 
              Obrek  35        4          2          2 
              Ussan  28        4          2          2 
              seems to be correct.

              Regards
              Marcus
              • 4. Re: Using case condition for date count of distinct values is not matching
                user555994
                Hello jeneesh
                thanks for the input and definately once i comment out following line -
                --AND t4.bookdate between 20110101 and 20111120

                then values are matching but at the same time why do we need to comment the date range condition as case is just a filter and not the condition.
                • 5. Re: Using case condition for date count of distinct values is not matching
                  jeneesh
                  user555994 wrote:
                  Hello jeneesh
                  thanks for the input and definately once i comment out following line -
                  --AND t4.bookdate between 20110101 and 20111120

                  then values are matching but at the same time why do we need to comment the date range condition as case is just a filter and not the condition.
                  filter should be -
                  AND t4.bookdate between 20110101 and 20121120 --"made it 12"