Solved

Case Statement - SQL

Posted on 2011-03-10
8
287 Views
Last Modified: 2012-05-11
SQL 2008

I have issue on rewriting my SQL Script for Case Statement.
I am adding Zero to the Column : DrugNDCNbr - Based on DrugNDCType.
So, we have DrugNDCType like 50,56,51,57 ..

Recently we have added new DrugNDCType : 49. If DrugNDCType is 49, Pick the actual value. I have to do this in a single query as because, i am using this syntax for joining also ..!

stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in (51, 57) then 6    when Dr.DrugNDCType = 52 then 10     end, 0, '0') as [NDC],                


For original SQL Query, i have kept as file attached. Please help me in rewriting this Case Statement.
select distinct            
     pat.NameLast as [Last Name],                
     pat.NameFirst as [First Name],            
                
     RIGHT( RTRIM( REPLACE( SSN, CHAR(160), '' ) ), 4 ) as [Last 4 Digit of SSN],            
     convert(varchar, Left(Pat.AdrsZip,5),101) as [ZipCode],                
            
     cty.County,                
     pat.SortCode1 as [340B],                    
               
     case when len(pat.DOB) = 7 then dateadd(d, (cast(RIGHT(pat.DOB,3) as int) -1), cast(LEFT(pat.DOB,4) as    DATE))else null end as [DOB],                    
        PAT.licnbr AS [Client ID],      
            
        case      
     when CONVERT(varchar, pat.weight)  = '1' then 'Insured'      
     when CONVERT(varchar, pat.weight) = '2' then  'Cash'      
     when CONVERT(varchar, pat.weight)  = '3' then  'ADAP'      
     else '0'      
     end as [Insurance Status],    
                      
        Pln.PlanId,                 
               
     Dr.GenericCode,            
     --Dr.GenericName,            
     Dr.Name as [Drug Name],                
     stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in (51, 57) then 6    when Dr.DrugNDCType = 52 then 10     end, 0, '0') as [NDC],                
     rx.RxNbr,                  
     case when len(Rx.FillDate) = 7 then dateadd(d, (cast(RIGHT(Rx.FillDate,3) as int) -1), cast(LEFT    (Rx.FillDate,4) as DATE))else null end as [Fill Date],                    
     rx.DispensedQty1 as [Dispensed Qty],                
     rx.Refills,                  
     rx.Daysupp as [Days Supplied],                    
     rx.Totalprice as [Plan Allows],      
         
     CASE WHEN pat.Weight = 2    
     THEN '0.0' ELSE rx.Cashpaid     
     END     
     AS [Copay],    
    
    
     CASE WHEN pat.Weight = 2    
     THEN '0.0' ELSE rx.PaidByPlan1     
     END     
     AS [Net due From Insurance]    
                   
     --rx.Cashpaid as [Copay],                
     --rx.PaidByPlan1 as [Net due From Insurance]      
                   
     ,BP.Price as [340B Price],                
     BP.PacketSize as [340B Packet Size],            
       
     case   
   when CONVERT(float, BP.PacketSize) > 0.0 then  
      CONVERT(float, BP.Price) / CONVERT(float, BP.PacketSize) * CONVERT(float,  rx.DispensedQty1)  
      else 0.00000000  
      End  as      [340B Cost for this Rx],  
         
     CP.[Column 1] as CardinalPrice,            
     CP.[Column 2] as PacketSize ,      
         
 case      
               when pat.weight = 1 then 22      
               when pat.Weight = 2 then  15      
               when pat.Weight = 3 then  22      
               else 0      
         end as [Dispensing Fees]    
             
     
     
            
from   patient pat                   
inner join   Rx Rx                    
on    Rx.PatNbrKey = Pat.PatKey              
                  
Left join   plans pln            
on    pln.PlanCodeKey = rx.Plan1Key            
            
inner join   drug dr                    
on    dr.DrugNbrKey = rx.DispensedDrugKey                  
            
