Query dilemma - join on most recent date

Question

Hi - I know what I need, but I can't figure out how to get from the parts to the desired result...   I want to end up with:

desired query would give me:

Name,MeasurementDate,DateStart,DateEnd

Adam,1/1/2001,1/15/2001,12/31/2001

Adam,2/2/2002,2/15/2002,12/31/2002

Adam,2/2/2002,3/15/2002,12/31/2003

because - for each Test_assign row, I want to find the most recent measurement date

CREATE TABLE [dbo].[test_assign](
	[name] [varchar](25) NULL,
	[datestart] [date] NULL,
	[dateEnd] [date] NULL,
	[assign_id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_test_assign] PRIMARY KEY CLUSTERED 
(
	[assign_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[test_measure](
	[name] [varchar](25) NULL,
	[measurementDate] [date] NULL,
	[measure_id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_test_measure] PRIMARY KEY CLUSTERED 
(
	[measure_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into Test_Measure (Name,Measurementdate) select 'Adam','1/1/2001'
insert into Test_Measure (Name,Measurementdate) select 'Adam','2/2/2002'
insert into Test_assign (Name,DateStart,DateEnd) select 'Adam','1/15/2001','12/31/2001'
insert into Test_assign (Name,DateStart,DateEnd) select 'Adam','2/15/2002','12/31/2002'
insert into Test_assign (Name,DateStart,DateEnd) select 'Adam','3/15/2003','12/31/2003'

appreciate your insight and ask questions if my question is not clear!

---

Marion in Hudson Valley

Answer 1

How these two tables are related? Are they related by name?

In any case, take a look at these 2 blogs explaining solutions to this problem in details:

Including an Aggregated Column's Related Values - Erik and mine blog presenting several solutions of the problem with explanations for each
Including an Aggregated Column's Related Values - Part 2 - my blog post with use cases for the previous blog

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 2

they are related on name (in real life, on name_id) and a date relationship - it's the date relationship that I'm having troube with.

I'll look at your links 

Marion

---

Marion in Hudson Valley

Answer 3

select TA.*, M.MeasurementDate 
FROM Test_Assign TA 
OUTER APPLY (SELECT TOP (1) * from Test_Measure TM where TM.Name = TA.Name
AND TM.MeasurementDate <=TA.DateEnd 
ORDER BY TM.MeasurementDate DESC) M

May be this is what you're looking for based on the results you show.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 4

WOW !  That does work with my sample data!   I'll load up some additional inserts and be back soon to mark this as the answer!  Thank you Naomi!

---

Marion in Hudson Valley

Answer 5

I modified slightly to compare the measurement date against the start date (not the end date) , and it's perfect - thank you !

select TA.*, M.MeasurementDate 
FROM Test_Assign TA 
OUTER APPLY (SELECT TOP (1) * from Test_Measure TM 
where TM.name = TA.name
AND TM.MeasurementDate <=TA.Datestart
ORDER BY TM.MeasurementDate DESC) M

---

Marion in Hudson Valley

Answer 6

Please read any book on data modeling. Why do you think that “name” is clear and precise? Why does it change from table to table? Why do you think that the physical insertion attempts to a disk drive (aka IDENTITY) are part of a logical data model? Because you still think of a magnetic tape file with its record numbers. Why are all the columns NULL-able? Why do you use verbs to name tables? They are sets of entities, so use nouns. 

You do not know  ANSI-ISO Standards. The ISO-8601 dates are the only format allowed. We also have the row constructor syntax for INSERT INTO in T-SQL now. 


>> because - for each Test_Assignment row, I want to find the most recent measurement date. << 

CREATE TABLE Test_Measurements
(student_name VARCHAR(25) NOT NULL,
 measurement_date DATE NOT NULL,
 PRIMARY KEY (student_name, measurement_date));

INSERT INTO Test_Measurements 
VALUES ('Adam', '2001-01-01'),
       ('Adam', '2002-02-02');

CREATE TABLE Test_Assignment
(student_name VARCHAR(25) NOT NULL, 
 test_start_date DATE NOT NULL, 
 test_end_date DATE NOT NULL,
 CHECK (test_start_date <= test_end_date), --- important!
 PRIMARY KEY (student_name, test_start_date)); -- real key! 

INSERT INTO Test_Assignment
VALUES 
('Adam', '2001-01-15', '2001-12-31'),
('Adam', '2002-02-15', '2002-12-31'),
('Adam', '2003-03-15', '2003-12-31');

SELECT A.student_name, A.test_start_date, A.test_end_date,
       M1.measurement_date
  FROM Test_Assignment AS A,
       Test_Measurements AS M1
 WHERE M1.measurement_date 
       = (SELECT MAX(M2.measurement_date)
    FROM Test_Measurements AS M2
   WHERE M2.measurement_date <= A.test_end_date);
    

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 7

try ,

select TA.*, M.MeasurementDate 
FROM Test_Assign TA 
OUTER APPLY (SELECT MAX(TM.MeasurementDate )MeasurementDate from Test_Measure TM where TM.Name = TA.Name
AND TM.MeasurementDate <=TA.DateEnd 
) M

---

Thanks
Sarat

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 8

@Sarat - thanks, but Naomi's code  solves my problem and your suggestion is not quite right

this thread can be closed

---

Marion in Hudson Valley