How to run a Sum on a DateTime Field in SQL with join on Single Column

Question

Using SSMS ( SQL Server Management Studio ) - 2012

Please help me finish building this SQL query.

First Table Sites

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

Second Table 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

Desired Results

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

It would have to show the hours of all row of the Date column in Table 2 that correspond with the sitenumber from table 1, but also place an inprogress into the column/row of the result if it didn't find a number to calculate with. (I can also do this in my code if this is not possible.) Also it may have a value but not have a "last value" just yet, as in a clocked in but no clock out time yet.

Please keep in mind all statuses are known and will not change. (So I know I will only have X amount of statuses, where X is 2 for this example.)

What I have tried so far :

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 EDIT:

Where i say -Here is where I'd like to add the Time adding for the joined table2 above, i'd like to know the logic to add All rows for Column Date(DateTime) givin the Sitenumbers and statuses Match

So 12345 has 2 sta1 entries 1 the starting entry 2 the one i'd like to add/ subtract to get the time from the first entry

12345 also has 3 sta2 entries we would be adding / subtracting the last entry from the first to get the time.

56789 has 1 sta1 and 1 sta2, there for the time i'd like for the code to show is (--) or (inProgress) since it has no end value....

Any more clear?

Andriy M · Accepted Answer

In its most basic form, one way to amend your query to return the required additional bits of data would be this:

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,
  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

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

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

For a group containing only one row, both min() and max() would return the same value and, as a result, datediff() would evaluate to 0. Turning the 0 into 'inprogress' is possible but please note that this would be mixing different types in the same column. You might want to consider returning just NULLs instead and interpret them as inprogress in your application. For that, you would only need to put the datediff calls inside nullif()s:

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

However, if you absolutely sure you need the query to return ready-for-display results, as in your Desired Results, you'll just need to add two more function calls to each of the two new expressions, one CAST/CONVERT to a varchar and one ISNULL/COALESCE to default the NULL to 'inprogress':

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

How to run a Sum on a DateTime Field in SQL with join on Single Column

Answers (2)

Related Questions