CODEWITHSUNDEEP
sqloracle-databaseconditional-aggregation
kailong chai
kailong chai

Reputation: 13

how to optimize my oracle sql?

I need count in range two date，this sql is work，bug not better,can you help me?

select dmc.doctor_id,
(
    select count(*)
    from hele_dct_member_config dmc
    WHERE (EXTRACT(YEAR FROM dmc.start_time) = 2016 OR EXTRACT(YEAR FROM dmc.end_time) = 2016) AND dmc.status=1
    AND TO_DATE('2016-01-31', 'yyyy-mm-dd') BETWEEN start_time AND end_time
) Jan,
(
    select count(*)
    from hele_dct_member_config dmc
    WHERE (EXTRACT(YEAR FROM dmc.start_time) = 2016 OR EXTRACT(YEAR FROM dmc.end_time) = 2016) AND dmc.status=1
    AND TO_DATE('2016-02-28', 'yyyy-mm-dd') BETWEEN start_time AND end_time
) Feb,
.
.
.
from hele_dct_member_config dmc
enter code here
WHERE (EXTRACT(YEAR FROM dmc.start_time) = 2016 OR EXTRACT(YEAR FROM dmc.end_time) = 2016) AND dmc.status=1
grouy by dmc.doctor_id

I need count in range two date，this sql is work，bug not better,can you help me?

Upvotes: 1

Views: 37

Answers (2)

kailong chai
kailong chai

Reputation: 13

if i want get quarter count ? this is one way

   SUM(case when date '2016-01-31' BETWEEN start_time AND end_time then 1 else 0 end) + 
   SUM(case when date '2016-02-28' BETWEEN start_time AND end_time then 1 else 0 end) + 
   SUM(case when date '2016-03-31' BETWEEN start_time AND end_time then 1 else 0 end)  one

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Use conditional aggregation:

select dmc.doctor_id,
       sum(case when date '2016-01-31' BETWEEN start_time AND end_time then 1 else 0
           end) as Jan,
       sum(case when date '2016-02-31' BETWEEN start_time AND end_time then 1 else 0
           end) as Feb,
    .
    .
    .
from hele_dct_member_config dmc
where (extract(year from dmc.start_time) = 2016 or
       extract(year from dmc.end_time) = 2016) AND
      dmc.status = 1
group by dmc.doctor_id;

Upvotes: 1

Related Questions