CIS SQL queries

Jake Solomon edited this page Nov 18, 2013 · 3 revisions
/******
Author:			Jake Solomon
Last updated:	9/17/2013

Description: Create tables for all WAIVED cases with upcoming discontinuations in the current month.
				- dc_waived
				- dc_waived_medical
				- dc_waived_calfresh_english
				- dc_waived_calfresh_spanish
				- dc_waived_calfresh_chinese
				- dc_waived_calfresh_tagalog
				- dc_waived_calfresh_russian
				- dc_waived_calfresh_vietnamese
				- dc_waived_calfresh_other

Changes:
- 7/24 - Removing cp.eff_bgn_dt per Alan's suggestion
- 8/23 - Filtering out DV cases - special char = 1P (note this is VERY rare, about 120 cases total in daily data)
- 8/29 - Removed the c.cs_sts_cd = 'OP' constraint. Cases seem to remain open when they're enrolled in another program.
- 9/17 - Moved everything to one file that now creates separate tables per program/language combo
- 10/08 - Added dc_waived_calfresh_other table because we decided to send reminders in English for all other languages.
- 11/13 - Removed CalWorks cases from dc_waived_calfresh tables because CW clients should call their worker, not the CalFresh call center.
- 11/13 - Added dc_waived_medical table with all soon to be discontinued waived MediCal cases

******/

USE CFA

/*------------------------------------------------------------------------------

				Prep - drop tables

-------------------------------------------------------------------------------*/

-- Drop temp tables
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name LIKE '#dc_waived%') DROP TABLE #dc_waived
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name LIKE '#dc_waived_calfresh%') DROP TABLE #dc_waived_calfresh
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name LIKE '#dc_waived_medical%') DROP TABLE #dc_waived_medical

-- Drop regular tables
IF OBJECT_ID('dbo.dc_waived', 'U') IS NOT NULL DROP TABLE dbo.dc_waived
IF OBJECT_ID('dbo.dc_waived_medical', 'U') IS NOT NULL DROP TABLE dbo.dc_waived_medical
IF OBJECT_ID('dbo.dc_waived_calfresh_english', 'U') IS NOT NULL DROP TABLE dbo.dc_waived_calfresh_english
IF OBJECT_ID('dbo.dc_waived_calfresh_spanish', 'U') IS NOT NULL DROP TABLE dbo.dc_waived_calfresh_spanish
IF OBJECT_ID('dbo.dc_waived_calfresh_chinese', 'U') IS NOT NULL DROP TABLE dbo.dc_waived_calfresh_chinese
IF OBJECT_ID('dbo.dc_waived_calfresh_tagalog', 'U') IS NOT NULL DROP TABLE dbo.dc_waived_calfresh_tagalog
IF OBJECT_ID('dbo.dc_waived_calfresh_russian', 'U') IS NOT NULL DROP TABLE dbo.dc_waived_calfresh_russian
IF OBJECT_ID('dbo.dc_waived_calfresh_vietnamese', 'U') IS NOT NULL DROP TABLE dbo.dc_waived_calfresh_vietnamese
IF OBJECT_ID('dbo.dc_waived_calfresh_other', 'U') IS NOT NULL DROP TABLE dbo.dc_waived_calfresh_other

/*------------------------------------------------------------------------------

				Create temporary #dc_waived table with all waived DC cases
				- less minor cases
				- less DV cases
				- less non-waived cases

-------------------------------------------------------------------------------*/
SELECT c.msg_tel_num AS Phone
	, (SELECT TOP 1 UPD_DTM
		FROM  CIS_.dbo.CS
		ORDER BY UPD_DTM DESC
		) AS DataSourceDateTime
	, getdate() AS QueryDateTime
	, c.cs_id AS CaseNumber
	, cp.pgm_typ_cd AS Program
	, cp.sts_dt AS DiscontinuationEffectiveDate
	, c.first_nm AS CaseFirst
	, c.last_nm AS CaseLast
	, c.prm_lang_cd AS CaseLanguage
	, c.hom_tel_num AS HomePhone
	, c.day_tel_num AS DayPhone
	, c.msg_tel_num AS MsgPhone
	, c.cs_email_adr AS Email

INTO #dc_waived

-- Case program table
FROM cis_.dbo.cs_pgm cp

-- Join most recent eligibility results for case
INNER JOIN cis_.dbo.ag_elig_rslt ager
	ON cp.cs_id = ager.cs_id
	AND ager.pgm_typ_cd = cp.pgm_typ_cd
	AND ager.ag_eff_end_dt is NULL

