Solved

Case Statement - SQL

Posted on 2011-03-10
8
289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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:
Huseyin KAHRAMAN 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

740 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