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?

***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using (please update tags accordingly).... — marc_s, Aug 07 '13 at 18:09
Hmm.... the body of your question says **SSMS 2012* which is the **SQL Server** Management Studio 2012 - yet your **tag** says `MySql` - which one are you ***really*** using? — marc_s, Aug 07 '13 at 18:12
Sorry I guess im unaware of the difference as my VB.net program that will everntually run this program ( or sql script ) uses SQL Server and or Mysql to get into the database and was using SSMS to create the Script. Ill have to take a look at what the difference between mySQL and SQL Server script is then cause that doesnt make sence to me. — Don Thomas Boyle, Aug 07 '13 at 18:15
You aren't explaining how exactly `TotalStatTime` values are calculated, and it is not entirely clear from your example. — Andriy M, Aug 08 '13 at 08:42
TotStatTime should be a sum of date(datetime) column from StitesStatus just like the Sum of Sta1 is ( shows count of 2 for site 12345 in results wanted ) is this any more clear? — Don Thomas Boyle, Aug 08 '13 at 15:21
Apparently it was clear (or more clear) to someone who upvoted your response, but not to me, I'm sorry. It is not clear to me what a sum of a datetime column is in the first place. Do you mean it is the sum of intervals between neighboring rows? If it is, then I don't understand why `TotalStat2Time` for `grum, lad, aaa` is 10 and not e.g. 20 (assuming that to be minutes). For you've got three `date` values there -> 2 intervals 10 minutes each -> the sum should be 20. But again, as I said, I'm not sure that is the logic, hence my asking you about it. — Andriy M, Aug 09 '13 at 05:23
Thank you for bearing with me. You seem to have confirmed my guess about the logic, but then my question about `TotStat2Time` being 10, not 20, remains open. The first `Date` entry for `12345, sta1` is `8/7/13 15:33:22` and the last one is `8/7/13 15:43:22`. The difference is 10 minutes and I can see 10 in `TotStatTime` in your Desired Results – so far so good. But for `12345, sta2`, the first `Date` is `8/7/13 15:53:22` and the last one is `8/7/13 16:13:22`. The difference is now **20** minutes and yet I still see **10** in your Desired Results' corresponding `TotStat2Time` entry. Why? — Andriy M, Aug 09 '13 at 16:10
I mean, either I'm still missing something (for which I'm *really* sorry, for your latest explanation does appear clear enough and so I shouldn't have had any doubts by now), or the 10 I was talking about should indeed be 20 instead. — Andriy M, Aug 09 '13 at 16:13
its a typo, i have been assuming that i did have 20 uptop, sorry about that and good catch — Don Thomas Boyle, Aug 09 '13 at 16:25

score 2 · Accepted Answer · answered Aug 09 '13 at 19:10

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

I am using Vb.net in coordination with the sql statment ( returning the 0 or null is fine ) Thanks for all your time this answer is what i was looking for. But since I still have your knowledge's attention, do you know how i add every Row instead of just the min and max wich im assuming is first and last records? ideally I need to add 1+2 , 3+4, 5+6 ( if its possible ) if not This will suffice! — Don Thomas Boyle, Aug 09 '13 at 20:28
Do you mean you want to add up all the differences? i.e. `(Date2 - Date1) + (Date3 - Date2) + ...`? That would give you same result as `DateN - Date1`, i.e. what this query is doing now. Or I'm having trouble understanding you (again! sorry). — Andriy M, Aug 09 '13 at 21:35
Well atm its taking Max(Date) and subtracting it from Min(Date) wich is right, but i was curious to how i would do this ( 1 row at a time ). Instead of max - min , you would do next - last row make sence? — Don Thomas Boyle, Aug 13 '13 at 12:42
If you mean to ask about how you could read the current and the previous value at the same time *at all*, then perhaps this question could be of help: [How to get previous and current row value using recursive CTE?](http://stackoverflow.com/questions/9127636/how-to-get-previous-and-current-row-value-using-recursive-cte). As I'm not using SQL Server 2012 so far and can't use `LAG` or other useful additions introduced with that version, I mostly use ranking and self-join, like one of the suggestions of the accepted answer. — Andriy M, Aug 13 '13 at 17:37
Thanks for all the help thats absolutely what i was wanting! Thanks — Don Thomas Boyle, Aug 13 '13 at 17:43

score 0 · Answer 2 · edited May 23 '17 at 10:25

0

I wonder if you are after the PIVOT operation, where one or more of the fields in the original query are converted into column headings, with the pivoted results being summary info. Here's some links to get you started.

edited May 23 '17 at 10:25

Community

1
1

answered Aug 09 '13 at 01:43

meataxe

969
13
33

I'm actually not sure thats what I'm looking for, what i am looking for is merely the way to Code to A 2nd query inside of the first one that adds up all DateTime Rows for "said" sitenumber and status? – Don Thomas Boyle Aug 09 '13 at 15:59

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

2 Answers2