CODEWITHSUNDEEP
sqlcase
Wîlliam Çhristopher Älexander
Wîlliam Çhristopher Älexander

Reputation: 17

SQL Nested Case Selects to perform Date manipulation

I'm trying to produce a payment date based off of 2 case selects. The first is to identify a payment schedule, and the second is to identify the payment day/date.

I'm also joining 2 tables, the first contains actual records, the second contains the payment schedule. They are both joined by the customer #.

Here's my code thus far:

Select comp, cust, data.inv, dat, amt, rel.PaymentSchedule, rel.PaymentDay, 
case rel.PaymentSchedule when 'BiMonthly' then 
    case rel.PaymentDay when '1' then DATEADD(MM, 2, dat)  end
    else 
    case rel.PaymentDay when '2' then DATEADD(MM, 2, dat) end 
else if 
    rel.PaymentSchedule when 'Daily' then 
    case rel.PaymentDay when '30' then DATEADD(MM, 1, dat)  end
    else 
    case rel.PaymentDay when '47' then DATEADD(dd, 47, dat) end 

end as PayDate  


from 
(
select b.ASCOMP as comp, b.ASCUST as cust, b.ASINV# as inv, b.ASIDAT as dat, b.ASINAM as amt
from SOLARSOFT.BNDSYS02.IVPDAT.AROHT a
right JOIN SOLARSOFT.BNDSYS02.IVPDAT.AROP b 
    on a.ASINV# = b.ASINV# 
where 
    a.ASINV# is null 
) as data
inner join tbl_payRel rel on cust = rel.Customer#
where 
    (inv not like 'D%')
    and ( inv not like 'P%')
and (cust <> 1308)
and  (cust <> 1067)
and ( cust <> 1295)

order by rel.PaymentSchedule, rel.PaymentDay, cust

Problem: Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'else'. Msg 4145, Level 15, State 1, Line 8 An expression of non-boolean type specified in a context where a condition is expected, near 'when'. Msg 156, Level 15, State 1, Line 24 Incorrect syntax near the keyword 'as'.

where did i go wrong? been working on overdrive all day!

I appreciate your help!

Upvotes: 0

Views: 277

Answers (1)

Twelfth
Twelfth

Reputation: 7180

Focusing on this snippet:

case rel.PaymentSchedule when 'BiMonthly' then 
case rel.PaymentDay when '1' then DATEADD(MM, 2, dat)  end
else 
case rel.PaymentDay when '2' then DATEADD(MM, 2, dat) end 
else if 
rel.PaymentSchedule when 'Daily' then 
case rel.PaymentDay when '30' then DATEADD(MM, 1, dat)  end
else 
case rel.PaymentDay when '47' then DATEADD(dd, 47, dat) end 

end as PayDate

Just a couple comments...the first two statements evaluate to the same, is that intentional? Why nest this as two cases? This could be one statement:

case rel.paymentday 
  when '1' then DATEADD(MM, 2, dat) 
  when '2' then DATEADD(MM, 2, dat)
  when '30' then DATEADD(MM, 1, dat)
  when '47' then DATEADD(dd, 47, dat)
end as PayDate

You don't need to end each case statement with an end...they can evaluate multiple cases as needed. I use this reference from microsoft when I can'ty remember how the syntax off hand: http://msdn.microsoft.com/en-us/library/ms181765.aspx

I should add...you can still nest this if you want..

case rel.PaymentSchedule 
   when 'BiMonthly' then 
   case rel.PaymentDay  when 1 then DATEADD(MM, 2, dat)
                        when 2 then DATEADD(MM, 2, dat)
   end 
   when 'Daily' then
   case rel.PaymentDay  when '30' then DATEADD(MM, 1, dat)
                        when '47' then DATEADD(dd, 47, dat)
   end
end

Upvotes: 1

Related Questions