Filter record based on a column using SQL Server

Question

I have a query like this that will return two records, one with NULL in VendorCode and VendoroffsetAccount and another with valid values. I want a way to get the non null record if there are two records.Otherwise the record with NULL without using temp tables.Any help will be appreciated.

    SELECT DISTINCT  ift.BatchGuid AS ProcessId ,
                'H' AS RecordType ,
                ISNULL(ift.SuperBranch, cd.SuperBranch) AS SuperBranch ,
                ISNULL(ift.ProcessName, cd.ProcessName) AS ProcessName ,
                VendorOffsetAccount = CASE WHEN ift.OffsetType = 'V'
                THEN ISNULL(ift.VendorOffsetAccount,
                cd.VendorOffsetAccount)
                END ,
                VendorCode = CASE WHEN ift.OffsetType = 'V'
                THEN ISNULL(ift.VendorCode, cd.VendorCode)
                END                    
                FROM    IFP.InboundFileTransaction ift
                CROSS APPLY IFP.ctlConfigDefault cd
                WHERE   CAST(BatchGuid AS CHAR(36)) = '67C6A9C3-F8B7-45ED-8DDD-0AB6701BED34'

Here is a sample output of this query.

     Id   Type      SB   Name    VendorOffsetAccount        VendorCode
      1     H       XYZ  UPLOAD  NULL                       NULL
      1     H       XYZ  UPLOAD  XYZ000123                  DFEE

Thanks in Advance

Sample data and expected output would be helpful here.

Thom A
– Thom A ♦

2018-02-22 19:20:30 +00:00
Commented Feb 22, 2018 at 19:20 — Thom A
– Thom A ♦, Commented Feb 22, 2018 at 19:20

SqlZim · Accepted Answer · 2018-02-22 19:24:30Z

2

If you want one record, and prefer the non null value then you can use top and order by:

select top 1
    ift.BatchGuid as ProcessId
  , 'H' as RecordType
  , isnull(ift.SuperBranch, cd.SuperBranch) as SuperBranch
  , isnull(ift.ProcessName, cd.ProcessName) as ProcessName
  , VendorOffsetAccount = case when ift.OffsetType = 'V' then isnull(ift.VendorOffsetAccount, cd.VendorOffsetAccount) end
  , VendorCode = case when ift.OffsetType = 'V' then isnull(ift.VendorCode, cd.VendorCode) end
from ifp.InboundFileTransaction ift
  cross apply ifp.ctlConfigDefault cd
where cast(BatchGuid as char(36)) = '67C6A9C3-F8B7-45ed-8ddd-0ab6701bed34'
order by case when ift.OffsetType = 'V' then isnull(ift.VendorCode, cd.VendorCode) end desc

answered Feb 22, 2018 at 19:24

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filter record based on a column using SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related