Solved

Case Statement - SQL

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

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
t-sql month question 8 42
Sort by Month and Year - SQL 3 22
SQL Help - 12 37
BULK INSERT most recent CSV 19 18
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now