Using CASE WHEN to fetch value based on another column

user_12772637 0
Viewed 74

I have a table that contains data in the following format,

Date Metric_Type Metric_Value
01/01/2021 v1 1.5
01/01/2021 v1.2 7.2
01/01/2021 v1.3 1.85

NOTE: The different versions of the metric type represent updates to the metric during each quarter.

I want my query to lookup the metric value pertaining to a specific metric type depending upon the quarter in which the date falls on. For example, if the date is 01/01/2021 - I want my query to pull metric_value pertaining to v1 (which references 1st quarter).

Here is a sample of the query I tried

SELECT date, CASE WHEN (QUARTER(DATE) = 1 AND metric_type = 'v1') THEN metric_value ELSE 0
FROM table

My output looked like this

Date Metric_Value
01/01/2021 1.5
01/01/2021 0
01/01/2021 0

But the flaw in my logic is that it returns all three rows with only one row having the metric value that matches my case when condition. Ideally, I want my query to return only the first row as the result. Is there any way to accomplish this using SQL?

snowflake-cloud-data-platform sql
4 Answers

If I understand the question correctly, instead of this:

SELECT date, CASE WHEN (QUARTER(DATE) = 1 AND metric_type = 'v1') THEN metric_value ELSE 0
FROM table

You want this:

SELECT date, metric_value
FROM table
WHERE (QUARTER(DATE) = 1 AND metric_type = 'v1')

I'm only confused because this is basic SQL, but it seems to be exactly what the question wants.

user_1324380

If you have many rows, and you only want the "rows for the matching quarter"

Then you can do a WHERE with four clauses OR'ed together, or use a UNION. Myself I would go that latter route as, while the SQL is hard to read, it's easer for the engine to execute (that is if you are worried about performance).

Thus:

SELECT date, metric_value
FROM table
WHERE (
    (QUARTER(DATE) = 1 AND metric_type = 'v1') OR
    (QUARTER(DATE) = 2 AND metric_type = 'v1.2') OR
    (QUARTER(DATE) = 3 AND metric_type = 'v1.3') OR
    (QUARTER(DATE) = 4 AND metric_type = 'v1.4')
)

SELECT date, metric_value
FROM table
WHERE QUARTER(DATE) = 1 AND metric_type = 'v1' 
UNION ALL
SELECT date, metric_value
FROM table
WHERE QUARTER(DATE) = 2 AND metric_type = 'v1.2' 
UNION ALL
SELECT date, metric_value
FROM table
WHERE QUARTER(DATE) = 3 AND metric_type = 'v1.3' 
UNION ALL
SELECT date, metric_value
FROM table
WHERE QUARTER(DATE) = 4 AND metric_type = 'v1.4'
user_439920

use Row_Number() partition by Metric_Type and quarter

declare @tmp as table(Date date,    Metric_Type varchar(20),    Metric_Value numeric(14,2))
insert into @tmp
values
('01/01/2021'   ,'v1',  1.5)
,('01/02/2021'  ,'v1',  0.5)
,('05/01/2021'  ,'v1',  2.5)
,('05/02/2021'  ,'v1',  3.5)
,('01/01/2021'  ,'v1.2',    7.2)
,('01/01/2021'  ,'v1.3',    1.85)

select * 
from
(
select 
DatePart(Quarter,Date) Q
,Metric_Value
,Row_Number() over(partition by Metric_Type,DatePart(Quarter,Date) order by Metric_Type,DatePart(Quarter,Date)) as RowID
from @tmp
)x
where RowID=1

output

Q   Metric_Value    RowID
1   1.50    1
2   2.50    1
1   7.20    1
1   1.85    1
user_40011770

I think you just want to to map quarter to the version and filter based on mapping that matched. This should do it

select date, metric
from t
qualify quarter(date)= row_number() over (partition by date order by version)
user_99471590
Related