<div>
Which version of SQL Server?
</div>

<div>
please take into account this article: 
<a href="https://www.experts-exchange.com/Database/MS_Access/A_1555-Analytical-SQL-Where-do-you-rank.html" rel="ugc">https://www.experts-exchange.com/Database/MS_Access/A_1555-Analytical-SQL-Where-do-you-rank.html</a> 
 
now, the issue with sql server is that it does not have a &quot;LEAD&quot; or &quot;LAG&quot; function like Oracle does. 
hence, you will need to self-join the table on a &quot;row number&quot; basis, joining on &nbsp;+- 1 on that value. 
 
hope this helps as rough input
</div>

<div>
SQL Server 2012 onwards does have a LEAD/LAG function. See <a href="http://msdn.microsoft.com/en-us/library/hh231256.aspx" rel="ugc nofollow">http://msdn.microsoft.com/en-us/library/hh231256.aspx</a>&nbsp;for details. 
 
So, which version of SQL Server?
</div>

<div>
When we order by date, e.g. 
 

<pre><code class="code-10 nolinks" id="code-8267643">DECLARE @Sample TABLE
 (
 appid INT ,
 statusid INT ,
 startdate DATE
 );

INSERT INTO @Sample
VALUES ( 111, 7, '20141027' ),
 ( 111, 7, '20140919' ),
 ( 111, 3, '20140318' ),
 ( 111, 8, '20140627' ),
 ( 111, 9, GETDATE() ),
 ( 123, 1, '20150101' ),
 ( 123, 1, '20150102' ),
 ( 123, 2, '20150103' );

WITH OrderedStatus
 AS ( SELECT S.appid ,
 S.startdate ,
 S.statusid ,
 ROW_NUMBER() OVER ( PARTITION BY S.appid, S.statusid ORDER BY S.startdate ) AS RN
 FROM @Sample S
 )
 SELECT O.appid ,
 O.startdate ,
 O.statusid ,
 LAG(O.startdate, 1, NULL) OVER ( PARTITION BY O.appid ORDER BY O.startdate ) AS DateLast ,
 DATEDIFF(DAY, LAG(O.startdate, 1, NULL) OVER ( PARTITION BY O.appid ORDER BY O.startdate ), O.startdate) AS DaysSinceDateLast
 FROM OrderedStatus O
 WHERE O.RN = 1
 ORDER BY O.appid ,
 O.startdate;

WITH OrderedStatus
 AS ( SELECT S.appid ,
 S.startdate ,
 S.statusid ,
 ROW_NUMBER() OVER ( PARTITION BY S.appid, S.statusid ORDER BY S.startdate ) AS RN
 FROM @Sample S
 ),
 OrderedApp
 AS ( SELECT O.appid ,
 O.startdate ,
 O.statusid ,
 ROW_NUMBER() OVER ( PARTITION BY O.appid ORDER BY O.startdate ) AS RN
 FROM OrderedStatus O
 WHERE O.RN = 1
 )
 SELECT L.appid ,
 L.startdate ,
 L.statusid ,
 R.startdate AS DateLast ,
 DATEDIFF(DAY, R.startdate, L.startdate) AS DaysSinceDateLast
 FROM OrderedApp L
 LEFT JOIN OrderedApp R ON R.appid = L.appid
 AND R.RN = L.RN - 1
 ORDER BY L.appid ,
 L.startdate;
</code></pre>
</div>

<div>
Hi Philip, my version is sql server 2008 r2
</div>

<div>
What if the data was this? 
 
appid statusid &nbsp; &nbsp; startdate 
111 &nbsp; &nbsp; &nbsp; 7 &nbsp; &nbsp; &nbsp; 2014-10-27 00:00:00 &nbsp; 
111 &nbsp; &nbsp; &nbsp; 7 &nbsp; &nbsp; &nbsp; 2014-09-19 00:00:00 &nbsp; 
111 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 2014-03-18 00:00:00 &nbsp; 
111 &nbsp; &nbsp; &nbsp; 8 &nbsp; &nbsp; &nbsp; 2014-11-27 00:00:00 &nbsp; 
111 &nbsp; &nbsp; &nbsp; 8 &nbsp; &nbsp; &nbsp; 2014-06-27 00:00:00 &nbsp; 
111 &nbsp; &nbsp; &nbsp; 9 &nbsp; &nbsp; &nbsp; 2014-12-27 00:00:00 &nbsp; 
 
What information would you want?
</div>

<div>
Maybe 
<pre><code class="code-1 nolinks" id="code-8267645">appid startdate statusid DateLast DaysSinceDateLast
----------- ---------- ----------- ---------- -----------------
111 2014-03-18 3 NULL NULL
111 2014-06-27 8 2014-03-18 101
111 2014-09-19 7 2014-06-27 84
111 2014-12-27 9 2014-09-19 99
</code></pre>
?
</div>

