Finding unique records in a table which did not repeat after a particular date in another table

Admin 1
Viewed 50

I am a beginner in SQL and trying to solve an issue.
I have two tables on my database, company and invoice as below

Company table

id          name          active   
1           company A     True
2           company B     True
3           company C     True
4           company D     True
5           company E     True

Invoice table

id            date            companyid
1             18/01/2018      4
2             06/05/2014      1
3             04/03/2017      4
4             25/05/2016      3
5             17/04/2018      2
6             10/11/2017      3

I want to get a get a list of unique values of companies and change the "active" column value to False if a company doesn't have any invoice after 01/01/2017. By saying this, Company A and E active column should be changed to False. The desired outcome would be this:

id          name          active   
1           company A     False
2           company B     True
3           company C     True
4           company D     True
5           company E     False

I have tried this but this is just half and I could't sort out the rest of the solution:

select distinct A.* from company A
    inner join invoice B
        on B.companyid = A.id
        where B.date < '20170101'
sql-server sql
4 Answers

You could use this:

SELECT c.id, c.name,
    MAX(CASE WHEN i.date > '01/01/2017' THEN 'True' ELSE 'False' END) AS active
FROM company c
LEFT JOIN invoice i
ON i.companyid = c.id
GROUP BY c.id, c.name;
user_38444580

Use case when with subquery and aggregation

select id, name, case when mdate>'20170101' then 'Active' else 'False' end as Active
from
(
select A.id,A.name,max(date) mdate from company A
    left join invoice B
        on B.companyid = A.id
group by A.id,A.name)a
user_69148640

use join and case when

    select t1.id,t2.name,
   case when t2.date<'01/01/2017' and t1.active then 
   'false' else t1.active end 
    as active     
     from Company t1 join invoice t2 on t1.id=t2.id
user_68085830

Like you said, you have indeed reached half your answer. You just need an update statement to go with your query.

UPDATE A SET active = 'false'
from company A
inner join invoice B
on B.companyid = A.id
where B.date < '20170101'
user_36355290
Related