На основе дня выборки всех дат - sql

У меня есть дата начала, дата окончания и название дней. Как получить все даты между этими двумя датами этих конкретных дней в sql?

пример данных:

  • дата начала: 11.04.2018
  • end_date: 11.05.2018
  • дни: понедельник, четверг

ожидаемый результат: все даты между начальной и конечной датами, приходящимися на понедельник и четверг, и сохраните их в таблице

обновлено мой текущий код (не работает)

; WITH CTE(dt)
AS
(
      SELECT @P_FROM_DATE
      UNION ALL
      SELECT DATEADD(dw, 1, dt) FROM CTE
      WHERE dt < @P_TO_DATE
)
INSERT INTO Table_name 
(
    ID
    ,DATE_TIME
    ,STATUS
    ,CREATED_DATE
    ,CREATED_BY
)
SELECT @P_ID
       ,(SELECT  dt  FROM CTE WHERE DATENAME(dw, dt) In ('tuesday','friday',null))
       ,'NOT SENT'
       ,CAST(GETDATE() AS DATE)
       ,@USER_ID

Вам нужен календарный стол или компьютер для его производства.

jarlh 11.04.2018 13:13

Какой СУБД вы используете? «SQL» - это просто язык запросов, а не имя конкретного продукта базы данных. Добавьте тег для продукта базы данных, который вы используете postgresql, oracle, sql-server, db2, ...

a_horse_with_no_name 11.04.2018 13:13

Пожалуйста, редактировать свой вопрос и добавьте Пример данных и ожидаемый результат на основе этих данных. Форматированный текст пожалуйста, нет скриншотов. (редактировать ваш вопрос - сделайте нет почтовый индекс или доп информацию в комментариях)

a_horse_with_no_name 11.04.2018 13:13

я отредактировал свой вопрос

prasanna 11.04.2018 13:18

Возможно, это хорошая отправная точка: sqlservercentral.com/articles/calendar/145206

Larnu 11.04.2018 13:22
2
5
115
6
Перейти к ответу Данный вопрос помечен как решенный

Ответы 6

Это сработает для вас:

DECLARE @table TABLE(
ID INT IDENTITY(1,1),
Date DATETIME,
Day VARCHAR(50)
)
DECLARE @Days TABLE(
ID INT IDENTITY(1,1),
Day VARCHAR(50)
)

INSERT INTO @Days VALUES ('Monday')
INSERT INTO @Days VALUES ('Thursday')




DECLARE @StartDate DATETIME='2018-01-01';
DECLARE @EndDate DATETIME=GETDATE();


DECLARE @Day VARCHAR(50)='Friday';

DECLARE @TempDate DATETIME=@StartDate;

WHILE CAST(@TempDate AS DATE)<=CAST(@EndDate AS DATE)
BEGIN

    IF EXISTS (SELECT 1 FROM @Days WHERE DAY IN (DATENAME(dw,@TempDate))) 
    BEGIN
        INSERT INTO @table
        VALUES  ( 
                  @TempDate, -- Date - datetime
                  DATENAME(dw,@TempDate)  -- Day - varchar(50)
                  )
    END

    SET @TempDate=DATEADD(DAY,1,@TempDate)
END



SELECT * FROM @table
INSERT INTO TargetTab(dateCOL)    
SELECT dateCOL
    FROM tab
    WHERE dateCOL >= startdate AND dateCOL <= enddate 
    AND (DATENAME(dw,dateCOL) ='Thursday' OR DATENAME(dw,dateCOL) = 'Monday')

Попробуйте этот запрос, чтобы получить результат.

я не понял что такое dateCol

prasanna 11.04.2018 15:08

Замените его именем столбца даты вашей таблицы

Akshey Bhat 11.04.2018 15:09

Вы можете использовать рекурсивное общее табличное выражение (CTE) для создания списка дней. С помощью datepart(dw, ...) вы можете фильтровать по определенным дням недели.

Пример создания списка понедельников и четвергов с 1 марта по сегодняшний день:

create table ListOfDates (dt date);

with    cte as
        (
        select  cast('2018-03-01' as date) as dt  -- First day of interval
        union all
        select  dateadd(day, 1, dt)
        from    cte
        where   dt < getdate()  -- Last day of interval
        )
insert  into ListOfDates
        (dt)
select  dt
from    cte
where   datepart(dw, dt) in (2, 5)  -- 2=Monday and 5=Thursday
option  (maxrecursion 0)

Посмотрите, как он работает в SQL Fiddle.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. это ошибка, которую я получаю.
prasanna 11.04.2018 14:33

