Change data from aggregated to granular level-CodePudding

My data is in the form:

To reproduce:

    DROP TABLE IF EXISTS SALARY;
    CREATE TEMP TABLE salary
    (
     Employee varchar(100),
     Salary1 numeric(38,12),
     Salary2 numeric(38,12)
    );
   INSERT INTO salary (Employee, Salary1 ,Salary2)
   VALUES ('A1',100,300),('A2',200,300),('A3',300,450),('A4',400,600);

I want to divide it evenly (as we have data for 2 days of salary aggregated into 1 column) and cast it into a daily level data as below:

Hence, if you see for employee A2-Sum of salary for 3rd and 4th may would be 300 (150 150 from the 2nd table). Any help/leads appreciated.

CodePudding user response：

A materialized calendar table with the desired dates will facilitate generating the dates needed for the query. Without one, a tally table or CTE (as in the below example) is an alternative method.

DECLARE
      @StartDate date = '2022-05-01'
    , @DaysPerSalary int = 2;
WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num  FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,salary1 AS (
        SELECT Employee, Salary1 / @DaysPerSalary AS Salary
        FROM SALARY
    )
    ,salary2 AS (
        SELECT Employee, Salary2 / @DaysPerSalary AS Salary
        FROM SALARY
    )
SELECT DATEADD(day, tally.num-1, @StartDate), Employee, Salary
FROM tally
CROSS JOIN salary1
WHERE tally.num <= @DaysPerSalary
UNION ALL
SELECT DATEADD(day, tally.num-1   @DaysPerSalary, @StartDate), Employee, Salary
FROM tally
CROSS JOIN salary2
WHERE tally.num <= @DaysPerSalary
ORDER BY Employee, Salary;