CODEWITHSUNDEEP
asp.netsqlgridviewsql-server-2012
Silentbob
Silentbob

Reputation: 3065

Selecting data between dates in SQL

I am using SQL Server 2012 and I want to create a query that I can use in a gridview.

Basically the data is a list of statuses (restricted, unavailable and available) and each has a datetime next to them.

I want to display in my gridview the dates and statuses when a certain status was held.

So for example

enter image description here

The other issue I have is the top line of the grid ie if the status was changed today I need to show that.

My SQL skills aren't up to this so any help offered would be great.

Upvotes: 1

Views: 115

Answers (2)

valex
valex

Reputation: 24144

In MSSQL 2012 you can use LEAD() function

select Status,
       StartDate,
       LEAD(StartDate, 1,null) 
         OVER (ORDER BY startDate) 
        as EndDate
FROM T
ORDER BY StartDate

SQLFiddle demo

Upvotes: 1

MatBailie
MatBailie

Reputation: 86765

SELECT
  Status_Current.yourDate   AS Date_From,
  Status_Next.yourDate      AS Date_To,
  Status_Current.yourStatus AS Status
FROM
  yourData   AS Status_Current
LEFT JOIN
  yourData   AS Status_Next
    ON Status_next.yourData = (SELECT TOP 1 lookup.yourDate
                                 FROM yourData   AS lookup
                                WHERE lookup.yourDate > Status_Current.yourDate
                             ORDER BY lookup.yourDate ASC
                              )
ORDER BY
  Status_Current.yourDate DESC

Upvotes: 0

Related Questions