select * from table1
where
(
(
--group1
amount_1 > 0
or
amount_2 > 0
or
amount_3 > 0
)
or
(
--group2
amount_4 > 0
or
amount_5 > 0
or
amount_6 > 0
)
)
or
amount_7 > 0
;
Above is a preliminary sql statement. I need to add a feature where to use at least three amounts from both groups or more, and at least one amount of each group (group1 and group2).
Example1: If group1 amounts1-3 are less than 0 then use amount_7.
Example2: If group1 amount_1 is more than zero and group2 amount_5 and amount_6 are more than zero then use amount_1, amount_5, amount_6.
Should I use some case-structure and give 1 and 0 when amount is bigger than 0 or how?
Amounts are pre-summed sales in this example, no need to sum anymore.
Answer is something like that, still testing....
select * from table1
where
(
(
--group1
case when
(case when amount_1 > 0 then 1 else 0 end)
+
(case when amount_2 > 0 then 1 else 0 end)
+
(case when amount_3 > 0 then 1 else 0 end)
> 0
then
(
--group1
amount_1 > 0
or
amount_2 > 0
or
amount_3 > 0
)
else null end
)
or
(
--group2
(case when amount_4 > 0 then 1 else 0 end)
+
(case when amount_5 > 0 then 1 else 0 end)
+
(case when amount_6 > 0 then 1 else 0 end)
> 0
then
(
--group2
amount_4 > 0
or
amount_5 > 0
or
amount_6 > 0
)
else null end
)
)
or
amount_7 > 0
;
or a simpler solution:
select * from table1
where
(
(
--group1
amount_1
+
amount_2
+
amount_3
>0
)
and
(
--group2
amount_4
+
amount_5
+
amount_6
>0
)
)
or
amount_7 > 0
;
