How to get minimum date of an ID with respect to startdate?

Question

Post reply

How to get minimum date of an ID with respect to startdate?

Charmer

SSChampion

Points: 14903
More actions
February 8, 2016 at 2:53 am

#307849

Hi Friends,
DECLARE @a TABLE (ID int, StartDate datetime)
DECLARE @b-2 TABLE (ID int, StartDate datetime)
INSERT INTO @b-2
SELECT 1,GETDATE()-5
UNION
SELECT 1,GETDATE()-7
UNION
SELECT 2,GETDATE()-8
UNION
SELECT 3,GETDATE()-10
UNION
SELECT 4,GETDATE()- 12
UNION
SELECT 4,GETDATE()-13
UNION
SELECT 5,GETDATE()-6
UNION
SELECT 6,GETDATE()-7
INSERT INTO @a
SELECT 1,GETDATE()-4
UNION
SELECT 1,GETDATE()-6
UNION
SELECT 2,GETDATE()-7
UNION
SELECT 3,GETDATE()-9
UNION
SELECT 4,GETDATE()- 12
UNION
SELECT 4,GETDATE()-12
UNION
SELECT 5,GETDATE()-6
UNION
SELECT 6,GETDATE()-7
---------------------- Expected Output ------------------------------
SELECT 1AS ID ,'2016-02-02 15:00:11.467' AS StartDate, '2016-02-01 15:00:11.467' AS NextMininmumDateFromTable@B
UNION
SELECT 1,'2016-02-04 15:00:11.467' , '2016-02-03 15:00:11.467'
UNION
SELECT 2,'2016-02-01 15:00:11.467' , '2016-01-31 15:00:11.467'
UNION
SELECT 3,'2016-01-30 15:00:11.467' , '2016-01-29 15:00:11.467'
UNION
SELECT 4,'2016-01-27 15:00:11.467' , '2016-01-26 15:00:11.467'
UNION
SELECT 4,'2016-01-28 15:00:11.467' , '2016-01-26 15:00:11.467'
UNION
SELECT 5,'2016-02-02 15:00:11.467' , '2016-02-02 15:00:11.467'
UNION
SELECT 6,'2016-02-01 15:00:11.467' , '2016-02-01 15:00:11.467'
I have provided the sample DDL script above
I am trying to get minimum date from table B for an ID wrt start date from table A.
I tried the below script but it is not meeting my requirement.
SELECT * FROM @a AS a
JOIN @b-2 AS b ON a.ID = b.ID AND b.StartDate <= a.StartDate
Thanks,
Charmer

Viewing 12 posts - 1 through 11 (of 11 total)

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Quick suggestion

😎

DECLARE @a TABLE (ID int, StartDate datetime)

DECLARE @b-2 TABLE (ID int, StartDate datetime)

INSERT INTO @b-2

SELECT 1,GETDATE()-5 UNION ALL

SELECT 1,GETDATE()-7 UNION ALL

SELECT 2,GETDATE()-8 UNION ALL

SELECT 3,GETDATE()-10 UNION ALL

SELECT 4,GETDATE()- 12 UNION ALL

SELECT 4,GETDATE()-13 UNION ALL

SELECT 5,GETDATE()-6 UNION ALL

SELECT 6,GETDATE()-7

INSERT INTO @a

SELECT 1,GETDATE()-4 UNION ALL

SELECT 1,GETDATE()-6 UNION ALL

SELECT 2,GETDATE()-7 UNION ALL

SELECT 3,GETDATE()-9 UNION ALL

SELECT 4,GETDATE()-12 UNION ALL

SELECT 5,GETDATE()-6 UNION ALL

SELECT 6,GETDATE()-7

SELECT

A.ID

,A.StartDate

,B.StartDate

FROM @a A

INNER JOIN @b-2 B

ON A.ID = B.ID

;WITH BASE_DATA AS

(

SELECT

ROW_NUMBER() OVER

(

ORDER BY A.ID

,A.StartDate

) AS A_RID

,A.ID

,A.StartDate

FROM @a A

)

SELECT

A.ID

,A.StartDate

,MIN(B.StartDate) AS NextMininmumDateFromTable@B

FROM BASE_DATA A

INNER JOIN @b-2 B

ON A.ID = B.ID

WHERE B.StartDate <= A.StartDate

GROUP BY A.ID

,A.A_RID

,A.StartDate;

Output

ID StartDate NextMininmumDateFromTable@B

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

1 2016-02-02 10:16:36.703 2016-02-01 10:16:36.703

1 2016-02-04 10:16:36.703 2016-02-01 10:16:36.703

2 2016-02-01 10:16:36.703 2016-01-31 10:16:36.703

3 2016-01-30 10:16:36.703 2016-01-29 10:16:36.703

4 2016-01-27 10:16:36.703 2016-01-26 10:16:36.703

5 2016-02-02 10:16:36.703 2016-02-02 10:16:36.703

6 2016-02-01 10:16:36.703 2016-02-01 10:16:36.703

Charmer SSChampion Points: 14903 More actions · Answer 2

Hi ,

Thanks for the quick reply. But this script is also not meeting my requirement. If you see ID 1 , The output must be like this

SELECT 1AS ID ,'2016-02-02 ' AS StartDate, '2016-02-01 ' AS NextMininmumDateFromTable@B

UNION

SELECT 1,'2016-02-04 ' , '2016-02-03'

not like this

SELECT 1AS ID ,'2016-02-02 ' AS StartDate, '2016-02-01 ' AS NextMininmumDateFromTable@B

UNION

SELECT 1,'2016-02-04 ' , '2016-02-01'