-- Join case info
INNER JOIN cis_.dbo.cs c
	ON cp.cs_id = c.cs_id
	AND c.eff_end_dt is NULL

WHERE cp.eff_end_dt is NULL
	-- Include cases set to the last day of the current month (FC is an exception...)
	AND cp.pgm_sts_cd = 'DC'
	AND cp.sts_dt = DATEADD(month,DATEDIFF(month, 0, GETDATE()) + 1,0) - 1

	-- Include case that failed most recent eligibility test
	AND ager.cur_elig_ind = 'A'
	AND ager.elig_sts_cd = 'FL'
	AND ager.delt_rcrd_sw = 'N' -- Y is flagged for deletion in overnight batch

	-- Exclude minor consent Medi-Cal (MI), expedited (EX) or immediate need (IN) CalFresh
	AND cp.pgm_typ_cd NOT IN ('MI','EX','IN')

	--  Exclude domestic violence cases - special indicator = 1P
	AND (SELECT count(*)
			FROM cis_.dbo.cs_spl_sitn csSC
			WHERE csSC.eff_end_dt is null
			AND csSC.cs_id = cp.cs_id
			AND csSC.sitn_typ_cd = '1P'
			) = 0

	--  Include ONLY waived cases - special indicator = 8T = Text messages OK + setup on 6/2013
	AND (SELECT count(*)
		FROM cis_.dbo.cs_spl_sitn csSC
		WHERE csSC.eff_end_dt is null
		AND csSC.cs_id = cp.cs_id
		AND csSC.sitn_typ_cd = '8T'
		AND year(csSC.eff_bgn_dt) >= '2013'
		) > 0

/*------------------------------------------------------------------------------

	CALFRESH: Create temporary #dc_waived_calfresh table with all waived DC CalFresh cases
	- less CalWorks cases (CR = CalWorks, AP = approved, eff_end_dt = null = active record)

-------------------------------------------------------------------------------*/
SELECT * INTO #dc_waived_calfresh
	FROM #dc_waived
	WHERE Program = 'FS'

	-- Have 0 approved CalWorks cases
	AND (SELECT count(*)
			FROM cis_.dbo.cs_pgm cp
			WHERE cp.eff_end_dt is null
			AND cp.cs_id = CaseNumber
			AND cp.pgm_typ_cd = 'CR'
			AND cp.pgm_sts_cd = 'AP'
			) = 0

/*------------------------------------------------------------------------------

	MEDI-CAL: Create temporary #dc_waived_medical table with all waived DC MediCal cases
	- less CalWorks cases (CR = CalWorks, AP = approved, eff_end_dt = null = active record)

-------------------------------------------------------------------------------*/
SELECT * INTO #dc_waived_medical
	FROM #dc_waived
	WHERE Program = 'MC'

	-- Have 0 approved CalWorks cases
	AND (SELECT count(*)
			FROM cis_.dbo.cs_pgm cp
			WHERE cp.eff_end_dt is null
			AND cp.cs_id = CaseNumber
			AND cp.pgm_typ_cd = 'CR'
			AND cp.pgm_sts_cd = 'AP'
			) = 0

/*------------------------------------------------------------------------------

		Save tables by program/language to be pushed to Promptly DB

-------------------------------------------------------------------------------*/
-- All waived
SELECT * INTO dbo.dc_waived FROM #dc_waived

-- All waived MediCal
SELECT * INTO dbo.dc_waived_medical FROM #dc_waived_medical

-- CalFresh by language
SELECT * INTO dbo.dc_waived_calfresh_english
	FROM #dc_waived_calfresh
	WHERE CaseLanguage = 'EN'

SELECT * INTO dbo.dc_waived_calfresh_spanish
	FROM #dc_waived_calfresh
	WHERE CaseLanguage = 'SP'

SELECT * INTO dbo.dc_waived_calfresh_chinese
	FROM #dc_waived_calfresh
	WHERE CaseLanguage = 'CN'

SELECT * INTO dbo.dc_waived_calfresh_tagalog
	FROM #dc_waived_calfresh
	WHERE CaseLanguage = 'TU'

SELECT * INTO dbo.dc_waived_calfresh_russian
	FROM #dc_waived_calfresh
	WHERE CaseLanguage = 'RU'

SELECT * INTO dbo.dc_waived_calfresh_vietnamese
	FROM #dc_waived_calfresh
	WHERE CaseLanguage = 'VI'

SELECT * INTO dbo.dc_waived_calfresh_other
	FROM #dc_waived_calfresh
	WHERE CaseLanguage NOT IN ('EN','SP','CN','TU','RU','VI')
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.