sql-server - 如何在 SQL 中对单列连接的日期时间字段求和

Question

使用 SSMS(SQL Server Management Studio)- 2012

请帮助我完成此 SQL 查询的构建。

第一个表 站点

 Client,Market,Project,SiteNumber
  grum , lad  , aaa   , 12345
  gla  , daa  , h11   , 56789

第二个表 SitesStatus

SiteNumber,StatusName,Date(DateTime),ByUser
12345   ,  sta1    , 8/7/13 15:33:22, hec
12345   ,  sta1    , 8/7/13 15:43:22, hec
12345   ,  sta2    , 8/7/13 15:53:22, hec
12345   ,  sta2    , 8/7/13 16:03:22, hec
12345   ,  sta2    , 8/7/13 16:13:22, hec
56789   ,  sta1    , 8/7/13 15:22:22, hec
56789   ,  sta2    , 8/7/13 15:32:22, hec

期望的结果

Client,Market,Project,TotalSites,   sta1   ,TotStatTime,    sta2   ,TotStat2Time,ByUser
 Grum ,  lad ,  aaa  ,     5    ,     2    ,     10    ,     3     ,     20     , hec
 gla  , daa  ,  h11  ,     2    ,     1    , inprogress,     1     , inprogress , hec

它必须显示表 2 中日期列中与表 1 中的 sitenumber 相对应的所有行的小时数，而且还要将 inprogress 放入如果没有找到要计算的数字，结果的列/行。 (如果这不可能，我也可以在我的代码中执行此操作。)它也可能有一个值但还没有“最后一个值”，就像在打卡时间但还没有打卡时间一样。

请记住，所有状态都是已知的，不会改变。 (所以我知道我只会有 X 个状态，对于这个例子，X 是 2。)

到目前为止我已经尝试过:

select 
  Client,
  Market,
  ProjectType,
  count(*) as Total,
  sum(case when StatusName = 'sta1' then 1 else 0 end) as 'sta1',
  sum(case when StatusName = 'sta2' then 1 else 0 end) as 'sta2'

--Here is where I'd like to add the Time adding for the joined table2

from
  Sites s
INNER JOIN SitesStatus ss
on s.sitenumber = ss.SiteNumber

where
  (StatusName in (
  'sta1', 
  'sta2', 
  ) 
  )
group by
  Client,
  Market,
  ProjectType

@Andi M 编辑:

我说的地方-这是我想为上面的连接表2添加时间的地方，我想知道为列日期(DateTime)添加所有行的逻辑给出站点编号和状态匹配

所以 12345 有 2 个 sta1 条目 1 起始条目 2 我想添加/减去第一个条目的时间

12345 也有 3 个 sta2 条目，我们将从第一个条目中添加/减去最后一个条目以获得时间。

56789 有 1 个 sta1 和 1 个 sta2，我希望代码显示的时间是 (--) 或 (inProgress)，因为它没有最终值....

更清楚了吗？

Answer 1

在最基本的形式中，一种修改查询以返回所需的额外数据位的方法是:

select 
  s.Client,
  s.Market,
  s.ProjectType,
  count(*) as Total,
  sum(case when ss.StatusName = 'sta1' then 1 else 0 end) as sta1,
  sum(case when ss.StatusName = 'sta2' then 1 else 0 end) as sta2,
  <b>datediff(
    minute,
    min(case ss.StatusName when 'sta1' then ss.Date end),
    max(case ss.StatusName when 'sta1' then ss.Date end)
  ) as TotSta1Time,
  datediff(
    minute,
    min(case ss.StatusName when 'sta2' then ss.Date end),
    max(case ss.StatusName when 'sta2' then ss.Date end)
  ) as TotSta2Time</b>

from
  Sites s
INNER JOIN SitesStatus ss
on s.sitenumber = ss.SiteNumber

where (
  StatusName in (
    'sta1', 
    'sta2', 
  ) 
)
group by
  Client,
  Market,
  ProjectType
;

对于只包含一行的组，min() 和 max() 将返回相同的值，因此，datediff() 将评估为 0。将 0 转换为 'inprogress' 是可能的，但请注意，这将在同一列中混合不同的类型。您可能想考虑只返回 NULL，并在您的应用程序中将它们解释为 inprogress。为此，您只需将 datediff 调用放在 nullif()s 中:

...
  <b>nullif(</b>
    datediff(
      minute,
      min(case ss.StatusName when 'sta1' then ss.Date end),
      max(case ss.StatusName when 'sta1' then ss.Date end)
    )<b>,
    0
  )</b> as TotSta1Time,
  <b>nullif(</b>
    datediff(
      minute,
      min(case ss.StatusName when 'sta2' then ss.Date end),
      max(case ss.StatusName when 'sta2' then ss.Date end)
    )<b>,
    0
  )</b> as TotSta2Time
...

但是，如果您绝对确定您需要查询返回准备好显示的结果，就像在您的 Desired Results 中一样，您只需要向两个新表达式中的每一个添加两个函数调用，一个 CAST/CONVERT 到一个 varchar 和一个 ISNULL/COALESCE 将 NULL 默认为 '进行中':

...
  <b>coalesce(
    convert(
      varchar(10),</b>
      nullif(
        datediff(
          minute,
          min(case ss.StatusName when 'sta1' then ss.Date end),
          max(case ss.StatusName when 'sta1' then ss.Date end)
        ),
        0
      )
    <b>),
    'inprogress'
  )</b> as TotSta1Time,
  <b>coalesce(
    convert(
      varchar(10),</b>
      nullif(
        datediff(
          minute,
          min(case ss.StatusName when 'sta2' then ss.Date end),
          max(case ss.StatusName when 'sta2' then ss.Date end)
        ),
        0
      )
    <b>),
    'inprogress'
  )</b> as TotSta2Time
...