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

Откуда: Kaliningrad
Сообщений: 86
Добрый день.
Помогите составить запрос. Есть таблица:
CREATE TABLE "Table1" (
"Id" integer NOT NULL ,
"NPerson" tinyint NOT NULL,
"Date_Open" date NULL,
"Date_close" date NULL,
PRIMARY KEY CLUSTERED ( "Id",  "NPerson" )

insert into table1 (id,nperson,date_open,date_close)
select 1,1,null,null
union 
select 1,2,'20040101',null
union 
select 1,3,'20040110','20040130'
union
select 1,4,'20040120',null

Нужно посчитать количество записей за месяц по периодам.
т.е. получить за январь

id   count(id)   date1         date2
1    2          '20040101'   '20040109'
1    3          '20040110'   '20040119'
1    4          '20040120'   '20040130'
1    3          '20040131'   '20040131'

Вроде где то подобное уже видел, но никак не могу найти.
28 июл 04, 17:00    [843257]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
GreenSunrise
Member

Откуда:
Сообщений: 12310
Странный у вас пример какой-то... Названия в кавычках, тип date (а не datetime). Сильно старая версия сервера? Или это вообще не MS SQL?

Ну а по существу вопрос такой: "количество записей за месяц по периодам" - это записи, у которых оба поля (Date_Open и Date_close) находятся в этом месяце или первое или второе?

P.S. Дайте нормальные ДАННЫЕ для тестирования. Из

select 1,1,null,null
union 
select 1,2,'20040101',null
union 
select 1,3,'20040110','20040130'
union
select 1,4,'20040120',null

вот такую

id   count(id)   date1         date2
1    2          '20040101'   '20040109'
1    3          '20040110'   '20040119'
1    4          '20040120'   '20040130'
1    3          '20040131'   '20040131'

выборку не получишь. Сидеть сочинять для вас тесты тоже не сильно хочется.

P.S. Group by вкупе с count рулит...
28 июл 04, 18:35    [843673]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2386
Блог
Это?
28 июл 04, 18:50    [843697]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
VadimS
Member

Откуда: Kaliningrad
Сообщений: 86
< Павел Воронцов
Похоже, но немного не то.
Попробую объяснить по другому.
Есть таблица состава проживающих, где
id - код квартиры
nperson - порядковый номер человека
Date_open - дата прописки, если null, то берем к примеру '01.01.1900'
date_Close - дата выписки, если null, то берем к примеру  '01.01.2050'
Теперь необходимо посчитать сколько человек проживало за месяц по дням
что бы получилось так:
с '01.01.2004' по '09.01.2004' проживало 2 человека
с '10.01.2004' по '19.01.2004' проживало 3 человека
с '20.01.2004' по '30.01.2004' проживало 4 человека
с '31.01.2004' по '31.01.2004' проживало 3 человека
с помощью промежуточной таблицы дат, я нашел сколько человек проживало за день в течение месяца, т.е.
'01.01.2004' - 2 чел
'02.01.2004' - 2 чел
...
'10.01.2004' - 3 чел
'11.01.2004' - 3 чел
...
'19.01.2004' - 3 чел
'20.01.2004' - 4 чел
и т.д.
теперь выбрать периоды не получается
29 июл 04, 11:08    [844680]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2386
Блог
CREATE TABLE PERIODS (ID INT NOT NULL, -- номер квартиры

PERSON INT NOT NULL, -- номер человека
START_DATE DATETIME NULL, END_DATE DATETIME NULL, constraint PERIODS_PK primary key nonclustered (ID, PERSON) ) insert into PERIODS select 1,1,null,null union select 1,2,'20040101',null union select 1,3,'20040110','20040130' union select 1,4,'20040120',null declare @startdate datetime, @enddate datetime set @startdate = '20040101' set @enddate = '20040131' SELECT ID, CNT, MIN(DT) AS START, MAX(DT) AS FIN FROM( SELECT COUNT(PERSON) AS CNT, DT, ID, SUM(PERSON) AS MPERSON FROM ( SELECT ID, PERSON, dateadd(day, num, isnull(START_DATE,@startdate) ) AS DT FROM PERIODS p1, numbers WHERE num <= datediff(day,isnull(START_DATE,@startdate),isnull(END_DATE ,@enddate)) ) a GROUP BY DT, ID) b GROUP BY ID, CNT, MPERSON order by 3 drop table PERIODS

я тут конечно мухлюю с SUM(PERSON), но ничего умнее пока не придумал
29 июл 04, 12:13    [845036]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
замечание... по моему вместо

1    3          '20040131'   '20040131'
должно быть
1    1          '20040131'   '20040131'

если так, то вот

declare @T TABLE (
[Id] integer NOT NULL ,
NPerson tinyint NOT NULL,
Date_Open datetime NULL,
Date_close datetime NULL)

insert into @t (id,nperson,date_open,date_close)
select 1,2,'20040101',null
union 
select 1,3,'20040110','20040130'
union
select 1,4,'20040120',null
union
select 1,1,null,null
union
select 2,1,null,null

declare @d1 datetime, @d2 datetime
select @d1 = '20040101', @d2 = '20040131'

select *,
    isnull((select top 1 nperson
      from @t where isnull(date_open, '19000101') = do and id = i
    ), (select top 1 nperson
         from @t where isnull(dateadd(dd, 1, date_close), '19000101') < dc and id = i
  )) a
 from 
  (select i, d do,
     isnull(
      (select top 1 dateadd(dd, -1, d)
        from (   select d, i
    from
     (select id i, isnull(date_open, '19000101') d from @t
      union
      select id, isnull(dateadd(dd, 1, date_close), '20500101') from @t
      union select distinct id, @d1 from @t
     ) a
    where d between @d1 and @d2) ti where ti.d>t.d and ti.i = t.i)
      , d) dc
    from (   select d, i
    from
     (select id i, isnull(date_open, '19000101') d from @t
      union
      select id, isnull(dateadd(dd, 1, date_close), '20500101') from @t
      union select distinct id, @d1 from @t
     ) a
    where d between @d1 and @d2) t
  ) a

P.S. на скорость не претендую... :)

