For Each Day Between Two Dates, Add a Row with the Same Info But Only That Day in the Start/End Columns

For each day between two dates, add a row with the same info but only that day in the start/end columns

May be you need a Recursive CTE.

CREATE TABLE #dates(NAME  VARCHAR(50),START DATETIME,[END] DATETIME)

INSERT INTO #dates
VALUES      ('Bob','2014-10-30','2014-11-02')

DECLARE @maxdate DATETIME = (SELECT Max([end]) FROM   #dates);

WITH cte
     AS (SELECT NAME,
                START,
                [END]
         FROM   #dates
         UNION ALL
         SELECT NAME,
                Dateadd(day, 1, start),
                Dateadd(day, 1, start)
         FROM   cte
         WHERE  start < @maxdate)
SELECT *
FROM   cte 

OUTPUT :

name    START       END
----    ----------  ----------
Bob     2014-10-30  2014-10-30
Bob     2014-10-31  2014-10-31
Bob     2014-11-01  2014-11-01
Bob     2014-11-02  2014-11-02

Create a new row for each day between dates from two different columns in Redshift SQL

Redshift does not support recursive common table expressions.

One approach is to build a table of numbers:

create table nums(n int);
insert into nums values(0), (1), (2), (3), ...

You can then join the numbers table with the original table to generate the expected result:

select
    d.name,
    dateadd(day, n.n, d.start_date) start_date,
    d.end_date
from dates d
inner join nums n 
    on dateadd(day, n.n, d.start_date) <= d.end_date

You can also list the numbers as a derived table directly in your query, or use row_number() against a large table.

Create a row for each date in a range, and add 1 for each day within a date range for a record in SQL

You can join your table to a calendar table containing all the dates you need:

with calendar as
(select cast('2022-01-01' as datetime) as d
union all select dateadd(day, 1, d)
from calendar
where d < '2022-02-01')
select d as "Date", count(*) as NumberOfCustomers 
from calendar inner join table_name
on d between LiveDate and coalesce(ServiceEndDate, '9999-12-31')
group by d;

Fiddle

Pandas df, adding rows between start and end dates

Suppose your df can have multiple rows and we need to create new records for each day between both dates in each row, we can create a date range record for each row by pd.date_range and then expand the date range records to multiple rows (each day in one row) by .explode(), as follows:

df['DATE_VALUE'] = df.apply(lambda x: pd.date_range(start=x['START_DATE'], end=x['END_DATE']), axis=1)

df = df.explode('DATE_VALUE').reset_index(drop=True)

Result:

print(df)

   START_DATE   END_DATE  DAYS_BETWEEN       NAME  TOTAL_AMT DATE_VALUE
0   8/16/2021  8/28/2021            13  LOCATION1       1000 2021-08-16
1   8/16/2021  8/28/2021            13  LOCATION1       1000 2021-08-17
2   8/16/2021  8/28/2021            13  LOCATION1       1000 2021-08-18
3   8/16/2021  8/28/2021            13  LOCATION1       1000 2021-08-19
4   8/16/2021  8/28/2021            13  LOCATION1       1000 2021-08-20
5   8/16/2021  8/28/2021            13  LOCATION1       1000 2021-08-21
6   8/16/2021  8/28/2021            13  LOCATION1       1000 2021-08-22
7   8/16/2021  8/28/2021            13  LOCATION1       1000 2021-08-23
8   8/16/2021  8/28/2021            13  LOCATION1       1000 2021-08-24
9   8/16/2021  8/28/2021            13  LOCATION1       1000 2021-08-25
10  8/16/2021  8/28/2021            13  LOCATION1       1000 2021-08-26
11  8/16/2021  8/28/2021            13  LOCATION1       1000 2021-08-27
12  8/16/2021  8/28/2021            13  LOCATION1       1000 2021-08-28

Add a row for every day between two dates with number of hours in SQL Server

this might get tricky, but I guess it can be solved using so called number table - i.e. table which has only one column populated with number sequence. In our case 0 based sequence.

The trick here is to get the number of days between start and end datetime. This value used in join between the data table and the numbers table will create the needed extra rows for each per day interval.

Of course we also have to setup properly start and end datetime of each day interval (CASE terms in the CTE)

Then we get for each per day interval number of minutes and divide by 60 to get proper decimal value.

Hope this helps.

Lets see the code:

-- input data
DECLARE @v_Dates TABLE
(
    id varchar(20),
    StartDateTime SMALLDATETIME,
    EndDateTime SMALLDATETIME
)

INSERT INTO @v_Dates (id, StartDateTime, EndDateTime)
VALUES ('example 1', '02-17-2019 0:45', '02-19-19 12:30'),
('example 2', '02-21-2019 18:00', '02-22-19 12:15'),
('example 3', '02-22-2019 20:15', '02-22-19 20:30');

-- so called Number table which holds numbers 0 - 9999 in this case 
DECLARE @v_Numbers TABLE
(
  Number INT
);

-- populating the number table
INSERT INTO @v_Numbers
SELECT TOP 10000 ROW_NUMBER() OVER(ORDER by t1.number) - 1 as Number
FROM master..spt_values t1 
CROSS JOIN master..spt_values t2

-- we parse the dates into the per day intervals
;WITH IntervalsParsed(id, StartDateTime, EndDateTime, Number, IntervalStartDateTime, IntervalEndDateTime) AS
(
SELECT id
,StartDateTime
,EndDateTime
,Number
, InervalStartDateTime = CASE
                            WHEN D.StartDateTime > DATEADD(day, DATEDIFF(day, 0, D.StartDateTime), N.Number) THEN D.StartDateTime
                            ELSE DATEADD(day, DATEDIFF(day, 0, D.StartDateTime), N.Number)
                        END
, IntervalEndDateTime = CASE 
                            WHEN D.EndDateTime < DATEADD(day, DATEDIFF(day, 0, D.StartDateTime), N.Number + 1) THEN D.EndDateTime
                            ELSE DATEADD(day, DATEDIFF(day, 0, D.StartDateTime), N.Number + 1)
                        END

FROM @v_Dates D
--this join basically creates the needed number of rows 
INNER JOIN @v_Numbers N ON DATEDIFF(day, D.StartDateTime, D.EndDateTime) + 1 > N.Number
)
-- final select
SELECT id
, StartDateTime
, EndDateTime
, IntervalStartDateTime
, IntervalEndDateTime
, Number
, DecimalValue = CAST( DATEDIFF(minute, IntervalStartDateTime, IntervalEndDateTime) AS DECIMAL)/60

FROM IntervalsParsed
ORDER BY id, Number

generate days from date range

This solution uses no loops, procedures, or temp tables. The subquery generates dates for the last 10,000 days, and could be extended to go as far back or forward as you wish.

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a union all select 1 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) as a
    cross join (select 0 as a union all select 1 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) as b
    cross join (select 0 as a union all select 1 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) as c
    cross join (select 0 as a union all select 1 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) as d
) a
where a.Date between '2010-01-20' and '2010-01-24' 

Output:

Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20

Notes on Performance

Testing it out here, the performance is surprisingly good: the above query takes 0.0009 sec.

If we extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it runs in 0.0458 sec.

Incidentally, this is a very portable technique that works with most databases with minor adjustments.

SQL Fiddle example returning 1,000 days

Create a row for every day in a date range?

You could use a recursive CTE to generate the records in the "gaps". To avoid that an infinite gap after the last date is "filled", first get the maximum date in the source data and make sure not to bypass that date in the recursion.

I have called your table tbl:

with recursive cte as (
    select     id, 
               price, 
               date, 
               (select max(date) date from tbl) mx
    from       tbl
    union all
    select     cte.id,
               cte.price, 
               date_add(cte.date, interval 1 day),
               cte.mx
    from       cte
    left join  tbl
           on  tbl.date = date_add(cte.date, interval 1 day)
    where      tbl.id is null
           and cte.date <> cte.mx
)
select   id, 
         price, 
         date
from     cte
order by 3;

demo with mysql 8

---

Related Topics