CODEWITHSUNDEEP
oracle-databasejoincalendar
Gilly
Gilly

Reputation: 45

oracle count records daily join

Gurus, (Oracle 12c) I am having a difficult time getting a daily count of records for multiple tables. The base of the query I think should be a calendar. I have create a calendar as the following:

with calendar as(select rownum -1 as day from dual
connect by rownum < sysdate - TO_DATE('2020-08-01')+1)
select to_date('2020-08-01')+ day as sale_date from calendar

Source table:

CREATE TABLE "CARS1" 
   (    "COUNT_CARS" NUMBER(10,0), 
"EQUIPMENT_TYPE" VARCHAR2(100 BYTE), 
"LOCATION" VARCHAR2(500 BYTE), 
"SALE_DATE" DATE) 
SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "USERS" ;

CREATE TABLE "CARS2" 
   (    "COUNT_CARS" NUMBER(10,0), 
"EQUIPMENT_TYPE" VARCHAR2(100 BYTE), 
"LOCATION" VARCHAR2(500 BYTE), 
"SALE_DATE" DATE) 
SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "USERS" ;

DML Script:

INSERT INTO "CARS1" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION,SALE_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2020-08-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS1" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('9', 'Jaguars', 'coventry', TO_DATE('2020-08-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS1" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('7', 'Rovers', 'leamington', TO_DATE('2020-08-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS1" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('10','Trans Am', 'leamington', TO_DATE('2020-08-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS1" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2020-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS1" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('4', 'Rovers', 'leamington', TO_DATE('2020-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
COMMIT;


INSERT INTO "CARS2" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION,SALE_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2020-08-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS2" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('9', 'Jaguars', 'coventry', TO_DATE('2020-08-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS2" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('7', 'Rovers', 'leamington', TO_DATE('2020-08-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS2" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('10','Trans Am', 'leamington', TO_DATE('2020-08-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS2" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2020-08-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CARS2" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, SALE_DATE) VALUES ('4', 'Rovers', 'leamington', TO_DATE('2020-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
COMMIT;

I can simply get the count of cars from 1 table without using the calendar as a base for the results, but I can't figure out how to make the calendar day as the base and join multiple tables to get a daily count of cars from that table (lets say adding 40 tables to get the daily counts on each against each day).

Result would look like:

#SALE_DATE #  cars1  cars2
2020-08-01    12      4
2020-08-02    0       17
2020-08-03    0       25
2020-08-04    7       0
2020-08-05    17      0

Upvotes: 0

Views: 39

Answers (1)

ekochergin
ekochergin

Reputation: 4129

The task appeared to be a bit harder as I thought in the beginning. This is how you can get the result you needed

with calendar as(select level + to_date('2020-08-01', 'yyyy-mm-dd') - 1 day 
                   from dual
                connect by level + to_date('2020-08-01', 'yyyy-mm-dd') < to_date('2020-09-01', 'yyyy-mm-dd')),
     c1_sales as (select sale_date, sum(count_cars) count_cars from cars1 group by sale_date),
     c2_sales as (select sale_date, sum(count_cars) count_cars from cars2 group by sale_date)

select c.day, nvl(t1.count_cars, 0), nvl(t2.count_cars, 0)
  from calendar c,
       c1_sales t1,
       c2_sales t2
 where t1.sale_date(+) = c.day
   and t2.sale_date(+) = c.day
 order by c.day;

Thanks for a tricky question - it was interesting to solve

Upvotes: 1

Related Questions