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

using ssms ( sql server management studio ) - 2012

please me finish building 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 have show hours of row of date column in table 2 correspond sitenumber table 1, place inprogress column/row of result if didn't find number calculate with. (i can in code if not possible.) may have value not have "last value" yet, in clocked in no clock out time yet.

please keep in mind statuses known , not change. (so know have x amount of statuses, x 2 example.)

what have tried far :

select    client,   market,   projecttype,   count(*) total,   sum(case when statusname = 'sta1' 1 else 0 end) 'sta1',   sum(case when statusname = 'sta2' 1 else 0 end) 'sta2'  --here i'd add time adding joined table2    sites s inner join sitesstatus ss on s.sitenumber = ss.sitenumber    (statusname in (   'sta1',    'sta2',    )    ) group   client,   market,   projecttype 

@andi m edit:

where -here i'd add time adding joined table2 above, i'd know logic add rows column date(datetime) givin sitenumbers , statuses match

so 12345 has 2 sta1 entries 1 starting entry 2 1 i'd add/ subtract time first entry

12345 has 3 sta2 entries adding / subtracting last entry first time.

56789 has 1 sta1 , 1 sta2, there time i'd code show (--) or (inprogress) since has no end value....

any more clear?

in basic form, 1 way amend query return required additional bits of data this:

select    s.client,   s.market,   s.projecttype,   count(*) total,   sum(case when ss.statusname = 'sta1' 1 else 0 end) sta1,   sum(case when ss.statusname = 'sta2' 1 else 0 end) sta2,   datediff(     minute,     min(case ss.statusname when 'sta1' ss.date end),     max(case ss.statusname when 'sta1' ss.date end)   ) totsta1time,   datediff(     minute,     min(case ss.statusname when 'sta2' ss.date end),     max(case ss.statusname when 'sta2' ss.date end)   ) totsta2time    sites s inner join sitesstatus ss on s.sitenumber = ss.sitenumber  (   statusname in (     'sta1',      'sta2',    )  ) group   client,   market,   projecttype ;

for group containing 1 row, both min() , max() return same value and, result, datediff() evaluate 0. turning 0 'inprogress' possible please note mixing different types in same column. might want consider returning nulls instead , interpret them inprogress in application. that, need put datediff calls inside nullif()s:

...   nullif(     datediff(       minute,       min(case ss.statusname when 'sta1' ss.date end),       max(case ss.statusname when 'sta1' ss.date end)     ),     0   ) totsta1time,   nullif(     datediff(       minute,       min(case ss.statusname when 'sta2' ss.date end),       max(case ss.statusname when 'sta2' ss.date end)     ),     0   ) totsta2time ...

however, if absolutely sure need query return ready-for-display results, in desired results, you'll need add 2 more function calls each of 2 new expressions, 1 cast/convert varchar , 1 isnull/coalesce default null 'inprogress':

...   coalesce(     convert(       varchar(10),       nullif(         datediff(           minute,           min(case ss.statusname when 'sta1' ss.date end),           max(case ss.statusname when 'sta1' ss.date end)         ),         0       )     ),     'inprogress'   ) totsta1time,   coalesce(     convert(       varchar(10),       nullif(         datediff(           minute,           min(case ss.statusname when 'sta2' ss.date end),           max(case ss.statusname when 'sta2' ss.date end)         ),         0       )     ),     'inprogress'   ) totsta2time ...