Skip to content
Advertisement

SQL: Creating quarter values based off conditional statements

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:

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:

If the amounts can also be negative, furthermore use ABS to prevent the sum will be 0 for couples like 100,-100 etc.:

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement