选择特定日期之后的第一个日期的数据(两个表连接在一起)

Question

我已经搜过了，但似乎找不到我需要它的地方。这与有关。

我希望连接两个表，但只在第一个表中的日期之后的第一个日期从第二个表中提取数据。请参阅下面的代码。

我有一张桌子，上面写着发送给顾客的电子邮件，看起来像这样：

SELECT
  e.Name
  ,se.SubscriberID
  ,se.SendID
  ,c1.Id
  ,ds.Service_Num
  ,sub.EmailAddress
  ,se.EventDate as 'SentDate'
  into #temp_billing_emails
FROM
  bi_views.dbo.sfdcMC_SentEvent se
  left join bi_views.dbo.sfdcmc_job j on j.jobid=se.sendid
  left join bi_views.dbo.sfdcMC_Email e on e.id=j.emailid
  left join bi_views.dbo.sfdcMC_Subscriber sub on sub.id=se.SubscriberID
  left join sfdcprod.dbo.contact c1 on sub.subscriberkey=c1.id
  left join bi_views.dbo.DIM_SERVICE_RF ds on c1.id=ds.ContractSignerContactID_bk
WHERE
  e.name like '%Past Due%'

从那里，我想看看多少天的电子邮件接收后，他们第一次付款收到的电子邮件。这就是我得到这个的地方：

SELECT
  e.*
  ,z.zuora__createddate__c
  ,z.zuora__status__c
  ,z.zuora__amount__c
  ,datediff(dd,e.sentdate,z.Zuora__CreatedDate__c) DaysToPay    
FROM
  #temp_billing_emails e
  left join sfdcprod.dbo.Service__C sc on e.Service_Num = right(sc.name,len(sc.name)-2)
  left join sfdcprod.dbo.[zuora__customeraccount__c] a on sc.billing_account__c=a.id
  left join sfdcprod.dbo.[zuora__payment__c] z on a.id=z.zuora__billingaccount__c
WHERE
  datediff(dd,e.sentdate,z.Zuora__CreatedDate__c)>=0
  and z.zuora__status__c not like 'Error'

这让我得到了客户在收到电子邮件后支付的所有款项。我需要的只是他们的第一笔付款，以及邮件发送后所花的时间。

我尝试了MIN()函数如下：

SELECT TOP 100
  e.EmailAddress
  ,e.SentDate
  ,e.Service_Num
  ,z.zuora__createddate__c
  ,z.zuora__status__c
  ,z.zuora__amount__c
  ,datediff(dd,e.sentdate,min(z.Zuora__CreatedDate__c)) DaysToPay   
FROM
  #temp_billing_emails e
  left join sfdcprod.dbo.Service__C sc on e.Service_Num = right(sc.name,len(sc.name)-2)
  left join sfdcprod.dbo.[zuora__customeraccount__c] a on sc.billing_account__c=a.id
  left join sfdcprod.dbo.[zuora__payment__c] z on a.id=z.zuora__billingaccount__c
WHERE
  datediff(dd,e.sentdate,z.Zuora__CreatedDate__c)>=0
  and z.zuora__status__c not like 'Error'
GROUP BY
  e.EmailAddress
  ,e.SentDate
  ,e.Service_Num
  ,z.zuora__createddate__c
  ,z.zuora__status__c
  ,z.zuora__amount__c

任何帮助都将不胜感激。

Answer 1

outer apply()是解决这类问题的好方法。

select e.*
 , x.*
, DaysToPay = datediff(day,e.sentdate,x.Zuora__CreatedDate__c)
from #temp_billing_emails e
  outer apply (
    select top 1 
          z.zuora__createddate__c
        , z.zuora__status__c
        , z.zuora__amount__c
      from sfdcprod.dbo.Service__C sc 
        inner join sfdcprod.dbo.[zuora__customeraccount__c] a 
          on sc.billing_account__c=a.id
         and ds.Service_Num = right(sc.name,len(sc.name)-2)
        inner join sfdcprod.dbo.[zuora__payment__c] z 
          on a.id=z.zuora__billingaccount__c
         and z.zuora__status__c not like 'Error'
         and z.Zuora__CreatedDate__c >= se.EventDate
        order by z.Zuora__CreatedDate__c asc
      ) as x

我认为没有临时表的情况是这样的：

select
    e.Name
  , se.SubscriberID
  , se.SendID
  , c1.Id
  , ds.Service_Num
  , sub.EmailAddress
  , se.EventDate as 'SentDate'
  , x.zuora__createddate__c
  , x.zuora__status__c
  , x.zuora__amount__c
  , DaysToPay = datediff(day,se.EventDate,x.Zuora__CreatedDate__c)
from bi_views.dbo.sfdcMC_SentEvent se
  inner join bi_views.dbo.sfdcmc_job j on j.jobid=se.sendid
  inner join bi_views.dbo.sfdcMC_Email e on e.id=j.emailid and e.name like '%Past Due%'
  left join bi_views.dbo.sfdcMC_Subscriber sub on sub.id=se.SubscriberID
  left join sfdcprod.dbo.contact c1 on sub.subscriberkey=c1.id
  left join bi_views.dbo.DIM_SERVICE_RF ds on c1.id=ds.ContractSignerContactID_bk
     outer apply (
        select top 1 
              z.zuora__createddate__c
            , z.zuora__status__c
            , z.zuora__amount__c
            from sfdcprod.dbo.Service__C sc 
              inner join sfdcprod.dbo.[zuora__customeraccount__c] a 
                on sc.billing_account__c=a.id
               and ds.Service_Num = right(sc.name,len(sc.name)-2)
              inner join sfdcprod.dbo.[zuora__payment__c] z 
                on a.id=z.zuora__billingaccount__c
               and z.zuora__status__c not like 'Error'
               and z.Zuora__CreatedDate__c >= se.EventDate
            order by z.Zuora__CreatedDate__c asc
          ) as x