Date range problem in absences of range

Transact-SQL
1

Hi everyone,

I have a problem that for me - and everybody I asked - is unsolvable. I have to point out, that I have read only rights on those tables and can’t create tables, views or anything….So here comes the problem:

I have 2 tables. One, where we have the history of everything that’s ever been sent to our customers. I filtered that by a SENDID and it looks like that:

CustomerID; DateLetter; Group; Template; SENDID
23 ; 19.07.2006 ;DM ; MK ; 6573
58 ; 19.07.2006 ;DM ; MK ; 6573

We regularly categorize and re-categorize our customers in “Stages” depending on amount and frequency of payment (RFM). That table looks like this:

CustomerID Stage DateStage
23 ; A ; 31.12.1999
23 ; B ; 11.03.2011
23 ; G ; 15.08.2015
58 ; B ; 01.01.2002
58 ; F ; 03.04.3005
58 ; C ; 06.08.2009
58 ; B ;15.08.2015

Now we need to do an analysis on how those stages develop over time. I need to know what stage the customer had on the date of the letter “DateLetter”. Result should be that on the “DateLetter” date (19.7.2006) CustomerID 23 was Stage A and CustomerID 58 was Stage F. Something like that:

CustomerID DateLetter Group Template Code Stage
23 ;19.07.2006 ;DM ;MK; 6573; A
58 ;19.07.2006 ;DM ;MK ;6573; F

If only I had a DateStage “Enddate” then I could filter on that, but I don’t. Any ideas? I can explain more if you like/need :wink:

Thanks already for taking time to think about problems that aren’t yours and for maybe solving things that I actually get paid for (undeserved apparently).

SQLBUNNY

2

Try this:

create table #table1(CustomerID int,DateLetter date,[Group] varchar(10),Template varchar(10),SENDID int);
insert into #table1
 values (23,'20060719','DM','MK',6573)
       ,(58,'20060719','DM','MK',6573)
;

create table #table2(CustomerID int,Stage varchar(10),DateStage date);
insert into #table2
 values (23,'A','19991231')
       ,(23,'B','20110311')
       ,(23,'G','20150815')
       ,(58,'B','20020101')
       ,(58,'F','20050403')
       ,(58,'C','20090806')
       ,(58,'B','20150815')
;

select CustomerID
      ,DateLetter
      ,[Group]
      ,Template
      ,Stage
  from (select a.CustomerID
              ,a.DateLetter
              ,a.[Group]
              ,a.Template
              ,b.Stage
              ,row_number() over(partition by a.CustomerID order by b.DateStage desc) as rn
          from #table1 as a
               inner join #table2 as b
                       on b.CustomerID=a.CustomerID
                      and b.DateStage<a.DateLetter
       ) as a
 where rn=1
;

drop table #table2;
drop table #table1;
3 
SELECT t1.CustomerID,
    t1.DateLetter,
    t1.[Group],
    t1.Template,
    oa.Stage
FROM #table1 t1
OUTER APPLY (
    SELECT TOP (1) t2.Stage
    FROM #table2 t2
    WHERE
        t2.CustomerID = t1.CustomerID AND
        t2.DateStage <= t1.DateLetter
    ORDER BY t2.DateStage DESC
) AS oa
4

Hi bitsmed and ScottPletcher,

thanks for the really fast replies. I tried both, and after some adjustments (so it would fit with my tables etc.), both worked perfectly. Thank you so much, guys. You never disappoint. I gave you all the credit, but I can't give you the money :wink:

Till next time....

SQLBUNNY