SQL присоединиться и сгруппировать по сгенерированному диапазону дат

У меня есть таблица 1, и мне нужен запрос для заполнения таблицы 2:

введите здесь описание изображения

Проблема здесь с столбцом даты. Я хочу знать процесс совмещения местоположения/партнера в день. Основная проблема здесь заключается в том, что я не могу выбрать DateCreated и сделать ее датой по умолчанию, поскольку она не обязательно охватывает весь диапазон дат, как в этом примере, где нет 2015-01-07 и 2015-01-09. Тот же случай с другими датами.

Итак, моя идея состоит в том, чтобы сначала выбрать даты из некоторой таблицы, которая содержит необходимый диапазон дат, а затем выполнить расчет для каждой комбинации дня/местоположения/партнера из cte, но в этом случае я не могу понять, как сделать объединение для LocationId и PartnerId .

Столбцы:

  • Date - CreatedItems - количество созданных элементов, где Table1.DateCreated = Table2.Date
  • DeliveredItems — количество доставленных товаров, где Table1.DateDateOut = Table2.Date
  • CycleTime – количество дней, в течение которых доставленный товар находился в этом месте (DateOut – DateIn + 1).

Я начал с чего-то вроде этого, но очень похоже, что я полностью упустил из виду:

with d as
(
    select date from DimDate
    where date between DATEADD(DAY, -365, getdate()) and getdate()
),

cr as -- created items
(
    select 
    DateCreated, 
    LocationId,
    PartnerId,
    CreatedItems = count(*)
    from Table1
    where DateCreated is not null
    group by DateCreated, 
    LocationId,
    PartnerId
),

del as -- delivered items
(
    select 
    DateOut, 
    LocationId,
    ParnerId,
    DeliveredItems = count(*),
    CycleTime = DATEDIFF(Day, DateOut, DateIn)
    from Table1
    where DateOut is not null
    and Datein is not null
    group by DateOut, 
    LocationId,
    PartnerId
)

select
d.Date
from d
LEFT OUTER JOIN cr on cr.DateCreated = d.Date -- MISSING JOIN PER LocationId and PartnerId
LEFT OUTER JOIN del on del.DateCompleted = d.Date -- MISSING JOIN PER LocationId and PartnerId

sql join common-table-expression date-range
person ilija veselica    schedule 05.03.2015    source источник
comment
Можете ли вы описать логику доставленной колонки? Выход не соответствует входу.   -  person Gordon Linoff    schedule 05.03.2015
comment
@GordonLinoff Я добавил описание для каждого столбца   -  person ilija veselica    schedule 05.03.2015

Ответы (2)


arrow_upward
1
arrow_downward 

with range(days) as (
    select 0 union all select 1 union all select 2 union all
    select 3 union all select 4 union all select 5 union all
    select 6 /* extend as necessary */
)
select dateadd(day, r.days, t.DateCreated) as "Date", locationId, PartnerId,
    sum(
        case
            when dateadd(day, r.days, t.DateCreated) = t.DateCreated
            then 1 else 0
        end) as CreatedItems,
    sum(
        case
            when dateadd(day, r.days, t.DateCreated) = t.Dateout
            then 1 else 0
        end) as DeliveredItems,
    sum(
        case
            when dateadd(day, r.days, t.DateCreated) = t.Dateout
            then datediff(days, t.DateIn, t.DateOut) + 1 else 0
        end) as CycleTime
from
    <yourtable> as t
    inner join range as r
        on r.days between 0 and datediff(day, t.DateCreated, t.DateOut)
group by dateadd(day, r.days, t.DateCreated), LocationId, PartnerId;

Если вам нужны только даты окончания (а не все даты между ними), это, вероятно, лучший подход:

with range(dt) as (
    select distinct DateCreated from T union
    select distinct DateOut from T
)
select r.dt as "Date", locationId, PartnerId,
    sum(
        case
            when r.dt = t.DateCreated
            then 1 else 0
        end) as CreatedItems,
    sum(
        case
            when r.dt = t.Dateout
            then 1 else 0
        end) as DeliveredItems,
    sum(
        case
            when r.dt = t.Dateout
            then datediff(days, t.DateIn, t.DateOut) + 1 else 0
        end) as CycleTime
from
    <yourtable> as t
    inner join range as r
        on r.dt in (t.DateCreated, t.DateOut)
group by r.dt, LocationId, PartnerId;
person shawnt00    schedule 05.03.2015
comment
Этот второй запрос выглядит так, как будто он может решить мою проблему, я немного подправлю его, так как мой конечный результат немного сложнее. Кстати, я не понимаю цель cte из первого запроса, зачем все эти союзы? - person ilija veselica; 05.03.2015
comment
Я думал, ты хочешь отчитаться обо всех днях между созданием и выходом; когда я думал об этом позже, я понял, что вы, вероятно, не хотели показывать дни, когда вообще ничего не происходило. Этот союз должен был создать набор строк для представления дня 0 до дня 6 (отсчитывается от DateCreated). Такой союз — это быстрый способ создать таблицу без необходимости иметь фактическую таблицу где-либо в базе данных. - person shawnt00; 05.03.2015
comment
На самом деле эта часть была решающей для всей моей проблемы - inner join range as r on r.dt in (t.DateCreated, t.DateOut)... не знаю, как я забыл, что я действительно могу присоединиться, используя ключевое слово in :) - person ilija veselica; 05.03.2015
comment
Я не обратил особого внимания на вашу попытку выше, кроме как отметить, что with в порядке и что вы, вероятно, используете SQL Server. Думаю, я мог бы использовать вашу таблицу DimDate, а не начинать с другого подхода. Да, я думаю, вы правы, что это был недостающий элемент. Приятно, что вы на самом деле вдумчивы, а не просто используете SO для бесплатной поездки. - person shawnt00; 05.03.2015

arrow_upward
0
arrow_downward

Если указать WHERE пункт? Что-то такое:

WHERE cr.LocationId = del.LocationId AND
      cr.PartnerId = del.PartnerId
person Stanislovas Kalašnikovas    schedule 05.03.2015