Returning Row Plus Next Newest Based On Date

Thursday, January 31, 2013 4:15 AM

Hi All,

I have a requirement to find a row based on a supplied date (which I'm able to do), but I also need to return the next newest following the current row returned.

Table DDL

CREATE TABLE [dbo].[Ranges](
	[RangeID] [varchar](50) NOT NULL,
	[RangeEndDate] [datetime] NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL)
GO

Sample Data

INSERT INTO Ranges 
	(RangeID, RangeEndDate, StartDate, EndDate) 
VALUES 
	('1', '2012-11-15', '2012-11-01', '2012-11-15'),
	('2', '2012-11-30', '2012-11-16', '2012-11-30'),
	('3', '2012-12-15', '2012-12-01', '2012-12-15'),
	('4', '2012-12-31', '2012-12-16', '2012-12-31'),
	('5', '2013-01-15', '2013-01-01', '2013-01-15'),
	('6', '2013-01-31', '2013-01-16', '2013-01-31')

Now, using the below query I can get the appropriate PeriodEndDate (which would be RangeID 2, PeriodEndDate 2012-11-30).

SELECT 
	TimePeriodID, 
	PeriodEndDate  
FROM 
	TimePeriods 
WHERE 
	(StartDate <= '11/25/2012' AND EndDate >= '11/25/2012')  
ORDER BY 
	PeriodEndDate DESC

My problem is that I need to include not only RangeID 2 in the resultset, but RangeID 3 as well as it would be the next newest PeriodEndDate in the table.

Any suggestions are greatly appreciated!

All Replies

Thursday, January 31, 2013 4:49 AM

0
No problemo.

OK one problem. You need to mess around with Casts or add another column of type int that refects RangeId.

Since your time periods don't overlap this will work. The code was not tested but you get the idea. Is there a faster way to do it, maybe someone more experienced will have something off the top of their heads.

IF you get 2 ranges one after the other there will be duplicate rows so an AND clause on the innerjoin to make sure the row is not already in #temp will do (or some other method to filter those out like creating distinct table from #temp one or some SQL I did not think of)

I hope this helps:
```
SELECT  RangeID,
        (RangeId+1) as RangeIDPlusOne,
	TimePeriodID, 
	PeriodEndDate into #temp 
FROM 
	TimePeriods 
WHERE 
	(StartDate <= '11/25/2012' AND EndDate >= '11/25/2012')  
ORDER BY 
	PeriodEndDate DESC

GO

--Do an inner joint to get al next row items (because your time periods dont overlap and are nicely ordered)
SELECT RangeID,
       (99999) as RangeIDPlusOne,
	TimePeriodID, 
	PeriodEndDate
into #temp2
from
TimePeriods l

inner join #temp r
on l.rangeID = r.RangeIDPlusOne

--Do a union to merge the 2 tables and you are done
```
- Edited by CountryStyle Thursday, January 31, 2013 4:52 AM
- Reply
- Quote
Thursday, January 31, 2013 5:04 AM

0
```
with CTE as
(Select ROW_NUMBER() Over(order by enddate) rownumber,*from Ranges)
select Rangeid,enddate from CTE
WHERE 
	(StartDate <= '11/25/2012' AND EndDate >= '11/25/2012')
	or rownumber in (select rownumber+1 from CTE WHERE 
	(StartDate <= '11/25/2012' AND EndDate >= '11/25/2012')
)
```
Something of sort

Regards,Eshwar.
Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.
- Proposed As Answer by Kalman TothMicrosoft Community Contributor, Moderator Sunday, February 03, 2013 10:44 PM
- Unproposed As Answer by 2012S4 Sunday, February 03, 2013 10:46 PM
- Reply
- Quote
Thursday, January 31, 2013 6:27 AM

Answerer

0
SQL Server 2012 only

WITH cte
AS
(
SELECT * FROM
(
SELECT
*,
LEAD(RangeID) OVER (ORDER BY RangeEndDate
) NextRangeIDValue
FROM
Ranges
) AS Der WHERE
(StartDate <= '11/25/2012' AND EndDate >= '11/25/2012')
) SELECT DISTINCT Ranges.* FROM cte
JOIN Ranges ON cte.NextRangeIDValue=Ranges.RangeID

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
- Edited by Uri DimantMVP, Editor Thursday, January 31, 2013 6:29 AM
- Reply
- Quote

Thursday, January 31, 2013 6:02 PM

Hi Eswararao,

Thanks for the reply. I've tested this query and it's working as expected. However, I now need to expand on the query and am running into problems. Below is DDL, Sample Data and example query.

CREATE TABLE [dbo].[Sheets](
	[SheetID] [varchar](50) NOT NULL,
	[RangeID] [varchar](50) NULL,
	[UserID] [varchar](50) NULL,
	[StatusCode] [varchar](50) NULL)
GO


INSERT INTO Sheets
        (SheetID, RangeID, UserID, StatusCode)
VALUES 
        ('1', '1', 'User1', 'S03'),
        ('2', '1', 'User1', 'S03'),
        ('3', '1', 'User1', 'S01'),
        ('4', '2', 'User1', 'S01'),
        ('5', '2', 'User2', 'S01'),
        ('6', '3', 'User1', 'S01')
GO


DECLARE @sheetDate datetime
DECLARE @userID varchar(50)

SET @sheetDate = '11/25/2012'
SET @userID = 'User1'
;

WITH CTE AS
(
SELECT 
	ROW_NUMBER() OVER(ORDER BY A.RangeEndDate ASC) AS RowNumber, 
	A.RangeID, 
	A.RangeEndDate, 
	A.StartDate, 
	A.EndDate, 
	B.SheetID, 
	B.UserID, 
	B.StatusCode 
FROM 
	Ranges A 
LEFT JOIN 
	Sheets B ON (A.RangeID = B.RangeID) 
WHERE 
	(B.UserID = @userID OR B.UserID IS NULL) 
)
SELECT 
	* 
FROM 
	CTE 
WHERE 
	(StartDate <= @sheetDate AND EndDate >= @sheetDate) 
OR 
	RowNumber IN (SELECT 
				      RowNumber + 1 
				  FROM 
				      CTE 
				  WHERE 
					  (StartDate <= @sheetDate AND EndDate >= @sheetDate))

The Sample Data for the Sheets table illustrates that there may be more than one Sheet, for one User, for a Single Range with different StatusCodes (this is caused by remote site users creating Sheets before they're supposed to). Now this query works as expected, *if there is one Sheet for each Range for the User*. Essentially the resultset is only showing two rows regardless of how many Sheets there are for the two Ranges/User. I'd like to be able to return them all. Is that possible?

Thanks.

Edited by 2012S4 Thursday, January 31, 2013 6:03 PM

Thursday, January 31, 2013 6:04 PM

0

Hi Uri,

Thanks for the reply. Should have mentioned I am running SQL Server 2008 R2. Appreciate your assistance though!
- Reply
- Quote
Thursday, January 31, 2013 6:06 PM

0

Hi CountryStyle,

Thanks for the reply, the help is appreciated. Looking for a solution I can execute in a single step though.
- Reply
- Quote
Thursday, January 31, 2013 8:09 PM

0
Hi CountryStyle,

Thanks for the reply, the help is appreciated. Looking for a solution I can execute in a single step though.

Well.....I remember Perl script where guys wanted to be fancy and complicated and put everything in one line.

It did not help anything but it did make it harder to read the code later. But, you are right I think this can be done better and get rid of the Union:

replace this
```
inner join #temp r
on l.rangeID = r.RangeIDPlusOne
```
With
```
inner join (select ....your first query) as r
on l.rangeID = r.RangeIDPlusOne
OR l.rangeId = r.RageId
```
- Edited by CountryStyle Thursday, January 31, 2013 8:10 PM
- Reply
- Quote
Friday, February 01, 2013 4:07 AM

0

What is the expected output in this case?

Regards,Eshwar.
Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.
- Reply
- Quote
Friday, February 01, 2013 4:50 AM

0
Hi Eswararao,

Thanks for the continuing assistance. My previous response was a bit confusing and I apologize for that. If I use a @sheetDate in the query for which I know there has not been a Sheet created by the User for the Range ahead of the current Range, the result looks like this:

RowNumber | RangeID | RangeEndDate | StartDate | EndDate | SheetID | UserID

44 | 1 | 2013-01-31 | 2013-01-16 | 2013-01-31 | 1 | User1

45 | 2 | 2013-02-15 | 2013-02-01 | 2013-02-15 | NULL | NULL

Which (in that case) is what I want. However, if there is a Sheet created by the User for the Range ahead of the current Range, I need the query to skip ahead (but also include) however many rows to get me the next newest (unused) Range (ie where a Sheet does not exist for that Range). So for example, I use a @sheetDate of '2012-12-07' the query shows me:

RowNumber | RangeID | RangeEndDate | StartDate | EndDate | SheetID | UserID

40 | 1 | 2012-12-15 | 2012-12-01 | 2012-12-15 | 16 | User1

41 | 1 | 2012-12-15 | 2012-12-01 | 2012-12-15 | 18 | User1 -- Two Sheets for the Same Range, Need Them All To Show Up, Which They Do

42 | 2 | 2012-12-31 | 2012-12-16 | 2012-12-31 | 19 | User1 -- Include In Resultset As Well

43 | 3 | 2012-01-15 | 2012-01-01 | 2012-01-15 | 20 | User1 -- Include In Resultset As Well

44 | 4 | 2012-01-31 | 2012-01-16 | 2012-01-31 | 21 | User1 -- Include In Resultset As Well

45 | 5 | 2012-02-15 | 2012-02-01 | 2012-02-15 | NULL | NULL -- Because This Is The Next Newest Unused Sheet for User

Hopefully this illustrates things clearer. Thanks.
- Edited by 2012S4 Friday, February 01, 2013 4:58 AM
- Reply
- Quote
Sunday, February 03, 2013 10:47 PM

0

Hi Eswararao,

I'd replied to your later reply regarding expected output later on in the thread, not sure if you saw it. Please give it a look and let me know your thoughts.

Thanks.
- Reply
- Quote
Monday, February 04, 2013 8:52 PM

0

yes.. because using windowed frames in the OVER() clause is new in 2012:

http://msdn.microsoft.com/en-us/library/ms189461.aspx

And the using window frames is quite powerful.
- Reply
- Quote

Returning Row Plus Next Newest Based On Date

All Replies

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?