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

Откуда:
Сообщений: 12
Добрый день!
Имеется таблица Status с полями (id_object, dat_time, amount). При выборке данных выводится:

1 '05.03.2015 02:00:00' 12
1 '05.03.2015 07:00:00' 30
2 '05.03.2015 04:00:00' 10
2 '05.03.2015 16:00:00' 22
3 '05.03.2015 03:00:00' 15
3 '05.03.2015 15:00:00'
Необходимо вывести недостающие часы для каждого id_object. Т.е. для каждого id_object получится в итоге 24 записи. С помощью рекурсивного запроса и внешнего объединения добавляю недостающие часы:

WITH cal (start_dat,end_dat)
AS (
SELECT
MIN(si.dat_time) start_dat,
MAX(si.dat_time) end_dat
FROM
status si
UNION ALL
SELECT
DATEADD(hh,1,start_dat),
end_dat
FROM
cal
WHERE
DATEADD(hh,1,start_dat) <= end_dat
)
SELECT
*
FROM
cal
LEFT JOIN status so ON cal.start_dat = so.dat_time
Но добавляются недостающие часы вобщем. Как мне сделать, чтоб выводились недостающие часы для каждого id_object ?
6 мар 15, 11:15    [17351327]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
сделать таблицу календарь в нужной гранулярностью и сджойнить с ней
6 мар 15, 11:16    [17351345]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
Glory
Member

Откуда:
Сообщений: 104760
Изюм
С помощью рекурсивного запроса и внешнего объединения добавляю недостающие часы:

Зачем для получения 24х статических записей(по одной на каждый час) нужен запрос(да еще рекурсивный) к таблице ?
6 мар 15, 11:20    [17351376]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
Изюм
Member

Откуда:
Сообщений: 12
Glory,
А как лучше поступить?
6 мар 15, 11:25    [17351402]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
Так? (Не проверял!)
WITH cal (id_object,start_dat,end_dat)AS
(
 SELECT id_object,MIN(si.dat_time) start_dat, MAX(si.dat_time) end_dat
 FROM status si
 GROUP BY id_object
 UNION ALL
 SELECT id_object, DATEADD(hh,1,start_dat), end_dat
 FROM cal
 WHERE start_dat < end_dat
)
SELECT *
FROM cal
LEFT JOIN status so ON cal.id_object=so.id_object AND cal.start_dat = so.dat_time;
6 мар 15, 11:26    [17351407]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
Glory
Member

Откуда:
Сообщений: 104760
Изюм
Glory,
А как лучше поступить?

select 1 as hour union all
select 2 as hour union all
select 3 as hour union all ...
6 мар 15, 11:26    [17351410]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
Glory
Изюм
Glory,
А как лучше поступить?

select 1 as hour union all
select 2 as hour union all
select 3 as hour union all ...
SELECT * FROM (VALUES(1),(2),(3),...)T(h);
6 мар 15, 11:28    [17351429]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
Изюм
Member

Откуда:
Сообщений: 12
iap,
Спс, это то чно мне нужно было.
6 мар 15, 11:44    [17351557]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Glory
Изюм
Glory,
А как лучше поступить?

select 1 as hour union all
select 2 as hour union all
select 3 as hour union all ...



https://www.sql.ru/forum/1097504/vuha-dlya-kalendarya?hl=????? ??? ?????????
6 мар 15, 13:47    [17352479]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
Glory
Member

Откуда:
Сообщений: 104760
a_voronin
Glory
пропущено...

select 1 as hour union all
select 2 as hour union all
select 3 as hour union all ...



https://www.sql.ru/forum/1097504/vuha-dlya-kalendarya?hl=????? ??? ?????????

Кто о чем, а вшивый о бане (это пословица, если что)
6 мар 15, 13:51    [17352511]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
o-o
Guest
Glory
a_voronin
пропущено...
https://www.sql.ru/forum/1097504/vuha-dlya-kalendarya?hl=????? ??? ?????????

Кто о чем, а вшивый о бане (это пословица, если что)

бань, как минимум , две.
пословицы и поговорки с картинками
давайте уже другие, присоединяйтесь, тоже с нами хором прокричите,
видите же: не канает человека
6 мар 15, 14:19    [17352642]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Glory
a_voronin
пропущено...



https://www.sql.ru/forum/1097504/vuha-dlya-kalendarya?hl=????? ??? ?????????

Кто о чем, а вшивый о бане (это пословица, если что)


Я не могу спорить о том, что

