Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
 Список дат между двумя заданными  [new]
Димкин
Member

Откуда:
Сообщений: 18
Подскажите, пожалуйста, как можно получить список всех дат, между двумя заданными.

К примеру, я задаю 2 даты: 28.06.2010 и 10.07.2010. Мне необходимо получить набор данных с датами:
28.06.2010
29.06.2010
30.06.2010
01.07.2010
...
10.07.2010

Это необходимо для составления отчетов - выбираем промежуток, в промежутке должны быть указаны все даты и события, если такие имеются в эти даты. Если кто может предложить другой вариант, кроме выбора полного перечня дат, а затем присоединения к ним данных - буду очень благодарен.
21 июл 10, 11:43    [9136361]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=316343&hl= , задача №3
21 июл 10, 11:47    [9136404]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
автор
в промежутке должны быть указаны все даты и события, если такие имеются в эти даты.


И зачем тут перечень всех дат, если за таковые не имеются события? Почему не хватает просто условия по диапазону?
21 июл 10, 11:51    [9136448]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
Димкин
Member

Откуда:
Сообщений: 18
pkarklin, необходимо отобразить отчет, в котором должен быть перечень всех рабочих дней и если есть события(выбираются из БД), то они должны быть туда включены.
21 июл 10, 12:27    [9136796]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
iljy
Member

Откуда:
Сообщений: 8711
Димкин
pkarklin, необходимо отобразить отчет, в котором должен быть перечень всех рабочих дней и если есть события(выбираются из БД), то они должны быть туда включены.

Если у вас есть такая штатная задача, то самым простым способом будет создать таблицу-календарь и выбирать из нее даты в нужном диапазоне.
21 июл 10, 12:32    [9136833]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
Димкин
Member

Откуда:
Сообщений: 18
iljy, В БД и так более 400 таблиц. Да и создавать таблицу с датами, учитывая, что дата это как никак константа, думаю неправильно.
21 июл 10, 12:47    [9136955]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37077
Димкин
iljy, В БД и так более 400 таблиц. Да и создавать таблицу с датами, учитывая, что дата это как никак константа, думаю неправильно.
Одной больше, одной меньше: какая разница? А за 50 лет там будет аж 18 тыщ записей.
21 июл 10, 12:50    [9136992]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Димкин
думаю неправильно.


Не думайте, создавайте... И будет правильно.
21 июл 10, 12:58    [9137080]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
Димкин
Member

Откуда:
Сообщений: 18
Спасибо, попробую.
21 июл 10, 13:09    [9137168]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
iljy
Member

Откуда:
Сообщений: 8711
Димкин
iljy, В БД и так более 400 таблиц. Да и создавать таблицу с датами, учитывая, что дата это как никак константа, думаю неправильно.

Тогда создайте универсальную таблицу с числами и по ней генерите даты с помощью DATEADD. Это самый быстрый способ, поверьте.
21 июл 10, 13:21    [9137286]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Димкин
создавать таблицу с датами, учитывая, что дата это как никак константа, думаю неправильно.
Димкин
необходимо отобразить отчет, в котором должен быть перечень всех рабочих дней
думаю, выделенное слово важно для принятия решения
21 июл 10, 13:28    [9137340]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
Anatoly Podgoretsky
Member

Откуда:
Сообщений: 62912
Димкин
pkarklin, необходимо отобразить отчет, в котором должен быть перечень всех рабочих дней и если есть события(выбираются из БД), то они должны быть туда включены.

Это уже совсем другой вопрос. Календарь как сказано твое решение, и на первый вопрос даст ответ и на второй тоже, и еще на некоторые, например о госпраздниках и воскресеньях.
21 июл 10, 13:41    [9137440]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
Димкин
Member

Откуда:
Сообщений: 18
Паганель, в базе есть таблица, в которой указаны все нерабочие дни, включая праздники. Поэтому учесть рабочие не составляет труда. Единственно что требовалось выбрать все даты из указанного диапазона, добавить к нему условие not exists из таблицы праздников и наложить на указанный диапазон события, которые выбираются из view.
21 июл 10, 14:08    [9137693]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
iljy
Member

Откуда:
Сообщений: 8711
Димкин
Паганель, в базе есть таблица, в которой указаны все нерабочие дни, включая праздники. Поэтому учесть рабочие не составляет труда. Единственно что требовалось выбрать все даты из указанного диапазона, добавить к нему условие not exists из таблицы праздников и наложить на указанный диапазон события, которые выбираются из view.

