CODEWITHSUNDEEP
sqlcumulative-sum
One_more_time
One_more_time

Reputation: 35

Calculated column in SQL based on multiple column matches between two tables and cumsum

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.

Upvotes: 0

Views: 57

Answers (2)

Punith Gubbi
Punith Gubbi

Reputation: 692

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

Upvotes: 0

Kliment Merzlyakov
Kliment Merzlyakov

Reputation: 1083

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:

  1. Calculate whole and reminder parts of table1.a column
  2. Convert table2.week string to integer
  3. Calculate cumulative sum
  4. 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

Upvotes: 1

Related Questions