Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Consolidate 2 Aggregate Queries

Is there anyway to combine the 2 queries below without temp tables, nested subqueries, etc?

--THE DATA:

ID   Value    CreateDT    UpdateDT
--------------------------------------
1    A        1/20/2023   1/21/2023
2    A        1/15/2023   1/26/2023
3    A        1/1/2023    1/2/2023
4    B        3/1/2023    3/8/2023 
5    C        2/10/2023   2/23/2023
6    C        2/20/2023   2/21/2023
7    C        2/1/2023    2/8/2023
8    D        4/1/2023    4/8/2023 

--WHAT I HAVE SO FAR:

--Query 1
SELECT Value, MAX(CreateDT) CreateDT
FROM SampleTbl
WHERE Value IN (A, X)
GROUP BY Value

--Query 2
SELECT Value, MAX(UpdateDT) UpdateDT
FROM SampleTbl
WHERE Value IN (C, Y)
GROUP BY Value

    
--DESIRED RESULT:

Value    CreateDT     UpdateDT
--------------------------------------
A        1/20/2023     1/26/2023
C        2/20/2023     2/23/2023

Thanks in advance!

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

Use conditional aggregation:

SELECT Value, 
       MAX(CASE WHEN Value IN ('A', 'X') THEN CreateDT END) CreateDT,
       MAX(CASE WHEN Value IN ('C', 'Y') THEN UpdateDT END) UpdateDT
FROM SampleTbl
WHERE Value IN ('A','C','X','Y')
GROUP BY Value

If A,C,X,Y are the only possible values, you can omit the final WHERE clause.

Output:

Value CreateDT UpdateDT
A 2023-01-20 null
C null 2023-02-23

Check the demo here.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading