Link to home
Start Free TrialLog in
Avatar of W.E.B
W.E.B

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of W.E.B
W.E.B

ASKER

Thanks,
that was easy.