Так сделайте из нее полную таблицу с добавленым признаком - рабочий день или нет.
21 июл 10, 14:29    [9137934]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
Автору в помощь
Guest
Димкин,

Вот скрипт для создания простого календаря на 2010 год, может кому еще пригодится...
set nocount on
set dateformat dmy
set language russian

if object_id ('SPK_Простой_Табель_Календарь', 'u') is not null drop table SPK_Простой_Табель_Календарь
go

Create table SPK_Простой_Табель_Календарь (Data datetime, WorkHours int)
go

;with cte(num) as (
 select 0
 union all
 select num+1 from cte where num < 365
)
insert into SPK_Простой_Табель_Календарь (Data, WorkHours)
 select dateadd(dd, num, '01.01.2010'), 8 from cte option (maxrecursion 0)


-- Выходные и праздники
update SPK_Простой_Табель_Календарь
set WorkHours = 0 
where (Data between '01.01.2010' and '10.01.2010')
or Data in   ('16.01.2010', '17.01.2010', '23.01.2010', '24.01.2010', '30.01.2010', '31.01.2010', '06.02.2010', '07.02.2010', '13.02.2010',
'14.02.2010', '20.02.2010', '21.02.2010', '22.02.2010', '23.02.2010', '28.02.2010', '06.03.2010', '07.03.2010', '08.03.2010', '13.03.2010',
'14.03.2010', '20.03.2010', '21.03.2010', '27.03.2010', '28.03.2010', '03.04.2010', '04.04.2010', '10.04.2010', '11.04.2010', '17.04.2010',
'18.04.2010', '24.04.2010', '25.04.2010', '01.05.2010', '02.05.2010', '03.05.2010', '08.05.2010', '09.05.2010', '10.05.2010', '15.05.2010',
'16.05.2010', '22.05.2010', '23.05.2010', '29.05.2010', '30.05.2010', '05.06.2010', '06.06.2010', '12.06.2010', '13.06.2010', '14.06.2010',
'19.06.2010', '20.06.2010', '26.06.2010', '27.06.2010', '03.07.2010', '04.07.2010', '10.07.2010', '11.07.2010', '17.07.2010', '18.07.2010',
'24.07.2010', '25.07.2010', '31.07.2010', '01.08.2010', '07.08.2010', '08.08.2010', '14.08.2010', '15.08.2010', '21.08.2010', '22.08.2010',
'28.08.2010', '29.08.2010', '04.09.2010', '05.09.2010', '11.09.2010', '12.09.2010', '18.09.2010', '19.09.2010', '25.09.2010', '26.09.2010',
'02.10.2010', '03.10.2010', '09.10.2010', '10.10.2010', '16.10.2010', '17.10.2010', '23.10.2010', '24.10.2010', '30.10.2010', '31.10.2010',
'04.11.2010', '05.11.2010', '06.11.2010', '07.11.2010', '14.11.2010', '20.11.2010', '21.11.2010', '27.11.2010', '28.11.2010', '04.12.2010',
'05.12.2010', '11.12.2010', '12.12.2010', '18.12.2010', '19.12.2010', '25.12.2010', '26.12.2010')


-- Сам календарь в удобочитаемом виде
select convert(varchar, Data, 104) [Дата], WorkHours from SPK_Простой_Табель_Календарь order by Data


/******************** Дополнительно ********************/
-- Проверка на кол-во рабочих дней
select * from
(
 select isnull (isnull(datename(mm, Data), cast(datepart(qq, Data) as varchar(20)) + '-й квартал'), 'Год') [Месяц],  count(Data) [Раб. дни]
 from SPK_Простой_Табель_Календарь 
 where WorkHours <> 0 group by datename(mm, Data), datepart(qq, Data)
 with cube having (grouping(datepart(qq, Data)) = 0)
 or (grouping(datename(mm, Data)) = 1)
) as t
pivot (sum([Раб. дни]) for [Месяц] in ([январь], [февраль], [март], [1-й квартал], [апрель], [май], [июнь], [2-й квартал], [июль], [август], [сентябрь],
[3-й квартал], [октябрь], [ноябрь], [декабрь], [4-й квартал], [Год])) as piv


