CODEWITHSUNDEEP
sql-server
user7577070
user7577070

Reputation: 97

how to query SQL for unique values for same record over the years

I am trying to get the number of unique values for one record over time. More specific, I have a table of facilities that has a name field, but most of the facilities have changed names over the years. I want to find the unique names for each facility. I am having trouble getting all of these values returned in one query. Below is the query I created, but the problem is that it gives me the name of the facility for each year. I only want when the name changed.

Select Distinct HAE.Name
            ,HAE.UniqueId
            ,S.Year
FROM HAEntity HAE
        INNER JOIN Survey S ON HAE.SurveyId=S.SurveyId
GROUP BY UniqueId, Name, Year
ORDER BY UniqueId, Name, Year

Thank you in advance for any advice.

Upvotes: 2

Views: 45

Answers (1)

SqlZim
SqlZim

Reputation: 38023

You could remove Year from your group by and get the min() year for each Name and UniqueId.

Select Distinct HAE.Name
            ,HAE.UniqueId
            ,min(S.Year) as Year
FROM HAEntity HAE
        INNER JOIN Survey S ON HAE.SurveyId=S.SurveyId
/* where facilities have had at least 1 name change */
where exists (
  select 1
    from HAEntity i
    where i.UniqueId = hae.UniqueId
    and i.Name<>hae.Name
    )
GROUP BY UniqueId, Name
ORDER BY UniqueId, Name, Year

Upvotes: 1

Related Questions