Interesting query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Interesting query

Author

Topic

sridhar3004
Starting Member

34 Posts

Posted - 2014-08-06 : 00:16:39

I have the following data

Startdate EndDate Available
2014-07-01 2014-07-05 1
2014-07-06 2014-07-10 1
2014-07-11 2014-07-15 0
2014-07-16 2014-07-20 0
2014-07-21 2014-07-26 1
2014-07-27 2014-07-31 1

I want a query that will retrieve the start date and end date of all the available time

so for the above data, the first available slot will be 2014-07-01 and 2014-07-10 (this is the start date of first record and end date of second row)

rows 3 and 4 is not available and will not be a part of the available slots query

the second available slot will 21 Jul 14 to 31 Jul 2014

so only 2 rows will be displayed as an output from the above data

Thanks
Sridhar

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-06 : 07:37:45

What else do you need to see, it sounds very simple what you are trying at the moment:

Select StartDate, EndDate
From YourTable
Where Available = 1
Order By StartDate

Or use this

Select StartDate, EndDate, 'Available Slots' = Case
When StartDate = 1
OR EndDate = 1
Then 'Available'
Else 'Not Available'
End
From YourTable
Order By StartDate

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources