vendredi 10 mai 2019

How to test for 2 values in a column where date and job are equal

If I have a credit of 12 and 14 and a writeoff of 0 and -4 and printable=1 then qty*paidamt in new column.

I used the case statement, with no luck; code and query below.

ALTER VIEW [dbo].[GEMTQAF] AS select tdate,job,qty as units, paidamt as unitAmount, qty*paidamt as ExtendedAmount,credit as credit, -123 as Charge, credits.writeoff as credittype, woseq as seq, postwho, credits.defaultsystemid as syscode, postdate, printable, cmpy, payor,'-123' as HCPCS, -123 as uniqueID,

Case
        When forCharge=0 then '1'
        Else
        forCharge
end as forCharge,

Case    
        When credits.code in (12,14) and printable=1 and credits.writeOff in (0,-4)
            then qty*paidamt
        Else
        0
end as pprimary

from trip_credits,credits where trip_credits.credit=credits.code union all select ta.tdate,ta.job,units, costperunit as unitAmount, convert(int, convert(int,round(units * costperunit,0))) as ExtendedAmount,-123 as credit, ta.addon as Charge, -99 as credittype, ta.seq, postwho, '-1' as syscode, postdate, printable, cmpy, -123 as payor, HCPCS, ta.uniqueID, -123 as forCharge,-123 as pprimary

from trip_addons ta,HCPCSandModifiers hm where ta.tdate=hm.tdate and ta.job=hm.job and ta.addon=hm.addon

Here is the output Where tdate='2017-07-30' and job='1240-A':

tdate job units unitAmount ExtendedAmount credit Charge credittype seq postwho syscode postdate printable cmpy payor HCPCS uniqueID forCharge pprimary 2017-07-30 1240-A 1.00 74092 74092 1 -123 -2 1 -201804 7 2018-05-04 1 250 11262 -123 -123 1 0 2017-07-30 1240-A 1.00 20944 20944 1 -123 -2 1 -201804 7 2018-05-04 1 250 11262 -123 -123 1 0 2017-07-30 1240-A 1.00 108180 108180 2 -123 -2 1 -201711 2017-12-21 0 250 11265 -123 -123 1 0 2017-07-30 1240-A 1.00 34580 34580 2 -123 -2 1 -201711 2017-12-21 0 250 11265 -123 -123 1 0 2017-07-30 1240-A -1.00 108180 -108180 2 -123 -2 1 -201804 2018-05-04 0 250 11265 -123 -123 1 0 2017-07-30 1240-A -1.00 34580 -34580 2 -123 -2 1 -201804 2018-05-04 0 250 11265 -123 -123 1 0 2017-07-30 1240-A 1.00 35991 35991 12 -123 0 0 201805 2018-05-29 1 250 11262 -123 -123 1 35991 2017-07-30 1240-A 1.00 12118 12118 12 -123 0 0 201805 2018-05-29 1 250 11262 -123 -123 2 12118 2017-07-30 1240-A 1.00 12273 12273 14 -123 -4 0 201805 2018-05-29 1 250 11265 -123 -123 1 12273 2017-07-30 1240-A 1.00 735 735 23 -123 -4 0 201805 2018-05-29 1 250 11262 -123 -123 1 0 2017-07-30 1240-A 1.00 247 247 23 -123 -4 0 201805 2018-05-29 1 250 11262 -123 -123 2 0 2017-07-30 1240-A 1.00 120000 120000 -123 4991 -99 1 201707 -1 2017-08-23 1 250 -123 A0427 1 -123 -123 2017-07-30 1240-A 14.00 2600 36400 -123 5000 -99 1 201707 -1 2017-08-23 1 250 -123 A0425 2 -123 -123

The output just puts the ExtendedAmount in pprimary in the rows that are in 12 and 14 from the credit column along with 0 and -4 from the credittype column. I need to only see the ExtendedAmount when both values are there for the credit column and the credittype column where tdate and job match. In other words for each job I am checking if medicare was primary payer and medicaid was secondary for a tdate+job only add those.

Aucun commentaire:

Enregistrer un commentaire