Thanks,
Charmer

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 3

select a.*, (select max(Startdate) from @b-2 b where b.id=a.id and b.StartDate<=a.StartDate) StartDateB from @a a

How the hell shall i type smaller or equal? 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Put a new line inside you code tags (which is a good idea in general, for readability)

select a.*, (select max(Startdate)

from @b-2 b

where b.id=a.id and b.StartDate <=a.StartDate) StartDateB

from @a a

And be careful, that's likely going to be horrifically slow on anything more than a handful of rows

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Charmer SSChampion Points: 14903 More actions · Answer 5

But now this one is getting failed.

For ID 4, minimum date must be 2016-01-26. but it takes 2016-01-27 because we have used max(startdate) in the script.

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 6

Let me explain the requirement.

I have to show the output with the changes happened to the point of startdate.

If ID 1 has a start date of today, I have to track when was the last time it was changed. changes can happen any day. So for example, if change was happened yesterday, I have to show the details with yesterday's change.

If same ID 1 has a start date '2016-02-05', if '2016-02-01' was last changed to this point, then I have to show the output with ID, startdate, lastchangeddate and other column information from the date of '2016-02-01'

I hope this helps. please let me know if you are not clear.

Thanks,
Charmer

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

Charmer (2/8/2016)
Let me explain the requirement.
I have to show the output with the changes happened to the point of startdate.
If ID 1 has a start date of today, I have to track when was the last time it was changed. changes can happen any day. So for example, if change was happened yesterday, I have to show the details with yesterday's change.
If same ID 1 has a start date '2016-02-05', if '2016-02-01' was last changed to this point, then I have to show the output with ID, startdate, lastchangeddate and other column information from the date of '2016-02-01'
I hope this helps. please let me know if you are not clear.

Your requirement is quite simple, however your output doesn't match your sample data - there are errors. Fix your sample data and you will probably find that at least one of the solutions offered will work.

Here's another solution:

DECLARE @a TABLE (ID int, StartDate datetime)

DECLARE @b-2 TABLE (ID int, StartDate datetime)

INSERT INTO @b-2

SELECT 1,GETDATE()-5 UNION ALL

SELECT 1,GETDATE()-7 UNION ALL

SELECT 2,GETDATE()-8 UNION ALL

SELECT 3,GETDATE()-10 UNION ALL

SELECT 4,GETDATE()-12 UNION ALL

SELECT 4,GETDATE()-13 UNION ALL

SELECT 5,GETDATE()-6 UNION ALL

SELECT 6,GETDATE()-7

INSERT INTO @a

SELECT 1,GETDATE()-6 UNION ALL

SELECT 1,GETDATE()-4 UNION ALL

SELECT 2,GETDATE()-7 UNION ALL

SELECT 3,GETDATE()-9 UNION ALL

SELECT 4,GETDATE()-12 UNION ALL

SELECT 4,GETDATE()-11 UNION ALL

SELECT 5,GETDATE()-6 UNION ALL

SELECT 6,GETDATE()-7

---------------------- Expected Output ------------------------------

SELECT 1AS ID ,'2016-02-02 15:00:11.467' AS StartDate, '2016-02-01 15:00:11.467' AS NextMininmumDateFromTable@B UNION

SELECT 1,'2016-02-04 15:00:11.467' , '2016-02-03 15:00:11.467' UNION

SELECT 2,'2016-02-01 15:00:11.467' , '2016-01-31 15:00:11.467' UNION

SELECT 3,'2016-01-30 15:00:11.467' , '2016-01-29 15:00:11.467' UNION

SELECT 4,'2016-01-27 15:00:11.467' , '2016-01-27 15:00:11.467' UNION

SELECT 4,'2016-01-28 15:00:11.467' , '2016-01-27 15:00:11.467' UNION

SELECT 5,'2016-02-02 15:00:11.467' , '2016-02-02 15:00:11.467' UNION

SELECT 6,'2016-02-01 15:00:11.467' , '2016-02-01 15:00:11.467'

SELECT *

FROM @a a

OUTER APPLY (

SELECT NextMininmumDateFromTable@B = MAX(StartDate)

FROM @b-2 b

WHERE b.ID = a.ID AND b.StartDate <= a.StartDate

) x

^{“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

Charmer SSChampion Points: 14903 More actions · Answer 8

Thank you, Chris. I am sorry for providing the wrong DDL.

Yes, the script is fine. Thank you guys...:-)

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 9

OMG,

I am sorry, Chris. The script is still wrong.

Just see the screen shot attached in here. The ID 4, having startdate '2016-01-27' should show '2016-01-26'

instead of '2016-01-27'. because we have '2016-01-26' is the minimum date to '2016-01-27'

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 10

Charmer (2/8/2016)
OMG,
I am sorry, Chris. The script is still wrong.
Just see the screen shot attached in here. The ID 4, having startdate '2016-01-27' should show '2016-01-26'
instead of '2016-01-27'. because we have '2016-01-26' is the minimum date to '2016-01-27'

I am sorry. I was confused. Script is absolutely working fine.

Thank you so much , friends. I am sorry again, pls.

Thanks,
Charmer

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

Charmer (2/8/2016)
OMG,
I am sorry, Chris. The script is still wrong.
Just see the screen shot attached in here. The ID 4, having startdate '2016-01-27' should show '2016-01-26'
instead of '2016-01-27'. because we have '2016-01-26' is the minimum date to '2016-01-27'

Easy enough - correct your scripts. Also, rather than using GETDATE() and arithmetic, use hardcoded date values.

^{“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