Assigning indicator to an event in time

Transact-SQL
1

Hi everyone, I'm new here and sincerely hope you can help me.
I am trying to assign a correct indicator to each event. The indicator changes sporadically but is not recorded at every event. I guess what I am after is something like a point in time report, where each event is assigned to a current (a the time) indicator.

Please find my sample data below:

CREATE TABLE #indicator
(key_no VARCHAR(30),
indicator VARCHAR(1),date DATE);

INSERT INTO #indicator VALUES ('G82002','Y','2014/12/10');
INSERT INTO #indicator VALUES ('G82002','N','2015/01/10');
INSERT INTO #indicator VALUES ('G82002','Y','2015/01/30');

CREATE TABLE #event
(key_no VARCHAR(30),date DATE);

INSERT INTO #event VALUES ('G82002','2014/12/10');
INSERT INTO #event VALUES ('G82002','2014/12/15');
INSERT INTO #event VALUES ('G82002','2014/12/30');
INSERT INTO #event VALUES ('G82002','2015/01/10');
INSERT INTO #event VALUES ('G82002','2015/01/15');
INSERT INTO #event VALUES ('G82002','2015/01/20');
INSERT INTO #event VALUES ('G82002','2015/01/25');
INSERT INTO #event VALUES ('G82002','2015/01/26');
INSERT INTO #event VALUES ('G82002','2015/01/30');
INSERT INTO #event VALUES ('G82002','2015/02/10');
INSERT INTO #event VALUES ('G82002','2015/02/15');

Select
e.key_no
,e.date
,i.indicator
from #event e
left join #indicator i
on e.key_no = i.key_no
and e.date = i.date

RESULT:

G82002 10/12/2014 Y
G82002 15/12/2014
G82002 30/12/2014
G82002 10/01/2015 N
G82002 15/01/2015
G82002 20/01/2015
G82002 25/01/2015
G82002 26/01/2015
G82002 30/01/2015 Y
G82002 10/02/2015
G82002 15/02/2015

Sought result:

G82002 10/12/2014 Y
G82002 15/12/2014 Y
G82002 30/12/2014 Y
G82002 10/01/2015 N
G82002 15/01/2015 N
G82002 20/01/2015 N
G82002 25/01/2015 N
G82002 26/01/2015 N
G82002 30/01/2015 Y
G82002 10/02/2015 Y
G82002 15/02/2015 Y

Can you help please?

Thank you

2 
SELECT
	e.*,
	i.[Indicator]
FROM
	#event e
	OUTER APPLY
	(
		SELECT TOP (1) i.[Indicator]
		FROM #indicator i
		WHERE i.key_no = e.key_no
			AND i.date <= e.date
		ORDER BY
			i.date DESC 
	)i
ORDER BY
	e.date
3

Hi James,

Thank you for your prompt reply, it works perfectly, cant thank you enough.
I shall come her more often :slight_smile:

Best wishes