select 1 as hour union all
select 2 as hour union all
select 3 as hour union all 


есть вершина совершенства, ибо в бесконечной портянке UNION ALL кроется истина ведущая к решению проблем производительности любой RBDSM
6 мар 15, 14:44    [17352745]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
Glory
Member

Откуда:
Сообщений: 104760
a_voronin
есть вершина совершенства, ибо в бесконечной портянке UNION ALL

23 UNION ALL - это не бесконечность.
Это гораздо меньше и быстрее вашего "суперпредставления"
6 мар 15, 14:46    [17352757]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
миксер
Guest
столкнулся со следующей проблемой в 2000 sql

есть таблица следующего вида

01.01.01_____50
02.01.01_____105
03.01.01_____15
.
.
.
07.01.01_____100

Нужно подставить в пропущеные даты (4,5,6) число 15.

Календарик с 01. по 07 я сделал
Спасибо
15 июн 15, 12:57    [17771584]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
Glory
Member

Откуда:
Сообщений: 104760
миксер
Календарик с 01. по 07 я сделал

И что теперь мешает для каждой даты этого календаря найти нужную запись из другой таблицы ?
15 июн 15, 13:00    [17771596]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
миксер
Guest
Glory, вот так я нашел первую пропущенную дату. теперь надо найти к ней нужное значение

IF OBJECT_ID('tempdb..#cal') IS NOT NULL DROP TABLE #cal
CREATE TABLE [dbo].[#cal](
	[DayId] [bigint] NULL,
	[DayName] [bigint] NULL
) ON [PRIMARY]


insert into #cal
select 20010101 as [DayId] , 1 as [DayName] union 
select 20010102 as [DayId] , 2 as [DayName] union 
select 20010104 as [DayId] , 3 as [DayName] union 
select 20010105 as [DayId] , 4 as [DayName] union
select 20010106 as [DayId] , 5 as [DayName]  union
select 20010107 as [DayId] , 6 as [DayName] 




IF OBJECT_ID('tempdb..#cal2') IS NOT NULL DROP TABLE #cal2
CREATE TABLE [dbo].[#cal2](
	[DayId] [bigint] NULL,
	[DayName] [bigint] NULL
) ON [PRIMARY]


insert into #cal2
select 20010101 as [DayId] , null as [DayName] union 
select 20010102 as [DayId] , null as [DayName] union 
select 20010103 as [DayId] , null as [DayName] union 
select 20010104 as [DayId] , null as [DayName] union 
select 20010105 as [DayId] , null as [DayName] union
select 20010106 as [DayId] , null as [DayName]  union
select 20010107 as [DayId] , null as [DayName] 

select min([c2].[DayId]) from #cal2 as [c2]
left join  #cal as [c] on [c2].[DayId]=[c].[DayId]
where [c].[DayId] is null
15 июн 15, 13:19    [17771664]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
Glory
Member

Откуда:
Сообщений: 104760
миксер
теперь надо найти к ней нужное значение

Ну так вы ищите по точное совпадению дат. А в #cal как раз нет такой даты. Но есть меньшая
15 июн 15, 13:22    [17771681]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
миксер
Guest
Glory,

я немного запутался. я поправил запрос чтобы был немного нагляднее

IF OBJECT_ID('tempdb..#cal') IS NOT NULL DROP TABLE #cal
CREATE TABLE [dbo].[#cal](
	[DayId] [bigint] NULL,
	[DayName] [bigint] NULL
) ON [PRIMARY]


insert into #cal
select 20010101 as [DayId] , 1 as [DayName] union 
select 20010102 as [DayId] , 2 as [DayName] union 
select 20010105 as [DayId] , 4 as [DayName] union
select 20010106 as [DayId] , 5 as [DayName]  union
select 20010107 as [DayId] , 6 as [DayName] 




IF OBJECT_ID('tempdb..#cal2') IS NOT NULL DROP TABLE #cal2
CREATE TABLE [dbo].[#cal2](
	[DayId] [bigint] NULL
) ON [PRIMARY]


insert into #cal2
select 20010101 as [DayId] union 
select 20010102 as [DayId] union 
select 20010103 as [DayId] union 
select 20010104 as [DayId] union 
select 20010105 as [DayId] union
select 20010106 as [DayId] union
select 20010107 as [DayId] 




select * from #cal2 where [DayId] between

(select min([c2].[DayId]) from #cal2 as [c2]
left join  #cal as [c] on [c2].[DayId]=[c].[DayId]
where [c].[DayId] is null
)

