SQL 2008

Posted on 2012-08-22
Last Modified: 2012-08-22
I'm using this Stored Procedure for one of my clients.

Select report from (
      '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 ''
+ '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 ''
CASE  ao.Instructions like '%NO ONE HOME%' then ao.StatusID = 'AP'

Any help is appreciated.
Question by:Wass_QA
    LVL 25

    Accepted Solution

    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

    Author Closing Comment

    that was easy.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

         When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now