для спящего время бодрствования равносильно сну
29 июл 04, 12:21    [845062]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2386
Блог
Вот так правильней
SELECT ID, CNT, MIN(DT) AS START, MAX(DT) AS FIN FROM(
SELECT COUNT(PERSON) AS CNT, DT, ID, MIN(PERSON) AS MPERSON, MAX(PERSON) AS MPERSON1
FROM (
SELECT ID, PERSON, dateadd(day, num, isnull(START_DATE,@startdate) ) AS DT
FROM PERIODS p1, numbers
WHERE num <= datediff(day,isnull(START_DATE,@startdate),isnull(END_DATE ,@enddate))
) a
GROUP BY DT, ID) b
GROUP BY ID, CNT, MPERSON, MPERSON1
order by 3
29 июл 04, 12:23    [845072]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
Буба Касторский
Guest
автор
с помощью промежуточной таблицы дат, я нашел сколько человек проживало за день в течение месяца, т.е.
'01.01.2004' - 2 чел
'02.01.2004' - 2 чел
...
'10.01.2004' - 3 чел
'11.01.2004' - 3 чел
...
'19.01.2004' - 3 чел
'20.01.2004' - 4 чел
и т.д.

теперь выбрать периоды не получается


А так не пойдет?

SELECT
CASE 
when [date] between '200401001' and '20040109' then 'с 01.01 2004 по 09.01.2004 проживало' 
when [date] between '200401010' and '20040119' then 'с 10.01 2004 по 19.01.2004 проживало' 
when [date] between '200401020' and '20040131' then 'с 20.01 2004 по 31.01.2004 проживало' 
END AS [TimeInterval],
SUM([lives]) AS [Lives]
FROM #tmp
GROUP BY
CASE 
when [date] between '200401001' and '20040109' then 'с 01.01 2004 по 09.01.2004 проживало' 
when [date] between '200401010' and '20040119' then 'с 10.01 2004 по 19.01.2004 проживало' 
when [date] between '200401020' and '20040131' then 'с 20.01 2004 по 31.01.2004 проживало' 
END

где #tmp ([date] datetime, lives int) - это Ваша промежуточная таблица с проживающим по дням
29 июл 04, 12:25    [845079]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
я конечно понимаю, у всех ДОЛЖНА БЫТЬ, но не у всех есть таблица NUMBERS


для спящего время бодрствования равносильно сну
29 июл 04, 12:29    [845096]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
малюсенькая поправочка...
и еще... в каждой строке должна быть дата начала... =\
кроме Х, Х, null, null

select *,
    isnull(
      (select top 1 nperson
        from @t where isnull(date_open, '19000101') = do and id = i
      ),  (select top 1 nperson
         from @t where isnull(dateadd(dd, 1, date_close), '19000101') < dc and id = i
      )
     ) a
 from 
  (select i, d do,
     isnull(
      (select top 1 dateadd(dd, -1, d)
        from (   select d, i
    from
     (select id i, isnull(date_open, '19000101') d from @t
      union
      select id, isnull(dateadd(dd, 1, date_close), '20500101') from @t
      union select distinct id, @d1 from @t
     ) a
    where d between @d1 and @d2) ti where ti.d>t.d and ti.i = t.i)
      , @d2) dc
    from (   select d, i
    from
     (select id i, isnull(date_open, '19000101') d from @t
      union
      select id, isnull(dateadd(dd, 1, date_close), '20500101') from @t
      union select distinct id, @d1 from @t
     ) a
    where d between @d1 and @d2) t
  ) a