Не знаю, как это объяснить, приведенный выше запрос работает на SQL Fiddle!

Andomar 11.04.2018 15:56
create table ListOfDates (dt date, name varchar(20)); with cte as ( select cast('2018-03-01' as date) as dt union all select dateadd(day, 1, dt) from cte where dt < getdate() ) insert into ListOfDates (dt, name) select (select dt from cte where datepart(dw, dt) in (2, 5) ),-- Monday and Thursday 'hello' попробуйте то же самое на скрипке
prasanna 11.04.2018 16:07

Часть запроса select не может создавать несколько строк. Это должно быть сделано в части from. См. Здесь способ вставки имени и даты: sqlfiddle.com/#!18/66678/1/0

Andomar 11.04.2018 20:13

Используйте рекурсивный CTE для создания дат, а затем отфильтруйте их по дням недели.

SET DATEFIRST 1 -- 1: Monday, 7 Sunday

DECLARE @StartDate DATE = '2018-04-11'
DECLARE @EndDate DATE = '2018-05-15'

DECLARE @WeekDays TABLE (WeekDayNumber INT)

INSERT INTO @WeekDays (
    WeekDayNumber)
VALUES
    (1), -- Monday
    (4) -- Thursday

;WITH GeneratingDates AS
(
    SELECT
        GeneratedDate = @StartDate,
        WeekDay = DATEPART(WEEKDAY, @StartDate)

    UNION ALL

    SELECT
        GeneratedDate = DATEADD(DAY, 1, G.GeneratedDate),
        WeekDay = DATEPART(WEEKDAY, DATEADD(DAY, 1, G.GeneratedDate))
    FROM
        GeneratingDates AS G -- Notice that we are referencing a CTE that we are also declaring
    WHERE
        G.GeneratedDate < @EndDate
)
SELECT
    G.GeneratedDate
FROM
    GeneratingDates AS G
    INNER JOIN @WeekDays AS W ON G.WeekDay = W.WeekDayNumber
OPTION
    (MAXRECURSION 30000)

Другой подход для генерации дат между диапазонами может быть похож на следующий запрос. Это будет быстрее по сравнению с петлей CTE или WHILE.

DECLARE @StartDate DATETIME = '2018-04-11'
DECLARE @EndDate DATETIME = '2018-05-15'

SELECT @StartDate + RN AS DATE FROM
(   
    SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))-1 RN 
    FROM   master..[spt_values] T1
) T 
WHERE RN <= DATEDIFF(DAY,@StartDate,@EndDate)
AND DATENAME(dw,@StartDate + RN) IN('Monday','Thursday')

Примечание:

Если количество строк в master..[spt_values] недостаточно для указанного диапазона, вы можете выполнить перекрестное соединение с тем же, чтобы получить больший диапазон, как показано ниже.

SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))-1 RN 
    FROM   master..[spt_values] T1
    CROSS JOIN master..[spt_values] T2

Таким образом вы сможете сгенерировать дату в диапазоне с промежутком в 6436369 дней.

Пара замечаний по этому поводу: 1. spt_values ​​недокументирован. 2. Вы можете избавиться от самого внешнего запроса и сделать это предложение where в среднем запросе. 3. В остальном это довольно близко к тому, что я собирался опубликовать, так что +1.

Zohar Peled 11.04.2018 13:44

@ZoharPeled, спасибо за информацию, вы правы. Я удалил внешний выбор.

PSK 11.04.2018 13:47
Ответ принят как подходящий

Попробуй это:

declare @start date = '04-11-2018'
declare @end date = '05-11-2018'
declare @P_ID int = 1
declare @USER_ID int = 11
;with cte as(
    select @start [date]
    union all 
    select dateadd(DAY, 1, [date]) from cte
    where [date] < @end
)
--if MY_TABLE doesn't exist
select @P_ID,
       [date],
       'NOT SENT',
       cast(getdate() as date),
       @USER_ID
into MY_TABLE
from cte
--here you can specify days: 1 - Sunday, 2 - Monday, etc.
where DATEPART(dw,[date]) in (2, 5)
option (maxrecursion 0)

--if MY_TABLE does exist
--insert into MY_TABLE
--select @P_ID,
--       [date],
--     'NOT SENT',
--     cast(getdate() as date),
--     @USER_ID
--from cte
--where DATEPART(dw,[date]) in (2, 5)
--option (maxrecursion 0)

@prasanna Попробуйте обновленный запрос. Кроме того, вы можете удалить комментарии, поскольку они не имеют отношения к обсуждению (я удалил свои).

Michał Turczyn 11.04.2018 16:51

Другие вопросы по теме