tags:

views:

525

answers:

3

Thanks to a previous question, I found out how to pull the most recent data based on a linked table. BUT, now I have a related question.

The solution that I found used row_number() and PARTITION to pull the most recent set of data. But what if there's a possibility for zero or more rows in a linked table in the view? For example, the table FollowUpDate might have 0 rows, or 1, or more. I just want the most recent FollowUpDate:

SELECT 
    EFD.FormId
    ,EFD.StatusName
    ,MAX(EFD.ActionDate)
    ,EFT.Name AS FormType
    ,ECOA.Account AS ChargeOffAccount
    ,ERC.Name AS ReasonCode
    ,EAC.Description AS ApprovalCode
    ,MAX(EFU.FollowUpDate) AS FollowUpDate
FROM (
  SELECT EF.FormId, EFD.ActionDate, EFS.Name AS StatusName, EF.FormTypeId, EF.ChargeOffId, EF.ReasonCodeId, EF.ApprovalCodeId, 
         row_number() OVER ( PARTITION BY EF.FormId ORDER BY EFD.ActionDate DESC ) DateSortKey
    FROM Extension.FormDate EFD INNER JOIN Extension.Form EF ON EFD.FormId = EF.FormId INNER JOIN Extension.FormStatus EFS ON EFD.StatusId = EFS.StatusId
  ) EFD
    INNER JOIN Extension.FormType EFT ON EFD.FormTypeId = EFT.FormTypeId
    LEFT OUTER JOIN Extension.ChargeOffAccount ECOA ON EFD.ChargeOffId = ECOA.ChargeOffId
    LEFT OUTER JOIN Extension.ReasonCode ERC ON EFD.ReasonCodeId = ERC.ReasonCodeId
    LEFT OUTER JOIN Extension.ApprovalCode EAC ON EFD.ApprovalCodeId = EAC.ApprovalCodeId
    LEFT OUTER JOIN (Select EFU.FormId, EFU.FollowUpDate, row_number() OVER (PARTITION BY EFU.FormId ORDER BY EFU.FollowUpDate DESC) FUDateSortKey FROM Extension.FormFollowUp EFU INNER JOIN Extension.Form EF ON EFU.FormId = EF.FormId) EFU ON EFD.FormId = EFU.FormId
WHERE EFD.DateSortKey = 1
GROUP BY
    EFD.FormId, EFD.ActionDate, EFD.StatusName, EFT.Name, ECOA.Account, ERC.Name, EAC.Description, EFU.FollowUpDate
ORDER BY 
    EFD.FormId

If I do a similar pull using row_number() and PARTITION, I get the data only if there is at least one row in FollowUpDate. Kinda defeats the purpose of a LEFT OUTER JOIN. Can anyone help me get this working?

A: 

It's pretty hard to guess what's going on without table definitions and sample data.

Also, this is confusing: "the table FollowUpDate might have 0 rows" and you "want the most recent FollowUpDate." (especially when there is no table named FollowUpDate) There is no "most recent FollowUpDate" if there are zero FollowUpDates.

Maybe you want

WHERE <follow up date row number> in (1,NULL)
Steve Kass
A: 

I rewrote your query - you had unnecessary subselects, and used row_number() for the FUDateSortKey but didn't use the column:

SELECT t.formid,
   t.statusname,
   MAX(t.actiondate) 'actiondate',
   t.formtype,
   t.chargeoffaccount,
   t.reasoncode,
   t.approvalcode,
   MAX(t.followupdate) 'followupdate'
FROM (
   SELECT t.formid, 
          fs.name 'StatusName',
          t.actiondate,
          ft.name 'formtype',
          coa.account 'ChargeOffAccount',
          rc.name 'ReasonCode',
          ac.description 'ApprovalCode',
          ffu.followupdate,
          row_number() OVER (PARTITION BY ef.formid ORDER BY t.actiondate DESC) 'DateSortKey'
     FROM EXTENSION.FORMDATE t 
     JOIN EXTENSION.FORM ef ON ef.formid = t.formid
     JOIN EXTENSION.FORMSTATUS fs ON fs.statusid = t.statusid
     JOIN EXTENSION.FORMTYPE ft ON ft.formtypeid = ef.formtypeid
LEFT JOIN EXTENSION.CHARGEOFFACCOUNT coa ON coa.chargeoffid = ef.chargeoffid
LEFT JOIN EXTENSION.REASONCODE rc ON rc.reasoncodeid = ef.reasoncodeid
LEFT JOIN EXTENSION.APPROVALCODE ac ON ac.approvalcodeid = ef.approvalcodeid
LEFT JOIN EXTENSION.FORMFOLLOWUP ffu ON ffu.formid = t.formid) t
    WHERE t.datesortkey = 1
 GROUP BY t.formid, t.statusname, t.formtype, t.chargeoffaccount, t.reasoncode, t.approvalcode
 ORDER BY t.formid

The change I made to allow for FollowUpDate was to use a LEFT JOIN onto the FORMFOLLOWUP table - you were doing an INNER JOIN, so you'd only get rows with FORMFOLLOWUP records associated.

OMG Ponies
This is great! I'm going to flesh this one out to fit the whole view; it looks faster than what I'm working with now by a long shot.
Valkyrie
A: 

I figured it out. And as usual, I need a nap. I just needed to change my subselect to something I would swear I'd tried with no success:

SELECT field1, field2     
FROM Table1 t1    
LEFT JOIN (    
      SELECT field3, max(dateColumn)    
      FROM Table2    
      GROUP BY    
            field3    
) t2    
ON (t1.field1 = t2.field3)
Valkyrie