This discussion is archived
5 Replies Latest reply: Nov 19, 2012 10:18 PM by jeneesh RSS

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

user555994 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    where is Mike age 27 coming from???

    Cheers,
    Manik.
  • 3. Re: Using case condition for date count of distinct values is not matching
    Marwim Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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"

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points