CODEWITHSUNDEEP
sqldatabasesql-server-2008-r2
C Sharper
C Sharper

Reputation: 8626

Find Difference between hours

I have made query :

SELECT    
 MemberId,  
      FirstName,LastName,  
      [DateOfChange]        
      ,(select title from StatusList where idStatus=[OldStatus]) as [OldStatus]  
      ,(select title from StatusList where idStatus=[NewStatus]) as [NewStatus]  
  FROM [statusLog] , Users where  
  statusLog.IdUser=Users.IdUser

This query gives me following result:

enter image description here

I have OldStatus and NewStatus and DateOfChange of status as a column.

I just wanted to have difference in hours of status change.

i.e. From DateOfChange For Old Status OnCall To Patrol i want to find difference between two dates as:

2014-04-04 16:13:33:000 and 2014-04-04 16:13:44:000

I Tried:

SELECT    
 MemberId,  
      FirstName,LastName,  
      [DateOfChange],
      DATEDIFF(HOUR,select [DateOfChange] from statusLog,Users where idstatus=[OldStatus]
      ,select [DateOfChange] from statusLog,Users where idstatus=[NewStatus])        
      ,(select title from StatusList where idStatus=[OldStatus]) as [OldStatus]  
      ,(select title from StatusList where idStatus=[NewStatus]) as [NewStatus]  
  FROM [statusLog] , Users where  
  statusLog.IdUser=Users.IdUser

But this doesnt worked.

Two tables which i have joined are:

Users:

enter image description here

statusLog:

enter image description here

Please help me.

How can i have difference in hours like this in above query??

Edit:

SELECT    
 MemberId,  
      FirstName,LastName,  
      [DateOfChange]     ,

      (SELECT

    DATEDIFF(HOUR, SL.DateOfChange, SLN.StatusTo) AS StatusDuration
FROM
    StatusLog SL
    OUTER APPLY (
        SELECT TOP(1)
            DateOfChange AS StatusTo
        FROM
            StatusLog SLT
        WHERE
            SL.IdUser = SLT.IdUser
            AND SLT.DateOfChange > SL.DateOfChange
        ORDER BY
            SLT.DateOfChange ASC
    ) SLN) Hourss

      ,(select title from StatusList where idStatus=[OldStatus]) as [OldStatus]  
      ,(select title from StatusList where idStatus=[NewStatus]) as [NewStatus]  
  FROM [statusLog] , Users where  
  statusLog.IdUser=Users.IdUser

Edit 2:

enter image description here

Upvotes: 0

Views: 82

Answers (3)

Pred
Pred

Reputation: 9042

Start with this one (you have to join users and what you want)

SELECT
    SL.DateOfChange AS StatusFrom
    , SLN.StatusTo AS 
    , DATEDIFF(HOUR, SL.DateOfChange, SLN.StatusTo) AS StatusDuration
FROM
    StatusLog SL
    OUTER APPLY (
        SELECT TOP(1)
            DateOfChange AS StatusTo
        FROM
            StatusLog SLT
        WHERE
            SL.IdUser = SLT.IdUser
            AND SLT.DateOfChange > SL.DateOfChange
        ORDER BY
            SLT.DateOfChange ASC
    ) SLN
    INNER JOIN Users U
        ON SL.IdUser = U.IdUser
    INNER JOIN StatusList SLO -- Old status
        ON SL.OldStatus = SLO.idStatus
    INNER JOIN StatusList SLC -- Current status
        ON SL.NewStatus = SLC.idStatus

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input. From MSDN: Using APPLY

As a side note: The table-valued function could be a subquery too.

I suggest you that use the explicit join syntax (INNER JOIN) instead of the implicit one (list the tables and use the WHERE condition).

Upvotes: 1

Kjetil Watnedal
Kjetil Watnedal

Reputation: 6157

Here is a SQL query returning the desired result simply using a sub select:

SELECT [users].MemberId,
  [users].FirstName,
  [users].LastName,
  thisLog.DateOfChange,
  statusList1.title as OldStatus,
  statuslist2.title as NewStatus,
  (SELECT TOP 1 DATEDIFF(hour,lastLog.DateOfChange,thisLog.DateOfChange)
  from [dbo].[statusLog] lastLog WHERE lastLog.DateOfChange<thisLog.DateOfChange
  ORDER BY DateOfChange desc ) AS HoursSinceLastChange
  FROM [dbo].[statusLog] thisLog
  INNER JOIN [users] ON [users].IdUser=thisLog.IdUSer
  INNER JOIN StatusList statusList1 ON statusList1.idStatus=thisLog.OldStatus
  INNER JOIN StatusList statusList2 on statusList2.idStatus=thisLog.Newstatus
  order by DateOfChange desc

Hopefully I got all your column and table names correct.

Upvotes: 1

DidIReallyWriteThat
DidIReallyWriteThat

Reputation: 1033

When I ran into this problem I inserted into a temp table. That allows you to do a lot of cool things. 

 declare @tab TABLE([Date1] TIME, [DATE2] TIME)

 Insert @tab(SELECT DATE1 ,DATE2 FROM WHEREVER)

 SELECT (DATEDIFF(DATE1,DATE2) AS Difference FROM @tab

Upvotes: 0

Related Questions