1

I have two tables:

Table A:

ID CODE time-stamp
Aid_z code_z 2020-07-21
... ... ...
Aid_1 code_1 2020-11-30
Aid_2 code_2 2020-11-30
Aid_3 code_2 2020-11-30
Aid_4 code_1 2020-11-30
Aid_5 code_3 2020-11-30
... ... ...
Aid_n code_x 2021-04-06

ID is unique, there is 67 distinct CODEs.

Table B:

ID ID_A status value time-stamp
Bid_z Aid_z z ... 2020-07-21
... ... ... ... ...
Bid_1 Aid_1 1 101.1 2020-11-30
Bid_2 Aid_2 0 87.6 2020-11-30
Bid_3 Aid_3 0 76.2 2020-11-30
Bid_4 Aid_4 1 106.2 2020-11-30
Bid_5 Aid_5 2 124.6 2020-11-30
... ... ... ... ...
Bid_n Aid_n x ... 2021-04-06

ID is unique, ID_A is the same as ID from Table A.

What I need to do is:

  1. Count how much of all records are each day
  2. Count amount of each distinct CODEs
  3. Present them as a columns and each row should be representing one day
  4. Only for status "0" or "1" from Table B
  5. Only for records between 2020-11-30 and 2021-02-06

In the end reasult should looks like:

date (per day) sum_of_this_day code_1 code_2 code_2 ... code_z
2020-11-30 35 5 0 10 ... x
2020-11-31 60 6 5 12 ... x
... ... ... ... ... ... ...
2021-02-06 47 6 5 12 ... x
Jacek Krzyżanowski
  • 141
  • 7
  • 1
    And what did you try so far? – astentx Apr 09 '21 at 14:28
  • I alredy count with WHERE-clause taking into consideration date range and status. But I have problem to put everything in one query that give me the reasult as stated in my post - each day separately nad each column representing different CODE. Is it even possible to make one like that? – Jacek Krzyżanowski Apr 09 '21 at 14:35
  • 1
    https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql – astentx Apr 09 '21 at 14:38

1 Answers1

1

I tried this solution, let me know if this helps. I filtered rows on your conditions Only for status "0" or "1" from Table B Only for records between 2020-11-30 and 2021-02-06 then I have a table with those codes which satisfy your condition and I used ``pivot``` to count distinct codes for each day and in a separate table, I counted the total codes of that day and joined these two tables to get the final output distinct_day, total_codes, code1_count, code2_count,...codez_count

With filtered_table as (select A.time_stamp time_stamp , A.code code from A inner join B
                        on(A.id = B.id_a)
                        where B.status in (0, 1)
                        and A.time_stamp between to_date('2020-11-30','yyyy-mm-dd')  and to_date('2021-02-06','yyyy-mm-dd')),
     pivot_table as (select * from ( select time_stamp t1, code from filtered_table)
                    pivot ( count(code) for code  in ( 'code_1', 'code_2', 'code_3', 'code_z') )),
     every_day_cnt as ( SELECT time_stamp, count(code) cnt from filtered_table GROUP BY time_stamp)
SELECT A.*, B.cnt from pivot_table  A inner join every_day_cnt B on (A.t1 = b.time_stamp);
Aman Singh Rajpoot
  • 1,451
  • 6
  • 26