Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Case Statement - SQL

Posted on 2011-03-10
8
Medium Priority
?
295 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 59

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 59

Accepted Solution

by:
HainKurt earned 2000 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

730 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