CODEWITHSUNDEEP
sqloracle-database
Nikhil
Nikhil

Reputation: 163

How to use max in with clause to get a single value and use this value in main query with null check

I am unable to write this, please help. Below will give an idea of what I'm trying to achieve.

WITH monthly_data AS
  (SELECT MAX(some_date) latest_dt FROM monthly_data
  )
SELECT SUM(data)
FROM daily_data
WHERE (monthly_data.latest_dt IS NULL
OR daily_data.some_date      > monthly_data.latest_dt)

table: monthly_data

id     some_date
007    08-MAY-2018

table: daily_data some_date data

07-MAY-2018     1
08-MAY-2018     1
09-MAY-2018     1

Expected result

Case 1: 1 row exist in table monthly_data.
Query should return 1.

Case 2: No rows exist in table montly_data.
Query should return 3.

The joins in the above query is incorrect but basically written to give you an idea of what I'm trying to do. Also, when I say no rows exist in table monthly_data, it is simplified explanation. There are other conditions in the actual query that filter out the data.

This has to go in a procedure

Edit Thanks to @D-Shih I'm in a much better position where I started by using the exist clause query that he has provided.

On performance terms, can we write it in a faster way? Something that can evaluate to below would be fastest I believe

WITH CTE AS
  ( SELECT MAX(some_date) latest_dt FROM monthly_data
  )
SELECT SUM(d.some_data)
FROM daily_data d
WHERE (d.some_date > '08-MAY-2018'
OR '08-MAY-2018'  IS NULL)

Upvotes: 1

Views: 737

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS: 

SELECT CASE WHEN SUM(CASE WHEN md.Sdate IS NOT NULL THEN 1 ELSE 0 END) > 0 THEN 
            SUM(CASE WHEN md.Sdate IS NOT NULL THEN 1 ELSE 0 END)
        ELSE 
            SUM(CASE WHEN md.Sdate IS NULL THEN 1 ELSE 0 END) 
        END cnt
FROM daily_data dd
LEFT JOIN monthly_data md ON md.Sdate = dd.Sdate
....... {other conditions}

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

If I understand correct.I think this will be work.

Due to you didn't provide some sample data and expect result.If that didn't your expect result,you can provide some sample data and expect result,I will edit my answer.

WITH CTE AS (
    SELECT Max(some_date) latest_dt 
    FROM   monthly_data
) 
SELECT Sum(d.data) 
FROM   daily_data d
WHERE Exists (
    SELECT 1 
    FROM CTE c
    WHERE  
        d.some_date > c.latest_dt 
    OR 
        c.latest_dt IS NULL 
)

Edit

You can try use CTE table JOIN on daily_data table 

WITH CTE AS (
    SELECT Max(some_date) latest_dt 
    FROM   monthly_data
) 
SELECT SUM(d.data)
FROM CTE c JOIN  daily_data d
ON  d.some_date > c.latest_dt OR c.latest_dt IS NULL;

sqlfiddle: http://sqlfiddle.com/#!4/33c64e/28

Upvotes: 1

Related Questions