Benjamin Benjamin - 3 months ago 6
SQL Question

Set all values within a group to 0 when any value in the group is 0

I would like to scan a table and reassign values within a group to 0 if any of the values in that group is 0. I can't seem to figure out a solution to this by searching various combinations of

Group By
,
Partition
, and
Any
.

The data I am starting with looks like

CREATE TABLE #QP
(
[Method] VARCHAR(1),
[Station] VARCHAR(1),
[Instrument] VARCHAR(20),
[LastAnalysis] DATE,
[DaysPassed] INT
)

INSERT INTO #QP
(Method, Station, Instrument, LastAnalysis, DaysPassed)
VALUES
('A', 1, 'Polaris', '2016-07-19', 21),
('B', 1, 'Polaris', '2016-08-04', 5),
('C', 1, 'Polaris', '2016-07-31', 9),
('A', 2, 'Polaris', '2016-07-31', 9),
('B', 2, 'Polaris', '2016-08-09', 0),
('C', 2, 'Polaris', '2016-07-23', 17),
('A', 3, 'Polaris', '2016-08-09', 0),
('B', 3, 'Polaris', '2016-07-27', 13),
('C', 3, 'Polaris', '2016-07-19', 21)


And I would like the result to appear as (line breaks included for ease of interpretation)

Method Station Instrument LastAnalysis DaysPassed Weight
A 1 Polaris 2016-07-19 21 21
B 1 Polaris 2016-08-04 5 5
C 1 Polaris 2016-07-31 9 6

A 2 Polaris 2016-07-31 9 0
B 2 Polaris 2016-08-09 0 0
C 2 Polaris 2016-07-23 17 0

A 3 Polaris 2016-08-09 0 0
B 3 Polaris 2016-07-27 13 0
C 3 Polaris 2016-07-19 21 0


The closest I've gotten so far is to use

SELECT *,
CASE WHEN 0 = ANY(SELECT DaysPassed FROM #QP) THEN 0 ELSE DaysPassed END AS [Weight]
FROM #QP
WHERE Instrument = 'Polaris'
ORDER BY Station, Method


But this sets every value in the
Weight
column to 0, when the values in the
Station
group should remain as they are.

If this has an answer already, I'd love to know the right search terms to use to find it.

Answer

I think that using the min() window function will work. Give it a try:

SELECT *,
       case when min(DaysPassed) over (partition by station) = 0 then 0 else DaysPassed end as [Weight]
FROM #QP 
WHERE Instrument = 'Polaris'
ORDER BY Station, Method
Comments