CODEWITHSUNDEEP
sqlsql-server-2008-r2rank
RagePwn
RagePwn

Reputation: 421

SQL Rank based on date

I am trying to link a customer to a "peferred" merchant based on number of visits within the last 18 months, with the tiebreaker being the most recent visit date. I'm having a bit of trouble with the tiebreaker. If there are two records both ranked 1 based on # of visits for a certain MemberID, I want to set the IsFirst bit column to 1 on the record with the MAX(EncounterDate) for that MemberID. How should I go about doing this?

Upvotes: 0

Views: 29259

Answers (2)

Art
Art

Reputation: 5782

This may help you... This is Oracle query based on existing emp table. I think it is a good idea to create structures when you posting a problem. Replace first select with update etc...: UPDATE your table SET your date = max_date (max_hire_date in my example) WHERE your_field IN (select max date as in my example) AND rnk = 1 and rno = 1 

SELECT * FROM 
 (  
 SELECT deptno
      , ename
      , sal
      , RANK() OVER (PARTITION BY deptno ORDER BY sal desc) rnk 
      , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal desc) rno 
      , MAX(hiredate) OVER (PARTITION BY deptno ORDER BY deptno) max_hire_date
   FROM emp_test
  WHERE deptno = 20
 ORDER BY deptno
 )
 WHERE rnk = 1
   --AND rno = 1 -- or 2 or any other number...
/

SQL>

DEPTNO  ENAME   SAL    RNK  RNO HIREDATE    MAX_HIRE_DATE
-----------------------------------------------------------
 20     SCOTT   3000    1   1   1/28/2013   1/28/2013
 20     FORD    3000    1   2   12/3/1981   1/28/2013

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269463

The following SQL gets the information you want, assuming the structure of certain tables:

select c.*, NumVisits, MaxVisitDate, MaxFirstVisitDate
       (select count(*)
        from visits v
        where v.customerid = c.customerid and 
              v.visi
from customers c join
     (select customerid,
             sum(case when visitdate between getdate() - 365*1.5 and getdate()
                      then 1 else 0
                  end) as NumVisits,
             max(visitdate) as MaxVisitDate,
             max(case when IsFirst = 1 then visitdate end) as MaxFirstVisitDate
      from visits
      group by customerid
     ) v
     on c.customerid = v.customerid

From this information, you can put together the logic for what you want to do. When MaxVisitDate = MaxFirstVisitDate, then the bit is set on the most recent date.

The answer to your update question is something like this:

update visits
    set IsFirst = 1
    where exists (select max(encounterDate)
                  from visits v2
                  where v2.customer_id = visits.customer_id
                  having max(encounterDate) = visits.encounterDate)
                 )

Upvotes: 0

Related Questions