SQL rank is date based
I am trying to connect a customer to a "peferred" merchant based on the number of visits in the last 18 months, when the tie-break is the most recent visit date. I'm having trouble with a tiebreaker. If there are two records, each ranking 1 based on # visits for a particular MemberID, I want to set the IsFirst bit column to 1 in the MAX (EncounterDate) record for that MemberID. How should I do it?
source to share
This might help you ... This is an Oracle query based on an existing emp table. I think it is a good idea to create structures when posting a problem. Replace first select with update etc ...: UPDATE your table SET 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
source to share
The following SQL gets the information it needs by 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 figure out the logic of what you want to do. When MaxVisitDate = MaxFirstVisitDate
, the bit is set at the most recent date.
The answer to your update question looks 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)
)
source to share