Closest less date

Question

Closest less date

rb76

SSC Enthusiast

Points: 144
More actions
June 17, 2016 at 4:56 am

#319483

Hi,
I'm search for a solution how to find the nearest less date to a given date.
Here is a example:
Table1:
ItemNr | FromDate |
---------------
1 | 01.01.2016
1 | 01.02.2016
2 | 01.01.2016
2 | 01.02.2016
2 | 01.03.2016
2 | 01.07.2016
3 | 01.01.2016
3 | 01.03.2016
3 | 01.06.2016
The result should be, when I specify a date like 01.05.2016
ItemNr | FromDate |
---------------
1 | 01.02.2016
2 | 01.03.2016
3 | 01.03.2016
I found some solution via google, but they search always for the date which is the closest one. No matter if the date is less or higher to the given date.
Thanks in advance!!!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

When you closest, What exactly you mean by that ?

In your sample output you have shown 01-02-2016 and your input date is 01-05-2016. That means as per your requirement your closest date should atleast 3 days less then your input date?

If the above understanding is correct then Its a very simple solution

---- Data Prep

Declare @ItemTable table

( ItemNr int, FromDate date)

insert into @ItemTable

select 1 , '01.01.2016'union all

select 1 , '01.02.2016'union all

select 2 , '01.01.2016'union all

select 2 , '01.02.2016'union all

select 2 , '01.03.2016'union all

select 2 , '01.07.2016'union all

select 3 , '01.01.2016'union all

select 3 , '01.03.2016'union all

select 3 , '01.06.2016'

------------- Special Case

insert into @ItemTable

select 4 , '01.01.2015'union all

select 4 , '01.03.2015'union all

select 4 , '01.06.2015'

--- Declare the Input Date

Declare @InputDate date = '01.05.2016'

--- Solution

select *

from

(

Select ItemNr, FromDate, DATEDIFF(dd,FromDate, @InputDate) as DateDifference

, ROW_NUMBER() OVER (Partition by ItemNr ORder by FromDate desc) AS rnkId

from @ItemTable

Where FromDate < @InputDate

) A

where rnkId = 1

Hope it helps.

Edit: Well, after sharing script i realized that any date can be closest to the given date. so change my logic and added a special case in the data prep too.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

DECLARE @FromDate DATE = CONVERT(DATE,'01.05.2016',104);

WITH Table1 (ItemNr, FromDate) AS (

SELECT 1 , CONVERT(DATE,'01.01.2016',104) UNION ALL

SELECT 1 , CONVERT(DATE,'01.02.2016',104) UNION ALL

SELECT 2 , CONVERT(DATE,'01.01.2016',104) UNION ALL

SELECT 2 , CONVERT(DATE,'01.02.2016',104) UNION ALL

SELECT 2 , CONVERT(DATE,'01.03.2016',104) UNION ALL

SELECT 2 , CONVERT(DATE,'01.07.2016',104) UNION ALL

SELECT 3 , CONVERT(DATE,'01.01.2016',104) UNION ALL

SELECT 3 , CONVERT(DATE,'01.03.2016',104) UNION ALL

SELECT 3 , CONVERT(DATE,'01.06.2016',104)

)

SELECT ItemNr, FromDate

FROM (

SELECT *, rn =ROW_NUMBER() OVER(PARTITION BY ItemNr ORDER BY FromDate DESC)

FROM Table1

WHERE FromDate < @FromDate

) d

WHERE rn = 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rb76 SSC Enthusiast Points: 144 More actions · Answer 3

Hi,

thanks for the answers. But I think I didn't explain my question not good enough.

The table contains about 30000 lines. So I think I cannot make my query with the lines "SELECT 1 , CONVERT(DATE,'01.01.2016',104) UNION ALL" ...

The result of the query should always be the nearest date to the input date per ItemNr. But always the lesser date to the input date. Not a date that is higher than the input date.

Table1:

ItemNr | FromDate |

---------------

1 | 01.01.2016

1 | 01.02.2016

