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

Solution 1:

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 (PARTITIONBY deptno ORDERBY sal desc) rnk 
      , ROW_NUMBER() OVER (PARTITIONBY deptno ORDERBY sal desc) rno 
      , MAX(hiredate) OVER (PARTITIONBY deptno ORDERBY deptno) max_hire_date
   FROM emp_test
  WHERE deptno =20ORDERBY 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   3000111/28/20131/28/201320     FORD    30001212/3/19811/28/2013

Solution 2:

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

select c.*, NumVisits, MaxVisitDate, MaxFirstVisitDate
       (selectcount(*)
        from visits v
        where v.customerid = c.customerid and 
              v.visi
from customers c join
     (select customerid,
             sum(casewhen visitdate between getdate() - 365*1.5 andgetdate()
                      then 1 else 0
                  end) as NumVisits,
             max(visitdate) as MaxVisitDate,
             max(casewhen IsFirst = 1 then visitdate end) as MaxFirstVisitDate
      from visits
      groupby 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 =1whereexists (selectmax(encounterDate)
                  from visits v2
                  where v2.customer_id = visits.customer_id
                  havingmax(encounterDate) = visits.encounterDate)
                 )