Create a SQL View Based Converting Ranges into Rows

Create a sql view based converting ranges into rows

You can accomplish this using a recursive CTE

CREATE TABLE ranges (
    ColA int,
    ColB char,
    LowRange int,
    HighRange int,
);

INSERT INTO ranges
VALUES (1, 'A', 1, 5),
(2, 'B', 5, 10);
GO

CREATE VIEW range_view
AS
WITH each AS
(
    SELECT ColA, ColB, LowRange AS n, HighRange
      FROM ranges
    UNION ALL
    SELECT ColA, ColB, n + 1, HighRange
      FROM each
     WHERE n + 1 <= HighRange
)
SELECT ColA, ColB, n
FROM each
GO

SELECT * FROM range_view
DROP VIEW range_view
DROP TABLE ranges;

SQL Convert each date range into each day row

You can use the technique described here, in order to generate a date range for each interval of your table. Then simply group by Cname and date to get the desired result set:

;WITH natural AS 
(
  SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 AS val 
  FROM sys.all_objects
) 
SELECT m.Cname, d = DATEADD(DAY, natural.val, m.StartDate), 
       SUM(value) AS  value
FROM mytable AS m 
INNER JOIN natural ON natural.val <= DATEDIFF(DAY, m.StartDate, m.EndDate)
GROUP BY Cname, DATEADD(DAY, natural.val, m.StartDate)
ORDER BY Cname, d

The CTE is used to create a tally table. The numbers of this table are then used to add 1,2,3, ... days to StartDate until EndDate is reached.

If you group by Cname, [Date], then SUM will return the required value since it will add any overlapping records within each Cname partition.

SQL Fiddle Demo

SQL SELECT Convert Min/Max into Separate Rows

If you expect just a handful of rows per foobar, then this is a good opportunity to learn about recursive CTEs:

with cte as (
      select foobar_id, min_period as period_num, max_period
      from original t
      union all
      select foobar_id, min_period + 1 as period_num, max_period
      from cte
      where period_num < max_period
    )
select foobar_id, period_num
from cte
order by foobar_id, period_num;

You can extend this to any number of periods by setting the MAXRECURSION option to 0.

How to create rows based on the range of all values between min and max in Snowflake (SQL)?

Sample data:

CREATE OR REPLACE TABLE T1 (
    ID INT,
    T_Min INT,
    T_Max INT);

INSERT INTO T1(ID, T_Min, T_Max)
SELECT * FROM VALUES (1, 3, 5), (2, 1, 4) t(ID, T_Min, T_Max);

Solution:

WITH N AS (
  SELECT ROW_NUMBER() OVER(ORDER BY SEQ4()) AS T FROM TABLE(GENERATOR(ROWCOUNT => 1000)) -- Set to the maximum value of the difference between T_Max and T_Min
)
SELECT T1.ID, N.T
  FROM T1
  JOIN N ON N.T BETWEEN T1.T_Min AND T1.T_Max
 ORDER BY T1.ID, N.T;

How to split the time range into multiple rows

Here is a recursive CTE solution:

with cte as (
    select 
        employeecode, 
        startdatetime, 
        dateadd(hour, 1, datetimefromparts(year(startdatetime), month(startdatetime), day(startdatetime), datepart(hour, startdatetime), 0, 0, 0)) enddatetime
        enddatetime maxdatetime
    from mytable
    union all
    select employeecode, enddatetime, dateadd(hour, 1, enddatetime), maxdatetime
    from cte
    where enddatetime < maxdatetime
)
select employeecode, startdatetime, 
    case when enddatetime < maxdatetime then enddatetime else maxdatetime end as enddatetime
from cte

Basically, the anchor of the CTE performs computes the end of the first range, using datetimefrompart(). Then we iteratively generate the following ranges, until the maximum date time is reached. We can then display the results with the outer query, while adjusting the end date of the last range.

SQL how to convert row with date range to many rows with each date

Using some sample data...

create table data (begindate datetime, enddate datetime, data int);
insert data select 
 '20130101', '20130104', 7 union all select
 '20130105', '20130106', 9;

The Query: (Note: if you already have a numbers/tally table - use it)

select dateadd(d,v.number,d.begindate) adate, data
  from data d
  join master..spt_values v on v.type='P'
       and v.number between 0 and datediff(d, begindate, enddate)
order by adate;

Results:

|                       COLUMN_0 | DATA |
-----------------------------------------
| January, 01 2013 00:00:00+0000 |    7 |
| January, 02 2013 00:00:00+0000 |    7 |
| January, 03 2013 00:00:00+0000 |    7 |
| January, 04 2013 00:00:00+0000 |    7 |
| January, 05 2013 00:00:00+0000 |    9 |
| January, 06 2013 00:00:00+0000 |    9 |

---

Alternatively you can generate a number table on the fly (0-99) or as many numbers as you need

;WITH Numbers(number) AS (
  select top(100) row_number() over (order by (select 0))-1
  from sys.columns a
  cross join sys.columns b
  cross join sys.columns c
  cross join sys.columns d
  )
select dateadd(d,v.number,d.begindate) adate, data
  from data d
  join Numbers v on v.number between 0 and datediff(d, begindate, enddate)
order by adate;

SQL Fiddle Demo

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

Postgres - How to convert row with an int range into intermediate rows from individual values from that range?

Use generate_series():

select gs.i, t.*
from t cross join lateral
     generate_series(start_i, end_i, 1) gs(i);

Strictly speak, the lateral is not needed. But it does explain what is going on. I should also note that you can also do:

select generate_series(start_i, end_i) as i, t.*
from t;

However, generate_series() affects the number of rows in the query. I am uncomfortable with having such effects in the SELECT clause.

---

Related Topics