CODEWITHSUNDEEP
mysqlsqldatabricks
Kyle
Kyle

Reputation: 155

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).

merchant date tag
Target 2023-02-09 x
Target 2023-02-10 y

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.

Upvotes: 0

Views: 37

Answers (1)

ClearlyClueless
ClearlyClueless

Reputation: 762

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')

Upvotes: 1

Related Questions