Case Statement - SQL

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

Select allOpen in new window