0

I have a table that shows an amount for a specific start and end date similar to this table:

ID |start_date  |end_date   |amount
---|------------|-----------|-------
a1 |2019-01-01  |2019-01-05 |100
---|------------|-----------|-------
a2 |2019-01-01  |2019-01-10 |100

And I'm trying to generate a table in SQL that divides out the amount by days between start and end date. For example:

ID |date        |splitamount   
---|------------|-------
a1 |2019-01-01  |20
---|------------|-------
a1 |2019-01-02  |20
---|------------|-------
a1 |2019-01-03  |20
---|------------|-------
a1 |2019-01-04  |20
---|------------|-------
a1 |2019-01-05  |20
---|------------|-------
a2 |2019-01-01  |10
---|------------|-------
a2 |2019-01-02  |10
---|------------|-------
a2 |2019-01-03  |10
---|------------|-------
a2 |2019-01-04  |10
---|------------|-------
a2 |2019-01-05  |10
---|------------|-------
a2 |2019-01-06  |10
---|------------|-------
a2 |2019-01-07  |10
---|------------|-------
a2 |2019-01-08  |10
---|------------|-------
a2 |2019-01-09  |10
---|------------|-------
a2 |2019-01-10  |10
John McCoy
  • 45
  • 1
  • 7

4 Answers4

1

You can do it for example like this:

SELECT
t.*,
t.amount / (TIMESTAMPDIFF(DAY, t.start_date, t.end_date) + 1) AS splitamount
FROM
t
JOIN (
  SELECT 1 AS n UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4 UNION ALL
  SELECT 5 UNION ALL
  SELECT 6 UNION ALL
  SELECT 7 UNION ALL
  SELECT 8 UNION ALL
  SELECT 9 UNION ALL
  SELECT 10 UNION ALL
  SELECT 11
  ) numbers ON numbers.n <= TIMESTAMPDIFF(DAY, t.start_date, t.end_date) + 1

If you use a DBMS which supports recursive CTEs (for example MySQL 8.0), you can do it like this:

WITH RECURSIVE numbers (n) AS 
(
  SELECT 1 
  UNION
  SELECT n + 1 FROM numbers WHERE n <= 11 /* specify the amount of numbers you need here */
)
SELECT
t.*,
t.amount / (TIMESTAMPDIFF(DAY, t.start_date, t.end_date) + 1) AS splitamount,
t.start_date + INTERVAL n DAY AS `day`
FROM
t
JOIN numbers ON numbers.n <= TIMESTAMPDIFF(DAY, t.start_date, t.end_date) + 1
  • see it working live here
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • Any way to generate a new column that has the date split? Similar to what @erwin-brandstetter did? https://dbfiddle.uk/?rdbms=postgres_11&fiddle=1d9c2a2329d45b24653200bd49801e63 – John McCoy Jan 23 '19 at 23:18
  • @JohnMcCoy Of course, I added that column in my edited answer (the MySQL 8.0 solution). – fancyPants Jan 24 '19 at 07:52
0

You probally need to test rounding errors but this should be close.

DECLARE @T TABLE(ID INT, start_date DATETIME, end_date DATETIME, amount DECIMAL(18,2))
INSERT @T VALUES (1,'01/01/2019','01/05/2019',100),(2,'01/01/2019','01/10/2019',100)

;WITH R1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
   R2(N) AS (SELECT 1 FROM R1 a, R1 b),
   R3(N) AS (SELECT 1 FROM R2 a, R2 b), 
   Tally(Number) AS (SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM R3
)
SELECT ID,
    date=DATEADD(DAY,T.Number,start_date)-1,
    amounttospit = CASE WHEN  DATEDIFF(DAY,start_date,end_date) = 0 THEN amount ELSE amount / (DATEDIFF(DAY,start_date,end_date) + 1) END
FROM 
    @T 
    INNER JOIN Tally T ON T.Number BETWEEN 1 AND  DATEDIFF(DAY,start_date,end_date) +1
ORDER BY ID,DATEADD(DAY,T.Number,start_date)
Ross Bush
  • 14,648
  • 2
  • 32
  • 55
0

Use generate_series() in Postgres:

SELECT id, day, amount / days AS splitamount
FROM   (
   SELECT *, end_date - start_date + 1 AS days
        , generate_series(start_date, end_date, interval '1 day')::date AS day
   FROM tbl
   ) t;

db<>fiddle here

See:

Avoid an off-by-1 error when subtracting dates.
If amount is integer, be wary in integer division:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • This outputs exactly the way I need, but is there any way to do this in MySql? – John McCoy Jan 23 '19 at 23:12
  • MySQL does not have `generate_series()`. You need Postgres for this. (Unfortunate choice by DOMO if you ask me.) You can substitute with a (much more complex) rCTE, similar to what fancyPants provided. – Erwin Brandstetter Jan 23 '19 at 23:23
0

Try this:

-- Create a temporary table to store the generated data
CREATE TEMPORARY TABLE GeneratedData (
    IterationNumber INT NOT NULL,
    InnerLoopCount INT NOT NULL
);

-- Initialize the iteration counter
SET @iterationCounter = 0;

-- Start the outer loop to iterate through a range of values
WHILE @iterationCounter <= 10 DO
    -- Initialize the inner loop count for each iteration
    SET @innerLoopCount = 1;

    -- Start the inner loop to generate and insert data into the temporary table
    WHILE @innerLoopCount <= (@iterationCounter + 1) DO
        -- Insert values into the temporary table
        INSERT INTO GeneratedData (IterationNumber, InnerLoopCount) VALUES (@iterationCounter, @innerLoopCount);

        -- Increment the inner loop count
        SET @innerLoopCount = @innerLoopCount + 1;
    END WHILE;

    -- Increment the iteration counter
    SET @iterationCounter = @iterationCounter + 1;
END WHILE;

-- Retrieve data with split amounts and date intervals
SELECT 
    ID, 
    CASE 
        WHEN gd.InnerLoopCount = 1 THEN t.start_date
        WHEN DATEDIFF(t.start_date, t.end_date) = (gd.InnerLoopCount - 1) THEN t.end_date
        ELSE DATE_ADD(t.start_date, INTERVAL (gd.InnerLoopCount - 1) DAY)
    END AS date,
    t.amount / (DATEDIFF(t.start_date, t.end_date) + 1) AS splitamount
FROM Table t
-- Join with the generated data table to match iteration numbers
LEFT JOIN GeneratedData gd ON gd.IterationNumber = DATEDIFF(t.start_date, t.end_date);
Bala Bharathy
  • 1
  • 1