SQL- split single row into multiple based on date columns-CodePudding

Matthew earns $600 in three days. And each day how he should earn should be split into three different rows.

RDBMS is SQL Server.

id  name    start_date  end_date     Total_Dollars
---------------------------------------------------
1   Mathew  01/01/2021  03/01/2021   600

Output should be

id  name    start_date  end_date     Total_Dollars
--------------------------------------------------
1   Rahul   01/01/2021  01/01/2021   200
1   Rahul   02/01/2021  02/01/2021   200
1   Rahul   03/01/2021  03/01/2021   200

CodePudding user response：

If you have a calendar table, use that:

WITH                                                                                                                                                                                                                                       
-- need a calendar table with one row per calendar date
cal (dt) AS (
          SELECT DATE '2021-01-01'
UNION ALL SELECT DATE '2021-01-02'
UNION ALL SELECT DATE '2021-01-03'
UNION ALL SELECT DATE '2021-01-04'
UNION ALL SELECT DATE '2021-01-05'
UNION ALL SELECT DATE '2021-01-06'
UNION ALL SELECT DATE '2021-01-07'
)
,
-- your input ...
indata(id,nam,start_date,end_date,total_dollars) AS (
          SELECT 1,'Mathew',DATE '2021-01-01',DATE '2021-01-03',600
)
-- real query starts here, replace following comma with "WITH" ...
,
daycount(daycount) AS (
  SELECT COUNT(*) FROM cal JOIN indata ON dt BETWEEN start_date AND end_date
)
SELECT
  id
, nam
, dt AS start_date
, dt AS end_date
, total_dollars / daycount AS total_dollars
FROM cal
JOIN indata ON dt BETWEEN start_date AND end_date
CROSS JOIN daycount;
-- out  id |  nam   | start_date |  end_date  | total_dollars 
-- out ---- -------- ------------ ------------ ---------------
-- out   1 | Mathew | 2021-01-01 | 2021-01-01 |           200
-- out   1 | Mathew | 2021-01-02 | 2021-01-02 |           200
-- out   1 | Mathew | 2021-01-03 | 2021-01-03 |           200

Or, also:

SELECT
  id
, nam
, dt AS start_date
, dt AS end_date
, total_dollars // count(*) OVER(PARTITION BY id)  AS total_dollars
FROM cal
JOIN indata ON dt BETWEEN start_date AND end_date
-- out  id |  nam   | start_date |  end_date  | total_dollars 
-- out ---- -------- ------------ ------------ ---------------
-- out   1 | Mathew | 2021-01-01 | 2021-01-01 |           200
-- out   1 | Mathew | 2021-01-02 | 2021-01-02 |           200
-- out   1 | Mathew | 2021-01-03 | 2021-01-03 |           200

CodePudding user response：

You may use a recursive query as the following:

WITH CTE AS
  (
    SELECT id, name, start_date SDT, end_date, Total_Dolllars
    FROM T
    UNION ALL
    SELECT id, name, DATEADD(DAY, 1,SDT), end_date, Total_Dolllars
    FROM CTE
    WHERE DATEADD(DAY, 1,SDT) <= end_date
  )
SELECT id, name, SDT start_date, SDT end_date,
       Total_Dolllars *1.00 / COUNT(*) OVER (PARTITION BY id) Total_Dolllars
FROM CTE
ORDER BY ID, SDT;

See a demo.