User:Dzntree/sandbox

--MJ041515 --Create Eligibility Table for 1+ months Minimum Eligibility during 2006-1-1 to 2013-9-30 CREATE TABLE E2014_DSI_972850_02pp_allpp_ME AS SELECT A.pat_id FROM  IMS201312.ENROLL A WHERE  INSTR(SUBSTR(A.ESTRING, 133, 93), '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, 133, 93), '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, 133, 93), 'Y') > 0);

SELECT COUNT(distinct pat_id) FROM E2014_DSI_972850_02pp_allpp_ME; --

--Claims for Patient Cohort CREATE TABLE E2014_DSI_972850_02pp_allpp_Claims AS SELECT ROW_NUMBER OVER(ORDER BY A.pat_id, A.from_dt) AS ROWNUM, A.* FROM  IMS201312.CLAIMS A, E2014_DSI_972850_02pp_allpp_ME B WHERE  A.pat_id=B.pat_id AND   A.from_dt BETWEEN '2006-01-08' AND '2013-10-07';

SELECT count(1) FROM E2014_DSI_972850_02pp_allpp_Claims; --

CREATE TABLE E2014_DSI_972850_02pp_allpp_Pop AS SELECT SELECT ROW_NUMBER OVER(ORDER BY A.pat_id) AS ROWNUM, A.* FROM  IMS201312.ENROLL A, E2014_DSI_972850_02pp_allpp_ME B WHERE  A.pat_id=B.pat_id;

SELECT count(1) FROM E2014_DSI_972850_02pp_allpp_Pop; --

CREATE TABLE E2014_DSI_972850_02pp_allpp_Pop2 AS SELECT ROW_NUMBER OVER(ORDER BY A.pat_id, A.string_type) AS ROWNUM, A.* FROM  IMS201312.ENROLL2 A, E2014_DSI_972850_02pp_allpp_ME B WHERE  A.pat_id=B.pat_id;

SELECT count(1) FROM E2014_DSI_972850_02pp_allpp_Pop2; --

--perl ftp.pl mj041515 -claim E2014_DSI_972850_02pp_allpp_Claims -pop E2014_DSI_972850_02pp_allpp_Pop -pop E2014_DSI_972850_02pp_allpp_Pop2 -line 8 &

use strict;
 * 1) !/usr/bin/perl -w

=head1 USAGE:

run in command line: perl script.pl schema >xxx.log 2>xxx.err &

=head1 EXAMPLE:

nohup perl sql.pl zx017217 >sql.log 2>sql.err &

=cut

my $schema = $ARGV[0]; my %schema_pw = ('hm017218'=>7218, 'zx017217'=>7217, 'yg017216'=>7216, 'mj041515'=>'0415');

if(not exists $schema_pw{$schema}) {	print "Please confirm schema name is correct!\n"; exit 1; }

my $sql = qq(" CREATE TABLE E2014_DSI_972850_02pp_allpp_Claims AS SELECT ROW_NUMBER OVER(ORDER BY A.pat_id, A.from_dt) AS ROWNUM, A.* FROM  IMS201312.CLAIMS A, E2014_DSI_972850_02pp_allpp_ME B WHERE  A.pat_id=B.pat_id AND    A.from_dt BETWEEN '2006-01-08' AND '2013-10-07'; ");
 * 1) -- sql script #

system("vsql -h ngspdbaims01lp -U $schema -w 'ims\@$schema_pw{$schema}' -A -t -c $sql");
 * 1) --- run the sql script

CTAS: table "E2014_DSI_972850_02pp_allpp_Claims" was dropped in another session (DDL interference).

SELECT node_name, storage_path, disk_space_used_mb + disk_space_free_mb as disk_space_total_mb, disk_space_used_mb, disk_space_free_mb, disk_space_free_percent FROM disk_storage WHERE storage_path like '%data' ORDER BY node_name