Объединение нескольких полей даты в таблицу календаря

У меня есть таблица, назовем ее Records, где соответствующие данные организованы следующим образом:

| Employee | SubmissionDate | FirstReviewDate | SecondReviewDate |
  Anne       2017-10-02       2017-10-03        2017-10-10          
  Bernard    2017-10-03       2017-10-05        2017-10-10
  Charlene   2017-10-06       2017-10-09        2017-10-09
  Danielle   2017-10-02       2017-10-03        2017-10-09
  Anne       2017-10-03       2017-10-03        2017-10-09

Каждый раз, когда сотрудник делает представление, добавляется новая запись с SubmissionDate. Позже запись редактируется, чтобы включить в нее первую и вторую проверки.

У меня также есть таблица календаря под названием «Календарь» с полем «Дата», в котором есть даты на каждый день в этом году. Что я хотел бы сделать, так это связать даты SubmissionDate, FirstReviewDate и SecondReview с Calendar.TheDate, чтобы я мог подсчитывать все три поля для любого заданного дня.

Я пробовал следующий код:

SELECT Employee,
       Count(SubmissionDate) AS "Submissions",
       Count(FirstReviewDate) AS "First Reviews",
       Count(SecondReviewDate) AS "Second Reviews"
  FROM Records
  LEFT JOIN Calendar ON Records.SubmissionDate = Calendar.TheDate
   AND Records.FirstReviewDate = Calendar.TheDate
   AND Records.SecondReviewDate = Calendar.TheDate
 WHERE TheDate = '2017-10-11'

Все варианты этого, которые я пробовал, ничего не выводят:

| Employee | Submissions | First Reviews | Second Reviews |

Мой желаемый код будет выглядеть так:

SELECT Employee,
       Count(SubmissionDate),
       Count(FirstReviewDate),
       Count(SecondReviewDate)
  FROM ???
 WHERE TheDate = '2017-10-03'

куда ??? было бы правильным объединением. Используя желаемый блок кода (включая предложение where), желаемый вывод будет таким для предоставленного примера данных:

| Employee | Submissions | First Reviews | Second Reviews |
  Anne       1             2               0
  Bernard    1             0               0
  Charlene   0             0               0
  Danielle   0             1               0

Я не уверен, как это сделать. Я читал много ресурсов о таблицах календаря, но примеры всегда включают объединение таблиц, каждая из которых имеет один идентификатор даты. Моя проблема в том, что у меня есть одна таблица с тремя полями даты, которые необходимо связать с Calendar.TheDate.

Я упорядочиваю свои данные таким образом, чтобы данные можно было визуализировать в Qlik Sense. Я хотел бы, чтобы пользователи могли выбирать TheDate на панели фильтров и объединять все три поля для указанной даты (по сути, идентично предложению WHERE в моем примере кода).


sql-server calendar tsql join qliksense
person Sparrower    schedule 11.10.2017    source источник
comment
поможет, если вы отобразите образцы данных и результат.   -  person maSTAShuFu    schedule 12.10.2017
comment
Мои образцы данных находятся вверху, и я упомянул, что они ничего не выводят. Постараюсь отредактировать пост, чтобы было понятнее.   -  person Sparrower    schedule 12.10.2017
comment
нужно будет увидеть желаемый результат, а не основанный на том, что вы пробовали.   -  person maSTAShuFu    schedule 12.10.2017
comment
Я обновил свой исходный пост, включив в него еще несколько примеров данных, а также желаемый результат. Я прошу прощения; Я понял, о чем вы спрашивали, после того, как ответил. Дайте мне знать, если есть что-то еще, что я могу добавить для ясности.   -  person Sparrower    schedule 12.10.2017
comment
на основе желаемого результата и необработанных данных ... как вы пришли к тому, что у Анны было 2 даты первого обзора?   -  person maSTAShuFu    schedule 12.10.2017
comment
Всякий раз, когда документ отправляется, создается новая запись вместе с SubmissionDate. В данных примера у Анны есть две записи: одна отправлена ​​2 октября, а другая – 3 октября. Обе записи имеют FirstReviewDate от 3 октября. Я добавил объяснение таблицы Records для ясности.   -  person Sparrower    schedule 12.10.2017
comment
У Анны 2 заявки и 2 отзыва... но в желаемом результате у Анны только 1 заявка   -  person maSTAShuFu    schedule 12.10.2017
comment
В моем желаемом блоке кода есть предложение where, которое гласит: WHERE TheDate = '2017-10-03'. Я обновлю свой пост, чтобы привлечь к этому внимание.   -  person Sparrower    schedule 12.10.2017

Ответы (2)


arrow_upward
2
arrow_downward

Попробуйте код ниже

create table #recs (
  Employee varchar(10), 
  SubmissionDate date, 
  FirstReviewDate date, 
  SecondReviewDate date
)

insert into #recs values
('Anne',       '2017-10-02', '2017-10-03', '2017-10-10'),          
('Bernard',    '2017-10-03', '2017-10-05', '2017-10-10'),
('Charlene',   '2017-10-06', '2017-10-09', '2017-10-09'),
('Danielle',   '2017-10-02', '2017-10-03', '2017-10-09'),
('Anne',       '2017-10-03', '2017-10-03', '2017-10-09')

select Employee
    , sum(IIF(SubD.Date_Value = #recs.SubmissionDate, 1, 0)) AS Submissions 
    , sum(IIF(SubD.Date_Value = #recs.FirstReviewDate, 1, 0)) AS [First Reviews]
    , sum(IIF(SubD.Date_Value = #recs.SecondReviewDate, 1, 0)) AS [Second Reviews]
from #recs
    left join DimDate SubD on SubD.Date_Value = '2017-10-03' and
        (SubD.Date_Value = #recs.SubmissionDate
        or SubD.Date_Value = #recs.FirstReviewDate
        or SubD.Date_Value = #recs.SecondReviewDate)
group by Employee

Если ваш SQL Server старше 2012 года, используйте CASE вместо IIF.

select Employee
    , sum(case when SubD.Date_Value = #recs.SubmissionDate then 1 else 0 end) AS Submissions 
    , sum(case when SubD.Date_Value = #recs.FirstReviewDate then 1 else 0 end) AS [First Reviews]
    , sum(case when SubD.Date_Value = #recs.SecondReviewDate then 1 else 0 end) AS [Second Reviews]
from #recs
    left join DimDate SubD on SubD.Date_Value = '2017-10-03' and
        (SubD.Date_Value = #recs.SubmissionDate
        or SubD.Date_Value = #recs.FirstReviewDate
        or SubD.Date_Value = #recs.SecondReviewDate)
group by Employee
person FLICKER    schedule 11.10.2017
comment
Спасибо еще раз; Я отметил ваш ответ как ответ. - person Sparrower; 12.10.2017

arrow_upward
0
arrow_downward

Это должно работать - я включил простой DML для примера данных: -

--Table Setup
IF OBJECT_ID('Records') IS NOT NULL
  DROP TABLE Records;
IF OBJECT_ID('Calendar') IS NOT NULL
  DROP TABLE Calendar;

CREATE TABLE Records (
  Employee varchar(100),
  SubmissionDate datetime,
  FirstReviewDate datetime,
  SecondReviewDate datetime
)


CREATE TABLE Calendar (
  TheDate datetime
)

INSERT Records
  VALUES ('Anne', '2017-10-02', '2017-10-03', '2017-10-10'),
  ('Bernard', '2017-10-03', '2017-10-05', '2017-10-10'),
  ('Charlene', '2017-10-06', '2017-10-09', '2017-10-09'),
  ('Danielle', '2017-10-02', '2017-10-03', '2017-10-09'),
  ('Anne', '2017-10-03', '2017-10-03', '2017-10-09')


INSERT Calendar
  VALUES ('2017-10-03'), ('2017-10-04'), ('2017-10-05')



--Main Query
DECLARE @TheDate datetime = '2017-10-03'

SELECT
  Employee,
  SUM(CASE
    WHEN c1.TheDate IS NOT NULL THEN 1
    ELSE 0
  END),
  SUM(CASE
    WHEN c2.TheDate IS NOT NULL THEN 1
    ELSE 0
  END),
  SUM(CASE
    WHEN c3.TheDate IS NOT NULL THEN 1
    ELSE 0
  END)
FROM Records r
LEFT JOIN Calendar c1
  ON r.SubmissionDate = c1.TheDate
  AND c1.thedate = @TheDate
LEFT JOIN Calendar c2
  ON r.FirstReviewDate = c2.TheDate
  AND c2.thedate = @TheDate
LEFT JOIN Calendar c3
  ON r.SecondReviewDate = c3.TheDate
  AND c3.thedate = @TheDate
GROUP BY Employee
person Mat Richardson    schedule 11.10.2017
comment
Этот код работает в соответствии с моими требованиями. Большое спасибо за ваш ответ, я проголосовал за ответ (хотя он не виден публично). - person Sparrower; 12.10.2017