I have the following table:
| jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 322 | 44 | 1222 |
These are invoice amount columns. They’re based off of the months of the year. What I’m trying to do is make a new column called execution window that will have different quarter values depending on where the amounts show up in the months. So basically:
Q1,
Q2,
Q3,
Q4,
Q1,Q2,
Q1, Q3, Q4,
Q2, Q3,
Q2, Q4
This is what I have so far but it doesn’t work for every scenario:
CASE
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL AND forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q2'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL AND forecast_july IS NOT NULL OR forecast_august IS NOT NULL OR forecast_september IS NOT NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q3'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NOT NULL OR forecast_november IS NOT NULL OR forecast_december IS NOT NULL THEN 'Q4'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NOT NULL AND forecast_may IS NULL AND forecast_june IS NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q2'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NULL AND forecast_may IS NOT NULL AND forecast_june IS NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q2'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q2'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NOT NULL AND forecast_may IS NOT NULL AND forecast_june IS NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q2'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NOT NULL AND forecast_may IS NULL AND forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q2'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NULL AND forecast_may IS NOT NULL AND forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q2'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NOT NULL AND forecast_may IS NOT NULL AND forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q2'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL AND forecast_july IS NOT NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q3'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL AND forecast_july IS NULL AND forecast_august IS NOT NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q3'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NOT NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q3'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL AND forecast_july IS NOT NULL AND forecast_august IS NOT NULL AND forecast_september IS NOT NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q3'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL AND forecast_july IS NOT NULL AND forecast_august IS NULL AND forecast_september IS NOT NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q3'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL AND forecast_july IS NULL AND forecast_august IS NOT NULL AND forecast_september IS NOT NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q3'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL AND forecast_july IS NOT NULL AND forecast_august IS NOT NULL AND forecast_september IS NOT NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q3'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL OR forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NOT NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q2,Q3'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL OR forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NOT NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q2,Q3'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL OR forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NOT NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q2,Q3'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL AND forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NOT NULL AND forecast_august IS NOT NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q2,Q3'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL OR forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NOT NULL AND forecast_august IS NULL AND forecast_september IS NOT NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q2,Q3'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL OR forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NOT NULL AND forecast_september IS NOT NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q2,Q3'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL OR forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NOT NULL AND forecast_august IS NOT NULL AND forecast_september IS NOT NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q2,Q3'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL OR forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NOT NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q2,Q4'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL OR forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NOT NULL AND forecast_december IS NULL THEN 'Q2,Q4'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL OR forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NOT NULL THEN 'Q2,Q4'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL OR forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NOT NULL AND forecast_november IS NOT NULL AND forecast_december IS NULL THEN 'Q2,Q4'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL OR forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NOT NULL AND forecast_november IS NULL AND forecast_december IS NOT NULL THEN 'Q2,Q4'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL OR forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NOT NULL AND forecast_december IS NOT NULL THEN 'Q2,Q4'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL OR forecast_april IS NOT NULL OR forecast_may IS NOT NULL OR forecast_june IS NOT NULL AND forecast_july IS NULL AND forecast_august IS NULL AND forecast_september IS NULL AND forecast_october IS NOT NULL AND forecast_november IS NOT NULL AND forecast_december IS NOT NULL THEN 'Q2,Q4'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL OR forecast_july IS NOT NULL OR forecast_august IS NOT NULL OR forecast_september IS NOT NULL AND forecast_october IS NOT NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q3,Q4'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL OR forecast_july IS NOT NULL OR forecast_august IS NOT NULL OR forecast_september IS NOT NULL AND forecast_october IS NULL AND forecast_november IS NOT NULL AND forecast_december IS NULL THEN 'Q3,Q4'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL OR forecast_july IS NOT NULL OR forecast_august IS NOT NULL OR forecast_september IS NOT NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NOT NULL THEN 'Q3,Q4'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL OR forecast_july IS NOT NULL OR forecast_august IS NOT NULL OR forecast_september IS NOT NULL AND forecast_october IS NOT NULL AND forecast_november IS NOT NULL AND forecast_december IS NOT NULL THEN 'Q3,Q4'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL OR forecast_july IS NOT NULL OR forecast_august IS NOT NULL OR forecast_september IS NOT NULL AND forecast_october IS NOT NULL AND forecast_november IS NULL AND forecast_december IS NOT NULL THEN 'Q3,Q4'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL OR forecast_july IS NOT NULL OR forecast_august IS NOT NULL OR forecast_september IS NOT NULL AND forecast_october IS NULL AND forecast_november IS NOT NULL AND forecast_december IS NOT NULL THEN 'Q3,Q4'
WHEN forecast_january IS NULL AND forecast_february IS NULL AND forecast_march IS NULL AND forecast_april IS NULL AND forecast_may IS NULL AND forecast_june IS NULL OR forecast_july IS NOT NULL OR forecast_august IS NOT NULL OR forecast_september IS NOT NULL AND forecast_october IS NOT NULL AND forecast_november IS NOT NULL AND forecast_december IS NOT NULL THEN 'Q3,Q4'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NOT NULL AND forecast_may IS NOT NULL AND forecast_june IS NOT NULL AND forecast_july IS NOT NULL AND forecast_august IS NOT NULL AND forecast_september IS NULL AND forecast_october IS NOT NULL AND forecast_november IS NULL AND forecast_december IS NULL THEN 'Q1,Q4'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NOT NULL AND forecast_may IS NOT NULL AND forecast_june IS NOT NULL AND forecast_july IS NOT NULL AND forecast_august IS NOT NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NOT NULL AND forecast_december IS NULL THEN 'Q1,Q4'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NOT NULL AND forecast_may IS NOT NULL AND forecast_june IS NOT NULL AND forecast_july IS NOT NULL AND forecast_august IS NOT NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NULL AND forecast_december IS NOT NULL THEN 'Q1,Q4'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NOT NULL AND forecast_may IS NOT NULL AND forecast_june IS NOT NULL AND forecast_july IS NOT NULL AND forecast_august IS NOT NULL AND forecast_september IS NULL AND forecast_october IS NOT NULL AND forecast_november IS NOT NULL AND forecast_december IS NOT NULL THEN 'Q1,Q4'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NOT NULL AND forecast_may IS NOT NULL AND forecast_june IS NOT NULL AND forecast_july IS NOT NULL AND forecast_august IS NOT NULL AND forecast_september IS NULL AND forecast_october IS NOT NULL AND forecast_november IS NULL AND forecast_december IS NOT NULL THEN 'Q1,Q4'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NOT NULL AND forecast_may IS NOT NULL AND forecast_june IS NOT NULL AND forecast_july IS NOT NULL AND forecast_august IS NOT NULL AND forecast_september IS NULL AND forecast_october IS NULL AND forecast_november IS NOT NULL AND forecast_december IS NOT NULL THEN 'Q1,Q4'
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NOT NULL AND forecast_may IS NOT NULL AND forecast_june IS NOT NULL AND forecast_july IS NOT NULL AND forecast_august IS NOT NULL AND forecast_september IS NULL AND forecast_october IS NOT NULL AND forecast_november IS NOT NULL AND forecast_december IS NOT NULL THEN 'Q1,Q4'
ELSE NULL
END AS new_execution_window
WHEN forecast_january IS NOT NULL OR forecast_february IS NOT NULL OR forecast_march IS NOT NULL AND forecast_april IS NOT NULL AND forecast_may IS NULL AND forecast_june IS NULL AND forecast_july IS NOT NULL AND forecast_august IS NOT NULL AND forecast_september IS NOT NULL AND forecast_october IS NOT NULL AND forecast_november IS NOT NULL AND forecast_december IS NOT NULL THEN 'Q1,Q2,Q4'
Is there an easier way to pull this off? For example, if an amount shows up in January, June, or november, I would expect to see ‘Q1, Q2, Q4’ for that particular row. I’m trying to account for all possible combinations.
Advertisement
Answer
If these amounts are always positive numbers, you can simply build the sum per quarter. Use COALESCE to avoid the sum will be NULL in case some columns have no amount:
SELECT CONCAT( CASE WHEN COALESCE(jan,0) + COALESCE(feb,0) + COALESCE(mar,0) > 0 THEN 'Q1,' ELSE '' END, CASE WHEN COALESCE(apr,0) + COALESCE(may,0) + COALESCE(jun,0) > 0 THEN 'Q2,' ELSE '' END, CASE WHEN COALESCE(jul,0) + COALESCE(aug,0) + COALESCE(sep,0) > 0 THEN 'Q3,' ELSE '' END, CASE WHEN COALESCE(oct,0) + COALESCE(nov,0) + COALESCE(dec,0) > 0 THEN 'Q4' ELSE '' END) AS yourcolumn FROM yourtable;
If the amounts can also be negative, furthermore use ABS to prevent the sum will be 0 for couples like 100,-100 etc.:
SELECT CONCAT( CASE WHEN COALESCE(ABS(jan),0) + COALESCE(ABS(feb),0) + COALESCE(ABS(mar),0) > 0 THEN 'Q1,' ELSE '' END, CASE WHEN COALESCE(ABS(apr),0) + COALESCE(ABS(may),0) + COALESCE(ABS(jun),0) > 0 THEN 'Q2,' ELSE '' END, CASE WHEN COALESCE(ABS(jul),0) + COALESCE(ABS(aug),0) + COALESCE(ABS(sep),0) > 0 THEN 'Q3,' ELSE '' END, CASE WHEN COALESCE(ABS(oct),0) + COALESCE(ABS(nov),0) + COALESCE(ABS(dec),0) > 0 THEN 'Q4,' ELSE '' END) AS yourcolumn FROM yourtable;
Whenever the sum of amounts of a quarter is > 0, this means at least one of the months has an amount.
And this means you write that quarter to your result column.
Try out: db<>fiddle
Sidenote 1: Some DBMS don’t provide this usage of CONCAT, you might need to replace that by the correct syntax of their string concatenation then.
Sidenote 2: The queries I’ve shown will of course produce results with a trailing comma after the last quarter which appeared if this quarter is not Q4. I guess you will be able to cut this off using your application or in SQL (the syntax for doing this in SQL depends on the DBMS again).
If you need assistance due to one or both of these two sidenotes, please tag your DBMS.