Hello everyone,
I have two tables; one shows statuses with dates and the other shows tags with dates. I am trying to find the tag in one table at the time of the date of a particular status.
I added a condition to ensure that I get the tag before or on the same day as the status but if there are multiple tags before I can't get the latest. I tried using max but got an error.
Does anyone have any advice?
Query is rather long which is why I haven't yet posted, was hoping it was straight forward 
Thanks in advance!
I understand that query might be long, but you can extract the relevant portion and extrapolate an example. Also, plese post table definitions, sample data and expected output.
See here for examples: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
Thanks, the script is below:
--What is the offer when they had this status and when did this first receive offer for that intake?
With Offer as
(select sth.client_id, sth.crms_number, sth.client_id + ' ' + CONVERT(VARCHAR, sth.crms_number) as [Unique ID],
sth.tag_value, CONVERT(date, sth.date_created) as [Tag Created],
row_number() over (PARTITION by sth.client_id, sth.crms_number
order by sth.client_id, sth.crms_number, sth.date_created) Seq
from dbo.ZZ_crms_student_tag_history sth
left join dbo.ZZ_crms_student s on s.client_id = sth.client_id and s.crms_number = sth.crms_number
where sth.client_id in ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I') and dead = 0 and
tag_type = 'made offer source' and (tag_value like '%Sep 15%'
or tag_value like '%Sep 16%'))
select * from
(select ssh.client_id, ssh.crms_number, ssh.client_id + ' ' + convert(varchar, ssh.crms_number) as [Unique ID],
ssh.student_status, convert(date, ssh.date_modified) as [Status Date],
ROW_NUMBER() over (partition by ssh.client_id, ssh.crms_number
order by ssh.client_id, ssh.crms_number, ssh.date_modified desc) Seq_rev,
O.tag_value, O.[Tag Created]
from dbo.ZZ_crms_student_status_history ssh
left join dbo.ZZ_crms_student s on s.client_id = ssh.client_id and s.crms_number = ssh.crms_number
left join Offer O on O.client_id = ssh.client_id and O.crms_number = ssh.crms_number
where ssh.student_status like '%accepted%' and ssh.student_status like '%uo%' and dead = 0 and
ssh.client_id in ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I')
and O.[Tag Created] <= ssh.date_modified) T
where T.Seq_rev = 1
So, I would like to see the status with "Accepted" and "UO" in the string and the date that the record got this status, then I would like to see what the offer tag was at the time of this and what date it changed to this tag.
At the moment, the tag being shown is the earliest one.
Is that a bit clearer?