Solved

Case Statement - SQL

Posted on 2011-03-10
8
292 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 56

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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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 56

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

Three Considerations for Containers

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read our article on Experts Exchange.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

623 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