-1

I have a data set and need to exclude patients who died with in 6 month of last reported infection.
following is the sample data:

id      icd9    AgeAtDx AgeAtFirstDM    AgeAtDeath  disbetes    infection
479174  I288.2  68      NULL            68.166666   0.0         0.0
479174  IV45.82 68.16   NULL            68.166666   0.0         0.0
479174  IV45.82 68      NULL            68.166666   0.0         0.0
479174  I272.4  68      NULL            68.166666   0.0         0.0
479174  I276.8  68      NULL            68.166666   0.0         0.0
479174  I338.3  68.16   NULL            68.166666   0.0         0.0
479174  I197.7  68      NULL            68.166666   0.0         0.0
479174  I600.00 68      NULL            68.166666   0.0         0.0
479174  I790.5  67.6    NULL            68.166666   0.0         0.0
479174  I573.8  67.75   NULL            68.166666   0.0         0.0
479174  IV66.7  68.16   NULL            68.166666   0.0         0.0
479174  I154.1  68.16   NULL            68.166666   0.0         0.0
479174  I401.9  68      NULL            68.166666   0.0         0.0
479174  I578.1  67.66   NULL            68.166666   0.0         0.0
479174  I414.01 68.16   NULL            68.166666   0.0         0.0
479174  IV45.82 68      NULL            68.166666   0.0         1.0
479174  I715.98 67.66   NULL            68.166666   0.0         0.0
479174  I607.84 67.66   NULL            68.166666   0.0         0.0
479174  I154.1  68      NULL            68.166666   0.0         0.0
479174  I300.00 68.16   NULL            68.166666   0.0         0.0

0.0 = no infetion, 1.0 = infection
so far I have the following code

select * 
from #Data1
except
select * 
from #Data1
where infection = 1.0
and AgeAtDeath < AgeAtDx  + interval '6 month'

it's not working. Any help will be greatly appreciated.

Ian Kemp
  • 28,293
  • 19
  • 112
  • 138
Samina Khalid
  • 1
  • 1
  • your sample missing a column represents the date, without it, it'll be a pain in the neck to get the requested records. – iSR5 Feb 02 '19 at 04:42
  • `interval '6 month' ` should be `interval '6' month` I think, shouldn't be? – Barbaros Özhan Feb 02 '19 at 05:00
  • "not working" helps no one - something that is especially true when the code you posted is not syntactically valid. So break your problem down into pieces. You said "after nth ... infection". That means you are looking for a number - what number? Or did you mean ANY infection? Because "nth" does not match "any". – SMor Feb 02 '19 at 13:01
  • In addition, how do you interpret the age columns? Does 68.16 mean 68 years + (0.16 * 365) days? Perhaps (0.16 * 364) days? Or does the decimal part mean 16 days? And how do you compute "6 months" - 6 * 30 days? 0.5 years? – SMor Feb 02 '19 at 13:03
  • What database are you really using? Please tag accordingly. Your code is not really consistent with any database -- having elements of MySQL, SQL Server, and Postgres. – Gordon Linoff Feb 02 '19 at 13:07

1 Answers1

0

The simplest way, in my opinion, to do this would be to use days instead of months as you can avoid having to calculate days per month etc. This is if your solution would allow that leeway. 

select * 
from #Data1
except
select * 
from #Data1
where infection = 1.0
and DATEDIFF(DAY,AgeAtDeath, AgeAtDx) <= 180  --6 months

If your solution does not allow that then this guy: Calculating number of full months between two dates in SQL wrote a UDF that would allow you to be more precise. But it may effect performance slightly using a function.

JMabee
  • 2,230
  • 2
  • 9
  • 13