CODEWITHSUNDEEP
sqlms-access
Sha sha
Sha sha

Reputation: 9

SQL matching - How to pull all values for each unique value in one column and compare

I have a SQL table that looks like the one seen below. It has about 4000 observations. Below is an excerpt. I need help in pulling all distinct values ( project names) , grouping them according to project name, and pulling the 2 most recent dates for each project name, comparing the status value, and returning a " y " in a new column if certain parameters are met. i.e. ( if for project name 18362, the status values of the 2 most recent report dates are both equal to 1, return a yes) 

Report Date  Project Name    Status value
3/21/2016   18362                 1
3/14/2016   18355                 1
6/8/2015    18342                 2
4/20/2015   11234                 1
2/15/2015   17893                 2

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

If I understand correctly, you can do this with a rather nasty correlated subquery:

update t
    set status = (select iif(count(*) = 2, 'yes', 'no')
                  from t as t2
                  where t2.projectname = t.projectname and
                        t2.status = 1 and
                        t2.reportdate in (select top 2 t3.reportdate
                                          from t as t3
                                          where t3.projectname = t2.projectname 
                                          order by t3.reportdate desc
                                         )
                 );

Upvotes: 1

Related Questions