2 | 01.01.2016

2 | 01.02.2016

2 | 01.03.2016

2 | 01.07.2016

3 | 01.01.2016

3 | 01.03.2016

3 | 01.06.2016

The result should be, when I specify a inputdate like 01.05.2016

ItemNr | FromDate |

---------------

1 | 01.02.2016 (nearest lesser date from ItemNr 1)

2 | 01.03.2016 (nearest lesser date from ItemNr 2)

3 | 01.03.2016 (nearest lesser date from ItemNr 3)

Thank you!!!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

rb76 (6/18/2016)
Hi,
thanks for the answers. But I think I didn't explain my question not good enough.
The table contains about 30000 lines. So I think I cannot make my query with the lines "SELECT 1 , CONVERT(DATE,'01.01.2016',104) UNION ALL" ...
The result of the query should always be the nearest date to the input date per ItemNr. But always the lesser date to the input date. Not a date that is higher than the input date.
Table1:
ItemNr | FromDate |
---------------
1 | 01.01.2016
1 | 01.02.2016
2 | 01.01.2016
2 | 01.02.2016
2 | 01.03.2016
2 | 01.07.2016
3 | 01.01.2016
3 | 01.03.2016
3 | 01.06.2016
The result should be, when I specify a inputdate like 01.05.2016
ItemNr | FromDate |
---------------
1 | 01.02.2016 (nearest lesser date from ItemNr 1)
2 | 01.03.2016 (nearest lesser date from ItemNr 2)
3 | 01.03.2016 (nearest lesser date from ItemNr 3)
Thank you!!!

-- ChrisM solution using the "WITH (CTE)" statement was only to provide some sample data that demonstrated the solution

-- in most cases it helps you and everyone else if you provide sample data as a set up script

-- please read this article https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

-- maybe below you will find the same solution but using a temp table .

CREATE TABLE #Table1(

ItemNr INTEGER NOT NULL

,FromDate DATE NOT NULL

);

INSERT INTO #Table1(ItemNr,FromDate) VALUES (1,'2016-01-01');

INSERT INTO #Table1(ItemNr,FromDate) VALUES (1,'2016-02-01');

INSERT INTO #Table1(ItemNr,FromDate) VALUES (2,'2016-01-01');

INSERT INTO #Table1(ItemNr,FromDate) VALUES (2,'2016-02-01');

INSERT INTO #Table1(ItemNr,FromDate) VALUES (2,'2016-03-01');

INSERT INTO #Table1(ItemNr,FromDate) VALUES (2,'2016-07-01');

INSERT INTO #Table1(ItemNr,FromDate) VALUES (3,'2016-01-01');

INSERT INTO #Table1(ItemNr,FromDate) VALUES (3,'2016-03-01');

INSERT INTO #Table1(ItemNr,FromDate) VALUES (3,'2016-06-01');

DECLARE @FromDate DATE = CONVERT(DATE,'01.05.2016',104);

SELECT ItemNr, FromDate

FROM (

SELECT *, rn =ROW_NUMBER() OVER(PARTITION BY ItemNr ORDER BY FromDate DESC)

FROM #Table1 -- REPLACE AS NECESSARY

WHERE FromDate < @FromDate

) d

WHERE rn = 1

DROP TABLE #Table1

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

rb76 SSC Enthusiast Points: 144 More actions · Answer 5

Hi,

thanks I understand. This query runs perfectly for me in SQL server.

I tried it also in MS Access, but it have problems with the "SELECT *, rn =ROW_NUMBER() OVER(PARTITION BY ItemRelation ORDER BY FromDate DESC)" line. I think the ROW_NUMBER() function is not included in Access.

Do you have a solution for this problem as well?

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

Is the row_number even necessary? Don't you just want: -

SELECT [ItemNr],

MAX([FromDate]) AS FromData

FROM #Table1

WHERE FromDate < @FromDate

GROUP BY [ItemNr];

e.g. get me the max date per item number where the date is less than the specified date.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/