left join  [340bprice] BP                 
on   Left(BP.NDC,11) = stuff(Dr.DrugNDCNbr, case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in    (51, 57) then 6 when Dr.DrugNDCType = 52 then 10 end, 0, '0')                 
            
left join  county cty                
on    cty.Zipcode = convert(varchar, Left(Pat.AdrsZip,5),101)             
                 
Left join  CardinalPrice CP            
on   Left(CP.[Column 0],11) = stuff(Dr.DrugNDCNbr, case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in (51, 57) then 6 when Dr.DrugNDCType = 52 then 10 end, 0, '0')            
            
where        pat.SortCode1 like '%340B%'                
-- SC MED            
and rx.Plan1Key <> 49            
 and  rx.Rxstatus <> 16            
  and  rx.Rxstatus <> 18            
  and  rx.Rxstatus <> 20            
and   Rx.FillDate BETWEEN YEAR('02/01/2011')*1000 + DATEPART(dy, '02/01/2011')                
AND   YEAR('02/28/2011')*1000 + DATEPART(dy, '02/28/2011') 
         
order by pat.NameLast Asc

Open in new window

0
Comment
Question by:chokka
  • 4
  • 2
  • 2
8 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 35097762
please explain what does this mean

"If DrugNDCType is 49, Pick the actual value"

you have this rigth now

case
     when Dr.DrugNDCType in (50, 56) then 1
     when Dr.DrugNDCType in (51, 57) then 6
     when Dr.DrugNDCType = 52 then 10
     when Dr.DrugNDCType = 49 then ??????
end
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35097871
stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1
                                         when Dr.DrugNDCType in (51, 57) then 6  
                                         when Dr.DrugNDCType = 52 then 10    
                                        when Dr.DrugNDCType = 49 then 1 end, 0,
                               case when Dr.DrugNDCType = 49 then substring(Dr.DrugNDCNbr,1,1)
                                       else  '0' end ) as [NDC],                
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35097878
sorry
stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1
                                         when Dr.DrugNDCType in (51, 57) then 6  
                                         when Dr.DrugNDCType = 52 then 10    
                                        when Dr.DrugNDCType = 49 then 1 end, 0,
                               case when Dr.DrugNDCType = 49 then space(0)
                                       else  '0' end ) as [NDC],    
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:chokka
ID: 35098275
I assume, we both have same understanding about MY Syntax of Appending Zero

stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in (51, 57) then 6
0
 

Author Comment

by:chokka
ID: 35098420
I made this query to simple and easy to understand

select  
stuff(Dr.DrugNDCNbr,
            case      when Dr.DrugNDCType in (50, 56) then 1
                        when Dr.DrugNDCType in (51, 57) then 6    
                        when Dr.DrugNDCType = 52 then 10    
                        -- when Dr.DrugNDCType = Dont add anything .. just give me the actual value
                        end, 0, '0') as [NDC],* from drug Dr
where Dr.drugnbrkey = 6284




How to rewrite this scenario ..
-- when Dr.DrugNDCType = Dont add anything .. just give me the actual value

0
 

Author Comment

by:chokka
ID: 35098481
space(0)
 

is not working ..!
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 35098537
maybe you want this:

select  
case
when Dr.DrugNDCType=49 then Dr.DrugNDCNbr
else
(
stuff(Dr.DrugNDCNbr,
            case      when Dr.DrugNDCType in (50, 56) then 1
                        when Dr.DrugNDCType in (51, 57) then 6    
                        when Dr.DrugNDCType = 52 then 10    
                        end, 0, '0')
)
end as [NDC],* from drug Dr
where Dr.drugnbrkey = 6284
0
 

Author Comment

by:chokka
ID: 35098579
Yes, HainKurt - You are right.

I posted the same issue on

http://stackoverflow.com/questions/5264712/sql-case-statement

Thank you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach 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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

770 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