sql query to filter for column value that occurs over the course of two dates with one specific condition met one day and another the next

Trying to find a way to query merchants that appear in a table yesterday with an x tag and then appear again in the same table with a y tag the next day (today).

I just want a query that will find examples like the one above and return all merchants that meet the criteria. Originally the x and y tags are from different tables and I brought them together with a union, but I’m a little stuck trying to find a good solution to pull out what I need from the temp table.

>Solution :

This should get you pretty close. A self join that limits the records in the where clause by date and then by tag.

    SELECT Tdays.Merchant, 
        Tdays.date, 
        Tdays.tag, 
        Ydays.date, 
        ydays.tag
    FROM tblMerchants AS Ydays INNER JOIN
        tblMerchants AS Tdays ON Tdays.Merchant = Ydays.Merchant
    WHERE (Tdays.date = getDate() AND Tdays.tag = 'x') AND 
        (Ydays.date = DATEADD(day, -1, getDate()) AND Ydays.tag = 'Y')