для спящего время бодрствования равносильно сну
29 июл 04, 13:03    [845250]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2386
Блог
Алексей - ну так сделайте её скорей! )
29 июл 04, 13:04    [845251]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
VadomS
Guest
< Алексей2003
Нет, 31.01.2004 проживало 3 чел, а не 1

< Павел Воронцов
То что нужно. СПАСИБО!
29 июл 04, 13:10    [845286]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
с какой стати?
автор
select 1,3,'20040110','20040130'

действует до 30го числа... а 31 уже не действует...


для спящего время бодрствования равносильно сну
29 июл 04, 13:14    [845310]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
VadimS
Member

Откуда: Kaliningrad
Сообщений: 86
Алексей2003
с какой стати?
автор
select 1,3,'20040110','20040130'

действует до 30го числа... а 31 уже не действует...


для спящего время бодрствования равносильно сну


1-й проживал с 1 по 31
2-й с 1 по 31
3-й с 10 по 30
4-й с 20 по 31

31 числа проживало 3 человека
29 июл 04, 13:54    [845529]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
не туда глянул, не то понял :) сорри


для спящего время бодрствования равносильно сну
29 июл 04, 14:02    [845576]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
VadimS
Member

Откуда: Kaliningrad
Сообщений: 86
< Павел Воронцов

не совсем корректно отрабатывает, если
INSERT INTO Periods
select 1,1,null,null
union
select 1,2,null,null
union
select 1,3,null,null
union
select 1,4,'20040115','20040128'
выдает только два периода, а должно три
29 июл 04, 14:07    [845603]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2386
Блог
To VadimS

Ну звиняйте. Мне там зацепиться не за что. Есть ещё каки-нибудь колонки? А первичный ключ какой?
29 июл 04, 15:49    [846183]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
VadimS
Member

Откуда: Kaliningrad
Сообщений: 86
PRIMARY KEY CLUSTERED ( "Id",  "NPerson" )
29 июл 04, 15:59    [846237]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
вот и решение
CREATE TABLE PERIODS (ID INT NOT NULL, -- номер квартиры

PERSON INT NOT NULL, -- номер человека
START_DATE DATETIME NULL, END_DATE DATETIME NULL, constraint PERIODS_PK primary key nonclustered (ID, PERSON) ) delete from periods insert into PERIODS select 1,1,null,null union select 1,2,null,null union select 1,3,'20040110',null union select 1,4,null,null union select 1,5,null,null union select 1,6,'20040101',null union select 1,7,'20040115','20040125' union select 1,8,'20040120',null select id, d1, isnull((select top 1 dateadd(dd, -1, d2) from ( select id, isnull(START_DATE, '19000101') d2 from PERIODS union select id, isnull(dateadd(dd, 1, END_DATE), '20500101') d from PERIODS union select distinct id, @enddate from PERIODS ) b where d2 between @startdate and @enddate and a.d1<b.d2 order by 1 ), @enddate) d2, (select count(*) from periods p where isnull(START_DATE, '19000101') <= d1 and isnull(END_DATE, '20500101') >= d1 and p.id = a.id) cnt from ( select id, isnull(START_DATE, '19000101') d1 from PERIODS union select id, isnull(dateadd(dd, 1, END_DATE), '20500101') d from PERIODS union select distinct id, @startdate from PERIODS ) a where d1 between @startdate and @enddate drop table PERIODS


для спящего время бодрствования равносильно сну
30 июл 04, 07:42    [847556]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2386
Блог
вот ещё что-то очень похожее. Сейчас попробуем это дело использовать... Нет, не получается. Вот так наверно правильно

SELECT ID, CNT, MIN(DT) AS START, MAX(DT) AS FIN, MDT FROM(
SELECT COUNT(PERSON) AS CNT, DT, ID, --, MIN(PERSON) AS MPERSON, MAX(PERSON) AS MPERSON1, 

isnull((select max(isnull(p.START_DATE,@startdate)) from PERIODS p where p.ID = a.ID and isnull(p.START_DATE,@startdate) <= a.DT),@startdate) as MDT FROM ( SELECT ID, PERSON, dateadd(day, num, isnull(START_DATE,@startdate) ) AS DT FROM PERIODS p1, numbers WHERE num <= datediff(day,isnull(START_DATE,@startdate),isnull(END_DATE ,@enddate)) ) a GROUP BY DT, ID) b GROUP BY ID, CNT, MDT order by 1,3
30 июл 04, 07:48    [847558]     Ответить | Цитировать Сообщить модератору
 Re: выборка по датам  [new]
VadimS
Member

Откуда: Kaliningrad
Сообщений: 86
СПАСИБО ВСЕМ!
Оба варианта работают.
Сейчас буду проверять на больших объемах по скорости.
30 июл 04, 10:52    [848153]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить