代码之家 › 专栏 › 技术社区 › Sparrower

将多个日期字段连接到日历表

qliksense calendar join tsql sql-server

Sparrower · 技术社区 · 8 年前

我有一个表,我们称之为记录,其中相关数据按如下方式组织:

| 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

我还有一个日历表,名为calendar,有一个名为Date的字段,其中有今年每天的日期。我想做的是将SubmissionDate、FirstReviewDate和SecondReview date与日历关联。日期,以便我可以对任何给定的一天的所有三个字段进行计数。

我尝试了以下代码:

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

我不知道该如何加入。我已经阅读了许多关于日历表的参考资料,但示例总是包括连接表,每个表都有一个日期标识符。我的问题是,我有一个表,其中有三个日期字段需要与日历关联。日期。

我这样安排我的数据,以便数据可以在Qlik意义上可视化。我希望用户能够从过滤器面板中选择日期,并使其聚合指定日期的所有三个字段(基本上与示例代码中的WHERE子句相同)。

2 回复 | 直到 8 年前

FLICKER 8 年前

尝试以下代码

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

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

Mat Richardson 8 年前

这应该行得通——我为示例数据提供了一些简单的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