<div>
Hi Philip, 
in your data, since the below line has the most recent date, then I want the difference of days of 27th December 2014 and &nbsp;the next previous status change which is on 27the Nov 2014 
 
111 &nbsp; &nbsp; &nbsp; 9 &nbsp; &nbsp; &nbsp; 2014-12-27 00:00:00 &nbsp; 
111 &nbsp; &nbsp; &nbsp; 8 &nbsp; &nbsp; &nbsp; 2014-11-27 00:00:00
</div>

<div>
Hi ste5phan, I cannot use your query as I am using sql server 2008... 
 
Msg 195, Level 15, State 10, Line 28 
'LAG' is not a recognized built-in function name.
</div>

<div>
Kidding me? Just use the second one..
</div>

<div>
How about this: 
 

<pre><code class="code-10 nolinks" id="code-20-40540254-1">DECLARE @Sample TABLE
 (
 appid INT ,
 statusid INT ,
 startdate DATE
 );

INSERT INTO @Sample
VALUES ( 111, 7, '20141027' ),
 ( 111, 7, '20140919' ),
 ( 111, 3, '20140318' ),
 ( 111, 8, '20140627' ),
 ( 111, 8, '20141127' ),
 ( 111, 9, '20141227' );

with myTable as
(Select appid, statusid, min(startdate) as MinDate, max(startdate) as MaxDate, 
case statusID
when (select max(statusid) from @Sample as U where T.appid = U.appid) then 1 else 0 end as MaxStatusID
from @Sample as T
group by appid, statusid)
select appid, (select min(MinDate) from myTable as V where MaxStatusID = 1 and U.appid = V.appid) as FirstDateOfLatestStatus,
(select max(MaxDate) from myTable as V where MaxStatusID = 0 and U.appid = V.appid) as MaxDateOfPreviousStatus,
DATEDIFF(day,(select max(MaxDate) from myTable as V where MaxStatusID = 0 and U.appid = V.appid),(select min(MinDate) from myTable as V where MaxStatusID = 1 and U.appid = V.appid)) as DayDifference
from myTable as U
group by appid
</code></pre>
</div>

<div>
sorry ste5phan, just saw your second bit of the code. 
 
I only want to see one record in the output which is the number of days difference between the most recent status and one before that. &nbsp;we dont need to bother about the previous history. can you please update your query to show this ? 
 
Many Thanks
</div>

<div>
I only want to see one record in the output Per each application. which is the number of days difference between the most recent status and one before that. &nbsp;we dont need to bother about the previous history. 
 
sorry, I should have mentioned this originally.
</div>

<div>
MY code gives: 
 
111 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2014-12-27 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2014-11-27 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;30
</div>

<div>
ste5an solution was easy to understand and gave me the result. 
Many thanks to others also.
</div>

<div>
<div class="content wysiwyg-content">
Hi, i have a table called test and below is my sample data for an application id. 
 
so basically I want get the count in Days since the last status has changed based on the start date per each application. So in this example, it has to be the difference in days between the first row and the fourth row because eventough the second row is showing as the last changed date after 27th october 2014, it still got the same status as 7 so the status has not really changes so the query should look at the next latest date when the status was changed which is 4th record . 
 
Can anyone please give me that tsql code please ? 
 
 
 
 
appid statusid &nbsp; &nbsp; startdate 
111 &nbsp; &nbsp; &nbsp; 7 &nbsp; &nbsp; &nbsp; 2014-10-27 00:00:00 &nbsp; 
111 &nbsp; &nbsp; &nbsp; 7 &nbsp; &nbsp; &nbsp; 2014-09-19 00:00:00 &nbsp; 
111 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 2014-03-18 00:00:00 &nbsp; 
111 &nbsp; &nbsp; &nbsp; 8 &nbsp; &nbsp; &nbsp; 2014-06-27 00:00:00 &nbsp; 
 
Many Thanks
</div>
</div>

Hi, i have a table called test and below is my sample data for an application id.

so basically I want get the count in Days since the last status has changed based on the start date per each application. So in this example, it has to be the difference in days between the first row and the fourth row because eventough the second row is showing as the last changed date after 27th october 2014, it still got the same status as 7 so the status has not really changes so the query should look at the next latest date when the status was changed which is 4th record .

Can anyone please give me that tsql code please ?




appid statusid     startdate
111       7       2014-10-27 00:00:00  
111       7       2014-09-19 00:00:00  
111       3       2014-03-18 00:00:00  
111       8       2014-06-27 00:00:00  

Many Thanks

SQL Server Partioning or Ranking ?

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.