Help w/self join?

1

Hi,

Data in a table looks like this:

PERNO FIELDNAME OLDVALUE NEWVALUE CHANGEDDATE
10 SRO L/ADM L/ADM/SRO 10/2/2019

I want to return all PERNOs where:

  1. changeddate < '8/1/2018' and newvalue = 'L/ADM/SRO' and fieldname = 'UNIT'

but yet that same PERNO does not have another record where...

  1. changeddate < '8/2/2018' and oldvalue = 'L/ADM/SRO' and fieldname = 'UNIT'

Your help is greatly appreciated. Thanks so much.

2

Can you supply some relevant data so we can help? You are missing the Unit fieldname value from your DDL. I mocked up some data but I'm only guessing at what it looks like. You can modify this


declare @t table (PERNO int, FIELDNAME varchar(20), OLDVALUE varchar(20), NEWVALUE varchar(20), CHANGEDDATE date)
insert into @t values
(10,'Unit','SRO L/ADM','L/ADM/SRO','2/10/2018'),
(10,'Unit','SRO L/ADM','L/ADM/SRO','2/9/2018'),
(10,'Unit','SRO L/ADM','L/ADM/SRO','2/8/2018'),
(10,'Unit','SRO L/ADM','L/ADM/SRO','2/7/2018'),
(20,'Unit','SRO L/ADM','L/ADM/SRO','2/8/2018'),
(20,'Unit','SRO L/ADM','L/ADM/SRO','2/7/2018'),
(30,'Unit','SRO L/ADM','L/ADM/SRO','2/1/2018'),
(30,'Unit','SRO L/ADM','L/ADM/SRO','2/7/2018')
3

Thanks Mike. The data you've created is fine. "SRO" was meant to be the FIELDNAME in the sample record I provided. But "UNIT" is the value I need to use, like yours shows. I guess I should have posted another sample record(s).

4

I don't have anything that matches this, and I wasn't sure on dates. Like I said it was a guess or provide the results you are expecting?

5

The result I'm expecting is just a list of PERNOs that meet the criteria I cited in my original posting. I don't know what you mean when you say you "don't have anything that matches this". The data I want returned does exist in my database. I just need the structure for a query which will return all PERNOs in a table for certain conditions but it doesn't appear again in that same table for a different set of conditions.

Thanks for your willingness to help.

6

I give the general format for the solution below. Hopefully you can adapt it to your data. If not, please post sample data as CREATE TABLE and INSERT statement(s) and I'll adjust the code to match.


SELECT PERNO
FROM tablename
GROUP BY PERNO
HAVING 
    MAX(CASE WHEN changeddate < '20180801' and newvalue = 'L/ADM/SRO' and 
        fieldname = 'UNIT' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN changeddate < '20180802' and oldvalue = 'L/ADM/SRO' 
        and fieldname = 'UNIT' THEN 1 ELSE 0 END)  = 0
7

Thanks Scott. You're always a tremendous help.