CODEWITHSUNDEEP
sql-serverdatejoinmin
tandy
tandy

Reputation: 13

Select data from first date following a specific date (two tables joined)

I've searched but can't seem to get this exactly where I need it. This is in regards to Microsoft SQL Server.

I'm looking to join two tables, but only pull in data from the second table for the first date following the date in the first table. See code below.

I've got a table that shows emails sent to customers that looks like this:

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%'

From there, I want to see how many days it took the email receiver to make their first payment following the received email. That's where I've got this:

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'

That gets me all payments the customer has made after they received an email. What I need is just their first payment amount and the days it took following the email.

I tried the MIN() function like this:

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

Any help would be GREATLY appreciated.

Upvotes: 1

Views: 67

Answers (1)

SqlZim
SqlZim

Reputation: 38023

outer apply() is a good solution for this type of problem. 

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

I think this is how it would look without the temp table:

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

Upvotes: 1

Related Questions