-- Список выходных и праздничных дней
select convert(varchar, Data, 104) [Выходные и праздники] from SPK_Простой_Табель_Календарь where WorkHours = 0
/*********************************************************/
22 июл 10, 14:01    [9143884]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
Автору в помощь
Guest
Sorry, небольшая поправка,
вместо
select num+1 from cte where num < 365
надо написать
select num+1 from cte where num < 364
22 июл 10, 14:04    [9143921]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
ksn007
Member

Откуда:
Сообщений: 69
CREATE FUNCTION rep.DateList(@from DATE, @till DATE)
RETURNS @list TABLE (dt DATE)
BEGIN
	WHILE @from <= @till BEGIN
		INSERT @list(dt) VALUES(@from)
		
		SET @from = DATEADD(day, 1, @from)
	END
	
	RETURN
END
GO

SELECT dt FROM rep.DateList('20100101', '20101231') ORDER BY dt

только замените rep. на dbo.
31 авг 10, 19:01    [9359228]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
Greyghost
Member

Откуда:
Сообщений: 16
Автору в помощь
;with cte(num) as (
select 0
union all
select num+1 from cte where num < 365
)
insert into SPK_Простой_Табель_Календарь (Data, WorkHours)
select dateadd(dd, num, '01.01.2010'), 8 from cte option (maxrecursion 0)


Не корректно будет работать, если год високосный.
12 дек 10, 02:35    [9924148]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Список дат между двумя заданными  [new]
nekiyl
Member

Откуда:
Сообщений: 2
Простое, лаконичное решение: http://aliquis.ru/novosti/sql-spisok-dat-mezhdu-dvumya-datami/
23 май 16, 17:44    [19208979]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
nekiyl,

главное не выбирать период больше 6-ти лет?

и да чем руководствуются люди отвечающие на сообщения ШЕСТИ летней давности :)
23 май 16, 18:02    [19209034]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
А к чему сразу критиковать? :) Все выбирают решение в меру своих потребностей. Если я ожидаю, что будет малый диапазон, то либо заранее сгенеренная таблица либо spt_values. Если нужно необъяснимо много, то можно и так:

DECLARE @StartDate DATE = '20010101'
DECLARE @EndDate DATE = '20790101'

;WITH
    E1(N) AS (
        SELECT * FROM (
            VALUES
                (1),(1),(1),(1),(1),
                (1),(1),(1),(1),(1)
        ) t(N)
    ),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b),
    E4(N) AS (SELECT 1 FROM E2 a, E2 b),
    E8(N) AS (SELECT 1 FROM E4 a, E4 b)
SELECT DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY 1/0) - 1, @StartDate)
FROM (
    SELECT TOP(DATEDIFF(DAY, @StartDate, @EndDate) + 1) N
    FROM E8
) t
23 май 16, 18:16    [19209081]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
iljy
Member

Откуда:
Сообщений: 8711
AlanDenton,

ага, для потребностей, только у spt_values есть маааленький нюанс: юзеру нужен будет доступ к базе мастер, что в реальных системах редкость. А само по себе решение уже со времен 2005 сервера имеется, зачем его было с такой помпой, да еще в древней теме, на свет вытаскивать - вот это и интересно.
23 май 16, 18:53    [19209169]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
iljy, возможно прозвучит глупый вопрос от меня. Уж извините... недоспал сегодня после поездки. База master ведь публичная для всех пользователей. Я еще в жизни не видел, чтобы к ней доступ закрывали намеренно. И расскажите зачем это делать?
23 май 16, 19:01    [19209194]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
invm
Member

Откуда: Москва
Сообщений: 9442
AlanDenton,

Дело не в разрешениях - в мастере разрешен гость, а select из spt_values выдан public'у.
Дело в том, что таблица эта служебная и недокументированная. Следовательно ее структура, наполнение и само существование может изменится с любой новой версией или сервиспаком.
23 май 16, 19:54    [19209337]     Ответить | Цитировать Сообщить модератору
 Re: Список дат между двумя заданными  [new]
nekiyl
Member

Откуда:
Сообщений: 2
TaPaK, дело в том, что искал что-то подобное, но в данной ветке предлагались достаточно громоздкие решения и поэтому решил поделиться отсутствующим тут простым вариантом, так как другие люди так же могут искать решение данной задачи. Что касается шести лет, то в конкретном случае и не требуется больших промежутков, и вероятно в большом количестве случаев этого будет достаточно. По поводу доступа пользователей к базе данных в конкретной системе пользователи вообще не имеет доступа к база, а работает на уровне приложения, соответственно данной проблемы быть не может.
23 май 16, 21:21    [19209533]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить