CODEWITHSUNDEEP
sqloracle-database
mjanach
mjanach

Reputation: 85

How to select select max value of one column for each month using Oracle SQL

I want to make a report showing whether a project was completed in each month of the year. It doesn't matter how many project were completed, just whether or not at least on project was completed.

with project_data as(
select '123' account, 'Started' status, to_date('01/01/2017','MM/DD/YY') 
sale_date from dual union all
select '123' account, 'Complete' status, to_date('01/15/2017','MM/DD/YY') 
sale_date from dual union all
select '123' account, 'Started' status, to_date('02/01/2017','MM/DD/YY') 
sale_date from dual union all
select '123' account, 'Complete' status, to_date('04/12/2017','MM/DD/YY') 
sale_date from dual union all
select '123' account, 'Complete' status, to_date('04/16/2017','MM/DD/YY') 
sale_date from dual)

From this example I would like the output to be like:

Account Completed Month Year
123     Yes       1     2017
123     No        2     2017
123     No        3     2017
123     Yes       4     2017

Upvotes: 1

Views: 1014

Answers (2)

MT0
MT0

Reputation: 167981

Oracle Setup:

CREATE TABLE table_name ( account, status, sale_date ) AS
  select '123', 'Started',  DATE '2017-01-01' from dual union all
  select '123', 'Complete', DATE '2017-01-15' from dual union all
  select '123', 'Started',  DATE '2017-02-01' from dual union all
  select '123', 'Complete', DATE '2017-04-12' from dual union all
  select '123', 'Complete', DATE '2017-04-16' from dual union all
  select '456', 'Complete', DATE '2017-03-28' from dual;

Query:

SELECT ACCOUNT,
       MONTH,
       CASE COUNT( CASE status WHEN 'Complete' THEN 1 END )
         WHEN 0
         THEN 'No'
         ELSE 'Yes'
       END AS Completed
FROM   ( SELECT ADD_MONTHS( TRUNC( SYSDATE, 'YEAR' ), LEVEL - 1 ) AS month
         FROM   DUAL
         CONNECT BY ADD_MONTHS( TRUNC( SYSDATE, 'YEAR' ), LEVEL - 1 ) <= SYSDATE )
       LEFT OUTER JOIN
       table_name t
       PARTITION BY ( t.account )
       ON ( month <= sale_date AND sale_date < ADD_MONTHS( month, 1 ) )
GROUP BY Account, Month
ORDER BY Account, Month;

Output:

ACC MONTH               COM
--- ------------------- ---
123 2017-01-01 00:00:00 Yes
123 2017-02-01 00:00:00 No 
123 2017-03-01 00:00:00 No 
123 2017-04-01 00:00:00 Yes
456 2017-01-01 00:00:00 No 
456 2017-02-01 00:00:00 No 
456 2017-03-01 00:00:00 Yes
456 2017-04-01 00:00:00 No

Upvotes: 1

Mouad_Seridi
Mouad_Seridi

Reputation: 2716

SELECT YEAR,
       MONTH,
       ACCOUNT,
       CASE WHEN COMPLETED > 0 THEN 'YES' ELSE 'NO' END AS AT_LEAST_ONE_COMP
  FROM (  SELECT EXTRACT (YEAR FROM SALE_DATE) AS YEAR,
                 EXTRACT (MONTH FROM SALE_DATE) AS MONTH,
                 ACCOUNT,
                 SUM (CASE WHEN STATUS = 'Complete' THEN 1 ELSE 0 END)
                    AS completed
            FROM MY_TABLE
        GROUP BY EXTRACT (YEAR FROM SALE_DATE),
                 EXTRACT (MONTH FROM SALE_DATE), 
                 ACCOUNT)

Upvotes: 2

Related Questions