CODEWITHSUNDEEP
sql-servert-sqljoinfilter
user8139445
user8139445

Reputation:

Sql Filter table by two dates in order

I have been trying to filter one table by two dates with an order of importance (date2 > date1) as follows:

SELECT 
    t1.customer, t1.weights, t1.max(t1.date1) as date1, t1.date2
FROM 
    (SELECT *
     FROM table
     WHERE CAST(date2 AS smalldatetime) = '10/29/2017') t2
INNER JOIN 
    table t1 ON t1.customer = t2.customer
             AND t1.date2 = t2.date2
GROUP BY 
    t1.customer, t1.date2
ORDER BY 
    t1.customer;

It filters the table correctly by date2 first, the max(t1.date1) doesn't what I want it to do though. I get duplicate customers, that share the same (and correct) date2, but show different date1's. These duplicate records have the following in common: The weight row is different. What would I need to do to output just the the customer records connected to the most current date1 without taking other columns into consideration?

I am still a noob, help would be greatly appreciated!

Solution for t-sql (all based on the accepted answer):

SELECT * FROM (

    SELECT row_number() over(partition by t1.customer order by t1.date1 desc) as rownum, t1.customer, t1.weights, t1.date1 , t1.date2
    FROM 
         (SELECT *
          FROM table
          WHERE CAST(date2 AS smalldatetime) = '10/29/2017') t2
      INNER JOIN 
      table t1 ON t1.customer = t2.customer
      AND t1.date2 = t2.date2
     )t3
where rownum = 1;

Upvotes: 0

Views: 265

Answers (1)

Stassi
Stassi

Reputation: 101

If I understood correctly, then instead of a group by logic, I would just use a qualify row statement :)

Try the code below and tell me if it's what you needed - what I'm telling it to do is to bring back only one row per customer ID....but where we select the row based on the dates (by sorting them in ascending order) - however, I'm unclear of what you mean by importance of the 2 dates so I may be completely off base here...can you please give an example of input and desired output?

SELECT t1.customer, t1.weights, t1.date1, t1.date2 FROM ( Select * FROM table WHERE Cast(date2 as smalldatetime)='10/29/2017' ) t2 Inner Join table t1 ON t1.customer = t2.customer AND t1.date2 = t2.date2 Qualify row_number() over(partition by t1.customer order by date2 , date1)=1 Order By t1.customer;

Upvotes: 1

Related Questions