Генерировать данные между двумя диапазонами дат по некоторым значениям

ProgramBox

Генерировать данные между двумя диапазонами дат по некоторым значениям

Post author:admin
Запись опубликована:8 февраля, 2023
Post category:Вопросы по программированию

#sql #sql-server

#sql #sql-сервер

Вопрос:

У меня есть 2 даты, StartDate и EndDate :

 Declare @StartDate date='2018/01/01', @Enddate date ='2018/12/31'

Затем в таблице mytable есть некоторые данные с date и value :

 ----------------------------
 ID    date          value
----------------------------
  1  2018/02/14      4
  2  2018/09/26      7
  3  2017/09/20      2

данные могут начинаться до 2018 года, и если они существуют до того, как @startdate получит до того, как значения
else получат 0
Я ищу, чтобы получить результат, который выглядит следующим образом:

 -----------------------------------
fromdate      todate       value
-----------------------------------
2018/01/01    2018/02/13     2
2018/02/14    2018/09/25     4
2018/09/26    2018/12/31     7

Первое fromdate происходит от @StartDate , а последнее todate — от @Enddate , а остальные данные должны быть сгенерированы.

Я надеюсь получить это в SQL-запросе. Я использую sql-server 2016

Ответ №1:

Вы можете использовать CTE для создания полного диапазона дат, а затем LEAD для создания ToDate столбца:

 DECLARE @FromDate date = '20180101',
        @ToDate date = '20181231';


WITH VTE AS(
    SELECT ID,
           CONVERT(date,[date]) [date], --This is why using keywords for column names is a bad idea
           [value]
    FROM (VALUES(1,'20180214',4),
                (2,'20180926',7),
                (3,'20170314',4))V(ID,[date],[value])),
Dates AS(
    SELECT [date]
    FROM VTE V
    WHERE V.[date] BETWEEN @FromDate and @ToDate
    UNION ALL
    SELECT [date]
    FROM (VALUES(@FromDate))V([date]))
SELECT D.[date] AS FromDate,
       LEAD(DATEADD(DAY, -1,D.[date]),1,@ToDate) OVER (ORDER BY D.[date]) AS ToDate,
       ISNULL(V.[value],0) AS [value]
FROM Dates D
     LEFT JOIN VTE V ON D.[date] = V.[date];

db<>скрипка

1. это приведет к неправильному результату, когда пользователь добавит значения до 2018 года, например (1,’20170314′,4) это приведет к неправильному результату

2. Вот почему полная выборка данных в important @Alavi; ни у одного из ваших данных нет значения со значением от 2017. Если у вас есть данные за 2017 или другие годы, вы должны включать образцы в свои данные. Я добавил еще одно предложение ON , которое должно работать, но без полных выборочных данных оно не тестируется.

3. Хорошо, теперь протестировано путем добавления более репрезентативных данных, @Alavi. Пожалуйста, помните, задавая вопросы в будущем, предоставлять данные, которые представляют имеющиеся у вас данные, а не ответ.

4. Большое спасибо, это здорово, просто немного неправильно в первых данных (01/01/2018 00:00:00 13/02/2018 00:00:00 0) значение первых данных равно 0, но должно быть 4 из-за этого (3,’20170314′,4) старое значение данных до @FromDate равно 4

5. @Alavi ваша первая строка в ваших ожидаемых результатах имеет значение 0 в вашем вопросе. Почему это 0 неправильно, это то, что у вас есть в вашем вопросе. Вам нужно исправить свой вопрос, прежде чем кто-либо сможет начать вам помогать.

Ответ №2:

 with cte as 
(
    select 0 as row_num, @StartDate as start_date, 0 as val
    UNION
    select ROW_NUMBER() OVER(ORDER BY start_date) as row_num, * from input
)
select curr.start_date 
  , DATEADD(day,-1,ISNULL(nex.start_date,DATEADD(day,1,@Enddate))) as end_date 
  , curr.val 
from cte curr
left join cte nex on curr.row_num = nex.row_num - 1;

Вы можете найти симуляцию здесь: https://rextester.com/EIAXW23839

1. Жаль, что этот ответ был слишком отклонен без комментариев; особенно когда OP потребовалось время, чтобы показать, что пример работает.

2. это приведет к неправильному результату, если наши данные начнутся до 2018 года следующим образом: вставить во входные значения (‘2017-02-14’,4),(‘2018-09-26’,7);

3. @Alavi моя точка зрения в приведенном ниже ответе остается в силе. если вы не предоставите нам данные за 2017 год, как мы узнаем, что они там есть? Мы не можем видеть ваши данные.

4. данные будут создаваться пользователями во время жизни приложения, возможно, они определяют до даты, я имею в виду, если нет данных до того, как значение должно быть 0, и если оно существует, получите значение

5. хотел бы знать, почему я получаю здесь голоса. Разве даты начала и окончания не означают, что данные будут находиться между ними? я неправильно понимаю здесь?