Calculated column in SQL based on multiple column matches and cumsum between two tables
I have two tables.
Table 1: has 4 columns as shown below.
| X | Y | A( in days) | B(sum) |
|---|---|---|---|
| a | aa | 7 | |
| a | bb | 9 | |
| b | aa | 36 | |
| c | dd | 29 |
Column X and Column Y are strings and Column A in days (int) and another column B that is blank and needs to be filled with sum of rows from Table 2,the number of rows to be summed is based on how days translates to week in column B.
Table 2 has following columns:
| X | Y | Week | C(weekly) |
|---|---|---|---|
| a | aa | WK1 | 10 |
| a | aa | WK2 | 23 |
| a | aa | WK3 | 21 |
| a | aa | WK4 | 2 |
| a | aa | Wk5 | 5 |
| a | bb | WK1 | 10 |
| a | bb | WK2 | 7 |
| a | bb | WK3 | 14 |
| b | aa | WK1 | 10 |
| b | aa | WK2 | 5 |
| b | aa | WK3 | 4 |
| b | aa | WK4 | 8 |
| b | aa | Wk5 | 7 |
| b | aa | Wk6 | 18 |
| b | aa | Wk7 | 3 |
| b | aa | WK8 | 7 |
| c | dd | WK1 | 10 |
| c | dd | WK2 | 5 |
| c | dd | WK3 | 7 |
| c | dd | WK4 | 14 |
| c | dd | WK5 | 7 |
| c | dd | WK4 | 21 |
Table 2 also has four columns. Column X and Column Y are also string and this table has Week numbers which is a datetime column (in YYYY-MM-DD) and weekly which is sum of weekly units.
I want to fill the values in column B of Table based on column C in table 2 like below:
For first row in table 1 when X=a and Y =aa and A=7 days then from table 2 find corresponding matching values of a and aa in columns X and Y and then use just WK1 values (As 7 days is 1 week so just WK1 of C from df2) so B=10 in table 1
For second row in table 1 when X=a and Y =bb and A=9 days then from table 2 find corresponding matching values of a and bb in columns X and Y and then use WK1 + (2/7)WK2 (As 7 days is 1 week plus 2 additional days i.e. a fraction (2/7th) of week 2 value so 10+7(2/7) giving B=12
For third row in table 1 when X=b and Y =aa and A=36 days then from table 2 find corresponding matching values of b and aa in columns X and Y and then use WK1+WK2+WK3+WK4+WK5+(1/7)WK6 (As 36 days is 5 weeks plus 1 additional day i.e. a fraction (1/7th) of week 6 value so 10+5+4+8+7+18(1/7) giving B=36.5
and so on.
Table 1 (Completed):
| X | Y | A( in days) | B(sum) | Methodology |
|---|---|---|---|---|
| a | aa | 7 | 10 | a-aa-WK1-10 |
| a | bb | 9 | 10+7*(2/7)=12 | a-bb- Wk1+(2/7)*WK2 |
| b | aa | 36 | 10+5+4+8+7+18*(1/7)=36.5 | b-aa-WK1+WK2+WK3+WK4+WK5+(1/7)*WK6 |
| c | dd | 29 | 10+5+7+14+(1/7)*7=37 | c-dd-WK1+WK2+Wk3+(1/7)*WK4 |
I do not have the required level of SQL knowledge to get this task started.
1
You haven’t specified which database you use, so I use Snowflake in my example. But the difference in syntax would be minor.
Here is the approach:
- Calculate whole and reminder parts of table1.a column
- Convert table2.week string to integer
- Calculate cumulative sum
- Join tables by the week (note that join is made by + 1)
WITH t1 AS (
SELECT
x,
y,
a,
FLOOR(a, 7) AS weeks_whole,
a % 7 AS week_reminder
FROM table1
),
t2 AS (
SELECT
x,
y,
week,
CAST(REPLACE(week, 'WK', '') AS INTEGER) AS week_num,
c,
SUM(c) OVER (PARTITION BY x, y ORDER BY week_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS c_cumulative
FROM table2
)
SELECT
t1.x,
t1.y,
t1.a,
t2.c_cumulative - t2.c + t2.c * (t1.week_reminder / 7)
FROM t1
JOIN t2
ON t2.x = t1.x
AND t2.y = y1.y
AND t2.week_num = t1.weeks_whole + 1
2
Tried and tested in Oracle 21C,
SELECT OUTER_TABLE.X,OUTER_TABLE.Y,OUTER_TABLE.A,
CASE WHEN NEXTWEEKDAYS =0 THEN
FLOOR((SELECT SUM(Table2.C) FROM Table2
WHERE OUTER_TABLE.X=Table2.X
AND OUTER_TABLE.Y=Table2.Y
AND OUTER_TABLE.WEEK_NUM>=(SUBSTR(Table2.Week, 3))
))
ELSE
FLOOR((SELECT SUM(Table2.C) FROM Table2
WHERE OUTER_TABLE.X=Table2.X
AND OUTER_TABLE.Y=Table2.Y
AND OUTER_TABLE.WEEK_NUM>=(SUBSTR(Table2.Week, 3))
)
+
((
SELECT Table2.C FROM Table2
WHERE OUTER_TABLE.X=Table2.X
AND OUTER_TABLE.Y=Table2.Y
AND UPPER(OUTER_TABLE.NEXTWEEK)=UPPER(Table2.WEEK)
)
*(OUTER_TABLE.NEXTWEEKDAYS/7)
))
END AS SUM
FROM
(SELECT DISTINCT Table1.X,Table1.Y, Table1.A, Table1.A/7 AS WEEK_NUM, MOD(Table1.A,7) NEXTWEEKDAYS,'WK'||CEIL((Table1.A/7)) AS NEXTWEEK
FROM Table1, Table2
WHERE Table1.X=Table2.X
AND Table1.Y=Table2.Y
)OUTER_TABLE
You can check for the sample tables and output here fiddle