• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

SQL 2008

Hello,
I'm using this Stored Procedure for one of my clients.

Select report from (
Select  
      'DETAIL,' + case when ao.Instructions  like 'Return%' then 'Y' else 'N' end
        + ao.DeliveryCompanyName + ',' + ao.POD +
      + Case ao.StatusID
                   WHEN 4      THEN 'X6'  
                   WHEN 5      THEN 'X4'  
                   else ''
                   end
+ 'NS'+ ',' + case WHEN (coalesce(CONVERT(char(8), si.ShipmentDate, 112),'')) Like  '' then coalesce(CONVERT(char(8), ao.OrderDate, 112),'') else coalesce(CONVERT(char(8), si.ShipmentDate, 112),'')  end
+ case WHEN (COALESCE(RIGHT (si.BillingTimeSTamp ,6), ' ')) Like  '' then (Select replace(convert(varchar, getdate(),108),':','')) else COALESCE(RIGHT (si.BillingTimeSTamp ,6), ' ') end
+ coalesce(CONVERT(char(20), (Select Name From Drivers where DriverNumber = ao.PickupDriver)),'')
      AS [Report]
                  ,ROW_NUMBER() over (partition by
                  si.BSN , ao.Reference order by  si.BillingTimeSTamp) as rn
From Database01.[dbo].[ActiveOrders]  as ao
LEFT join Database02.[dbo].[ScanItem] as si
on si.CCOrderNo = ao.Orderno
where ao.Accountnumber = 12345  AND ao.Instructions  like 'Return%'
) as x
where rn=1

I need to add another case / if :into
      + Case ao.StatusID
                   WHEN 4      THEN 'X6'  
                   WHEN 5      THEN 'X4'  
                   else ''
                   end
CASE  ao.Instructions like '%NO ONE HOME%' then ao.StatusID = 'AP'

Any help is appreciated.
0
W.E.B
Asked:
W.E.B
1 Solution
 
lwadwellCommented:
do you mean?

+ Case ao.StatusID
                   WHEN 4      THEN 'X6'  
                   WHEN 5      THEN 'X4'  
                   else CASE WHEN ao.Instructions like '%NO ONE HOME%' then 'AP' end
                   end
0
 
W.E.BAuthor Commented:
Thanks,
that was easy.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now