and 
(
select max([c2].[DayId]) from #cal2 as [c2]
left join  #cal as [c] on [c2].[DayId]=[c].[DayId]
where [c].[DayId] is null
)


я вернул диапазон дат которые надо заполнить. и в этом весь затык.

как написать, чтобы и 03 и 04 число заполнялись из 02?
15 июн 15, 13:41    [17771807]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
Glory
Member

Откуда:
Сообщений: 104760
миксер
как написать, чтобы и 03 и 04 число заполнялись из 02?

Просто найти первую запись <=
15 июн 15, 13:43    [17771817]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
миксер
Guest
Glory, написал

IF OBJECT_ID('tempdb..#cal') IS NOT NULL DROP TABLE #cal
CREATE TABLE [dbo].[#cal](
[DayId] [bigint] NULL,
[DayName] [bigint] NULL
) ON [PRIMARY]


insert into #cal
select 20010101 as [DayId] , 1 as [DayName] union
select 20010102 as [DayId] , 2 as [DayName] union
select 20010106 as [DayId] , 6 as [DayName] union
select 20010109 as [DayId] , 9 as [DayName]




IF OBJECT_ID('tempdb..#cal2') IS NOT NULL DROP TABLE #cal2
CREATE TABLE [dbo].[#cal2](
[DayId] [bigint] NULL
) ON [PRIMARY]


insert into #cal2
select 20010101 as [DayId] union
select 20010102 as [DayId] union
select 20010103 as [DayId] union
select 20010104 as [DayId] union
select 20010105 as [DayId] union
select 20010106 as [DayId] union
select 20010107 as [DayId] union
select 20010108 as [DayId] union
select 20010109 as [DayId] union
select 200101010 as [DayId] union
select 200101011 as [DayId]

select s.[DayId], f.[DayName] from (

select CONVERT(CHAR(8),dateadd("d",1, cast(cast([DayId] as char(20)) as datetime)),112) as [DayId] , [DayName] from #cal
where 1=1
and
CONVERT(CHAR(8),dateadd("d",1, cast(cast([DayId] as char(20)) as datetime)),112)=
(select min([c2].[DayId]) from #cal2 as [c2]
left join #cal as [c] on [c2].[DayId]=[c].[DayId]
where [c].[DayId] is null
)

) as f

left join
(
select * from #cal2 where [DayId] between

(select min([c2].[DayId]) from #cal2 as [c2]
left join #cal as [c] on [c2].[DayId]=[c].[DayId]
where [c].[DayId] is null
)

and
(
select max([c2].[DayId]) from #cal2 as [c2]
left join #cal as [c] on [c2].[DayId]=[c].[DayId]
where [c].[DayId] is null
)
) as s

on s.[DayId]>=f.[DayId]


но появляется косяк. когда у меня несколько пропусков в датах
15 июн 15, 13:50    [17771846]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
миксер
Guest
миксер, получилась такая таблица
DayId DayId DayName
20010101 20010101 1
20010102 20010102 2
20010103 NULL NULL
20010104 NULL NULL
20010105 NULL NULL
20010106 20010106 6
20010107 NULL NULL
20010108 NULL NULL
20010109 20010109 9
200101010 NULL NULL
200101011 NULL NULL
15 июн 15, 13:53    [17771860]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
Glory
Member

Откуда:
Сообщений: 104760
миксер
но появляется косяк. когда у меня несколько пропусков в датах

Вы знаете, что такое OUTER APPLY ?
15 июн 15, 13:54    [17771867]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
Добрый Э - Эх
Guest
Glory
миксер
но появляется косяк. когда у меня несколько пропусков в датах

Вы знаете, что такое OUTER APPLY ?
откуда? У него сервер 2000
15 июн 15, 14:03    [17771899]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
Glory
Member

Откуда:
Сообщений: 104760
Добрый Э - Эх
Glory
пропущено...

Вы знаете, что такое OUTER APPLY ?
откуда? У него сервер 2000

Тогда пусть узнает, что такое подзапрос с TOP 1
15 июн 15, 14:04    [17771902]     Ответить | Цитировать Сообщить модератору
 Re: Добавление недостающих дат  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
Добрый Э - Эх
Glory
пропущено...

Вы знаете, что такое OUTER APPLY ?
откуда? У него сервер 2000
Просто поле SELECTа в виде подзапроса TOP 1 .... WHERE ... ORDER BY
15 июн 15, 14:06    [17771910]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить