User:Fishybt/sandbox

--Q2014-TVA-997992-01pp-RSV --Botao Yu

--0 Create Eligibility Table for 1+ months Minimum Eligibility during 10/1/2012 - 09/30/2013

SELECT floor ( MONTHS_BETWEEN('2012-10-01', '1995-01-01') +1) :: INTEGER START_POSITION, floor ( MONTHS_BETWEEN('2013-09-30', '2012-10-01') +1) :: INTEGER END_POSITION FROM DUAL; --213 12

CREATE TABLE Q2014_TVA_997992_01pp_RSV_ME AS SELECT A.pat_id, A.anon_ims_pat_id, A.der_yob FROM  IMS201312.ENROLL A WHERE  INSTR(SUBSTR(A.ESTRING, 214, 12), 'X') > 0 AND   EXISTS ( SELECT 1 FROM IMS201312.ENROLL2 B WHERE A.pat_id=B.pat_id AND B.string_type='ben_rx' AND INSTR(SUBSTR(B.STRING, 214, 12), 'Y') > 0) AND   EXISTS ( SELECT 1 FROM IMS201312.ENROLL2 C WHERE A.pat_id=C.pat_id AND C.string_type='ben_med' AND INSTR(SUBSTR(C.STRING, 214, 12), 'Y') > 0);

SELECT '0_ME' AS STEP, COUNT(distinct pat_id) FROM Q2014_TVA_997992_01pp_RSV_ME; --0_ME  25,185,666

--- --1	1+ claim with a Dx code for "RSV" during 10/1/2012 - 9/30/2013

CREATE TABLE Q2014_TVA_997992_01pp_RSV_P1 AS SELECT A.pat_id, A.from_dt, B.der_yob, (year(A.from_dt) - b.der_yob) as age FROM  IMS201312.CLAIMS A, 	Q2014_TVA_997992_01pp_RSV_ME B WHERE  A.pat_id=B.pat_id AND   A.from_dt BETWEEN '2012-10-01' AND '2013-09-30' AND   (       A.diag_admit in ('4801','46611','7707','V0482','4660','46611','46619','796') OR	   A.diag1 in ('4801','46611','7707','V0482','4660','46611','46619','796') OR	   A.diag2 in ('4801','46611','7707','V0482','4660','46611','46619','796') OR	   A.diag3 in ('4801','46611','7707','V0482','4660','46611','46619','796') OR	   A.diag4 in ('4801','46611','7707','V0482','4660','46611','46619','796') OR	   A.diag5 in ('4801','46611','7707','V0482','4660','46611','46619','796') OR	   A.diag6 in ('4801','46611','7707','V0482','4660','46611','46619','796') OR	   A.diag7 in ('4801','46611','7707','V0482','4660','46611','46619','796') OR	   A.diag8 in ('4801','46611','7707','V0482','4660','46611','46619','796') OR	   A.diag9 in ('4801','46611','7707','V0482','4660','46611','46619','796') OR	   A.diag10 in ('4801','46611','7707','V0482','4660','46611','46619','796') OR	   A.diag11 in ('4801','46611','7707','V0482','4660','46611','46619','796'));

SELECT count(*), COUNT (distinct pat_id) FROM Q2014_TVA_997992_01pp_RSV_P1; --1_P01 3672473 1142947

--2	of 1 include only patients age 0-1 years select count(distinct pat_id) from Q2014_TVA_997992_01pp_RSV_P1 where age>=0 and age <=1; --58206

--3	of 1 include only patients age 2-5 years select count(distinct pat_id) from Q2014_TVA_997992_01pp_RSV_P1 where age>=2 and age <=5; --77669

select count(*), count(distinct pat_id), age from Q2014_TVA_997992_01pp_RSV_P1 group by age order by age;

20	7	(null) 131170	17771	0 254280	45118	1 105345	27363	2 60390	20139	3 44455	17612	4 38505	16836	5 33695	15348	6 28291	13011	7 24030	11400	8 22694	10766	9 21443	10181	10 20722	10124	11 21299	9984	12 21397	10007	13 22599	10223	14 23093	10469	15 25113	10728	16 27204	11589	17 28982	11738	18 29193	11419	19 31132	11583	20 32133	11813	21 34152	12252	22 34502	12320	23 34649	12391	24 35386	12399	25 34291	12182	26 33249	11927	27 37158	12820	28 38129	13541	29 41948	14520	30 43821	15321	31 45987	16336	32 50043	17248	33 48749	16924	34 50331	17613	35 51348	18055	36 51498	17796	37 52954	18328	38 55961	18896	39 58308	19366	40 60983	20495	41 64547	21901	42 65890	22318	43 64202	21798	44 61792	20889	45 62616	21170	46 64510	21414	47 66598	22455	48 69467	23359	49 72017	24074	50 71854	23927	51 70810	24007	52 74559	24485	53 73433	24415	54 71895	24415	55 73333	24429	56 73175	24144	57 69330	23288	58 66955	22644	59 65871	22078	60 62864	21235	61 59218	19868	62 54172	18412	63 52135	17703	64 40481	13529	65 24834	8049	66 18178	5746	67 13294	4297	68 11954	3785	69 11691	3512	70 8830	2797	71 7372	2169	72 6802	1961	73 5934	1731	74 5541	1546	75 4900	1354	76 4262	1185	77 3944	1038	78 2995	869	79 3097	846	80 2798	716	81 2950	766	82 6733	1646	83 13105	2842	84 118	47	85 81	38	86 139	41	87 91	28	88 153	55	97 321	104	98