/*
Title: IDDT Service Utilization Report
Description: Pre and Current/post IDDT TX by Cost, Volume, Type of Services, and
				Hospital bed days
Data Sources: Claims extract, PCS extract, Affiliation extract, support table with IDDT participating
				 agencies.
Author: jdm 4/2/2008

Note:  This code was writen and tested again MS SQL Server 2005.  This code should work with some tweaking
    	  on other major SQL based systems such as Oracle, DB2, and MySQL.  Since I am not all that much familiar
		  with those systems, you will need to determine what statements to tweak.  Also this code does
		  utilize some features that are not available on pre-2005 MS SQL Server 2005 systems.  I have noted those sections
		  and offer rewrite suggestions.

		  Your table and field names may differ slightly from mine.  You will need to change to reflect the names in your 
		  system.  
 
		This is intended to be used as an example of how use the IDDT affiliation code
		This example does not profess to be the best or most efficient way to create this report.
		It is simply an attempt at a working example (at least I hope it is a working exmaple!)  
		This has not been veted by anyone else so all errors and flaws are my responsibility.
		If anyone spots any code errors or logic flaws, please let me know
*/

-- Change this to your database name
USE Bullfrog;
GO


-- Step 1.) Create Support tables

-- 1a.) Create table to store SUBNO, IDDT Min Effective Date, pre 1, 2, 3 yr dates, and post 1, 2, 3 dates

IF OBJECT_ID('dbo.tbldata_IDDTServiceUtilizationDts') IS NOT NULL
	DROP TABLE dbo.tbldata_IDDTServiceUtilizationDts
GO

CREATE TABLE dbo.tbldata_IDDTServiceUtilizationDts
(
SUBNO	VARCHAR(12)	PRIMARY KEY,
IDDT_MINEFFDATE	DATETIME,
Pre365Dt	DATETIME,
Pre730Dt	DATETIME,
Pre1095Dt	DATETIME,
Post365Dt	DATETIME,
Post730Dt	DATETIME,
Post1095Dt	DATETIME
)

GO

-- 1b.) Create table to store service utilization



IF OBJECT_ID('dbo.tbldata_IDDTServiceUtilization') IS NOT NULL
	DROP TABLE dbo.tbldata_IDDTServiceUtilization
GO

CREATE TABLE dbo.tbldata_IDDTServiceUtilization
(PROCDESC	VARCHAR(50)	PRIMARY KEY,
Pre1095Qty	INT,
Pre1095Cost	DECIMAL(10,2),
Pre1095ClientCnt INT,
Pre1095CostPerClient DECIMAL(7,2),
Pre730Qty	INT,
Pre730Cost	DECIMAL(10,2),
Pre730ClientCnt INT,
Pre730CostPerClient DECIMAL(7,2),
Pre365Qty	INT,
Pre365Cost	DECIMAL(10,2),
Pre365ClientCnt INT,
Pre365CostPerClient DECIMAL(7,2),
Post365Qty	INT,
Post365Cost	DECIMAL(10,2),
Post365ClientCnt INT,
Post365CostPerClient DECIMAL(7,2),
Post730Qty	INT,
Post730Cost	DECIMAL(10,2),
Post730ClientCnt INT,
Post730CostPerClient DECIMAL(7,2),
Post1095Qty	INT,
Post1095Cost DECIMAL(10,2),
Post1095ClientCnt INT,
Post1095CostPerClient DECIMAL(7,2)
)
GO

--1c) Create table to store Hospital PerDiem rates
--	  This is necessary to add Fiscal Year and PerDiem rate information to PCS data

IF OBJECT_ID('dbo.tbldata_HospitalPerDiemRates') IS NOT NULL
	DROP TABLE dbo.tbldata_HospitalPerDiemRates
GO


CREATE TABLE dbo.tbldata_HospitalPerDiemRates
(FiscalYear CHAR(6) PRIMARY KEY,
 BegDate DATETIME,
 EndDate DATETIME,
 PerDiemRate DECIMAL(5,2)
)
GO

-- Populate with values

INSERT INTO dbo.tbldata_HospitalPerDiemRates
VALUES
('2000', '19990701', '20000630', 327.00)
INSERT INTO dbo.tbldata_HospitalPerDiemRates
VALUES
('2001', '20000701', '20010630', 327.00)
INSERT INTO dbo.tbldata_HospitalPerDiemRates
VALUES
('2002', '20010701', '20020630', 351.00)
INSERT INTO dbo.tbldata_HospitalPerDiemRates
VALUES
('2003', '20020701', '20030630', 353.00)
INSERT INTO dbo.tbldata_HospitalPerDiemRates
VALUES
('2004', '20030701', '20040630', 441.00)
INSERT INTO dbo.tbldata_HospitalPerDiemRates
VALUES
('2005', '20040701', '20050630', 446.00)
INSERT INTO dbo.tbldata_HospitalPerDiemRates
VALUES
('2006', '20050701', '20060630', 470.00)
INSERT INTO dbo.tbldata_HospitalPerDiemRates
VALUES
('2007', '20060701', '20070630', 481.00)
INSERT INTO dbo.tbldata_HospitalPerDiemRates
VALUES
('2008', '20070701', '20080630', 481.00)
INSERT INTO dbo.tbldata_HospitalPerDiemRates
VALUES
('2009', '20080701', '20000630', 481.00)

GO

-- This table will hold PCS utilzation and cost information

IF OBJECT_ID('tbdata_PCSUtilization') IS NOT NULL
	DROP TABLE tbdata_PCSUtilization
GO

CREATE TABLE tbdata_PCSUtilization
(
	ClientId	VARCHAR(30),
	UCInumber	VARCHAR(7),
	Admissiondate DATETIME,
	Movementdate DATETIME,
	NumDays	INT,
	FiscalYear CHAR(4),
	PerDiemRate DECIMAL(5,2),
	TotalCost DECIMAL(8,2)
)

GO

-- Create Stored Procedure

IF OBJECT_ID('dbo.sproc_IDDTUtilization') IS NOT NULL
	DROP PROCEDURE dbo.sproc_IDDTUtilization
GO

CREATE PROCEDURE dbo.sproc_IDDTUtilization
	 @BegServDate AS DATETIME,
	 @EndServDate AS DATETIME

AS

DECLARE @HospBedDaysTitle AS VARCHAR(20)
SET @HospBedDaysTitle = 'HOSPITAL BED DAYS'


-- Step 2:  Create support Common Table Expressions (CTEs) This feature is available 
-- Note: CTEs are not supported on pre-2005 MS SQL Server systems.  You will need to rewrite these
--		 as derived tables when used or rewrite as a temporary table.

-- These CTEs can be rewritten as either views, temporary tables, or derived tables



-- Remove records from support tables

TRUNCATE TABLE dbo.tbldata_IDDTServiceUtilizationDts
TRUNCATE TABLE dbo.tbldata_IDDTServiceUtilization
TRUNCATE TABLE dbo.tbdata_PCSUtilization


-- 2a) CTE that returns first effective date that an individual was enrolled 

-- Return Affiliation records with "IDDT" Affiliation code for clients that had services between
-- the given date range.
-- Note: The OVER() fucntion used to return minimum effective date is not supported
--		 in Pre-2005 MS SQL Server.  May want to rewrite using a subquery or joins
 

WITH CTE_Affil_IDDT
AS
(
	SELECT SUBNO, EFFDATE, TERMDATE, AFFCODE, MIN(EFFDATE) OVER(PARTITION BY SUBNO, AFFCODE) AS MINEFFDATE
	FROM tbldata_Affiliation_Extract AS A
	WHERE AFFCODE = 'IDDT'
	AND EXISTS
	-- check to see if client had services for given date range
			(SELECT SUBNO FROM tbldata_claimsextract AS C
				WHERE C.SUBNO = A.SUBNO AND SERVDATE BETWEEN @BegServDate AND @EndServDate)
 ),

-- Returns IDDT affiliation records with the first effective date only

CTE_FirstEffDt_IDDT
AS
(
SELECT * FROM CTE_Affil_IDDT
WHERE EFFDATE = MINEFFDATE
)

-- Populate dbo.tbldata_IDDTServiceUtilizationDts table

INSERT INTO
dbo.tbldata_IDDTServiceUtilizationDts
(SUBNO, IDDT_MINEFFDATE)
SELECT SUBNO, MINEFFDATE
FROM CTE_FirstEffDt_IDDT

-- update Pre and Post date values

UPDATE dbo.tbldata_IDDTServiceUtilizationDts
SET Pre365Dt = DATEADD(day, -365, IDDT_MINEFFDATE)

UPDATE dbo.tbldata_IDDTServiceUtilizationDts
SET Pre730Dt = DATEADD(day, -730, IDDT_MINEFFDATE)

UPDATE dbo.tbldata_IDDTServiceUtilizationDts
SET Pre1095Dt = DATEADD(day, -1095, IDDT_MINEFFDATE)

UPDATE dbo.tbldata_IDDTServiceUtilizationDts
SET Post365Dt = DATEADD(day, 365, IDDT_MINEFFDATE)

UPDATE dbo.tbldata_IDDTServiceUtilizationDts
SET Post730Dt = DATEADD(day, 730, IDDT_MINEFFDATE)

UPDATE dbo.tbldata_IDDTServiceUtilizationDts
SET Post1095Dt = DATEADD(day, 1095, IDDT_MINEFFDATE)

-- Populate table with Hospital Bed days cost


INSERT INTO tbdata_PCSUtilization
(Clientid, UCInumber, Admissiondate, Movementdate, NumDays, FiscalYear, PerDiemRate)
SELECT Clientid, UCInumber, Admissiondate, Movementdate, Netdaysyear
		AS NumDays, FiscalYear, PerDiemRate
FROM
(
SELECT PCS.Clientid, PCS.UCInumber, PCS.Admissiondate, PCS.Movementdate, PCS.Netdaysmonth, PCS.Netdaysyear, 
	HospRate.FiscalYear, HospRate.PerDiemRate
FROM tbldata_PCS AS PCS
JOIN tbldata_HospitalPerDiemRates AS HospRate
ON PCS.Movementdate BETWEEN HospRate.BegDate AND HospRate.EndDate) AS PCS

-- Calculate total PCS cost

UPDATE tbdata_PCSUtilization
SET TotalCost = NumDays * PerDiemRate

-- Step XX Populate Main Data table

-- Note: brdPROCDESC is a computed field that returns the procedure name based on PROCCODE and MODIFIER
--	     and is not included in the claims extract.  


-- Insert eilligible service names into a temp table

-- Delete temp table if already exits

IF OBJECT_ID('tempdb..#IDDTServices') IS NOT NULL	
	DROP TABLE #IDDTServices


SELECT DISTINCT brdPROCDESC 
INTO #IDDTServices
FROM tbldata_claimsextract
WHERE SUBNO IN
	(SELECT SUBNO FROM dbo.tbldata_IDDTServiceUtilizationDts)
AND SERVDATE BETWEEN (SELECT MIN(Pre1095Dt) FROM dbo.tbldata_IDDTServiceUtilizationDts) AND
	(SELECT MAX(Post1095Dt) FROM dbo.tbldata_IDDTServiceUtilizationDts)
AND PROVNO IN
	(
	SELECT UPID FROM tbldata_IDDTProviders
	)

-- Insert place holder for hospital bed days

INSERT INTO #IDDTServices
VALUES
(@HospBedDaysTitle)


-- Generate Qty and Net sums for services dates between 2 and 3 yrs prior to IDDT program

IF OBJECT_ID('tempdb..#IDDTPre3') IS NOT NULL	
	DROP TABLE #IDDTPre3



SELECT brdPROCDESC, SUM(QUANTITY) AS QtySum, SUM(NETAMT) AS NetAmtSum, COUNT(DISTINCT SUBNO) AS ClientCnt
INTO #IDDTPre3
FROM tbldata_claimsextract AS Claims
WHERE EXISTS
	(SELECT * FROM dbo.tbldata_IDDTServiceUtilizationDts AS IDDT
		WHERE IDDT.SUBNO = Claims.SUBNO AND Claims.SERVDATE BETWEEN IDDT.Pre1095Dt AND IDDT.Pre730Dt)
-- Return records for IDDT participating agencies
AND PROVNO IN
	(
	SELECT UPID FROM tbldata_IDDTProviders
	)
GROUP BY brdPROCDESC

-- Insert Hospital Bed Day Expenses

INSERT INTO #IDDTPre3
SELECT @HospBedDaysTitle, SUM(NumDays), SUM(TotalCost), COUNT(DISTINCT UCInumber)
FROM tbdata_PCSUtilization AS PCS
WHERE EXISTS
	(SELECT * FROM dbo.tbldata_IDDTServiceUtilizationDts AS IDDT
		WHERE IDDT.SUBNO = PCS.UCInumber AND PCS.Movementdate BETWEEN IDDT.Pre1095Dt AND IDDT.Pre730Dt)




-- Generate Qty and Net sums for services dates between 1 and 2 yrs prior to IDDT program

IF OBJECT_ID('tempdb..#IDDTPre2') IS NOT NULL	
	DROP TABLE #IDDTPre2


SELECT brdPROCDESC, SUM(QUANTITY) AS QtySum, SUM(NETAMT) AS NetAmtSum, COUNT(DISTINCT SUBNO) AS ClientCnt
INTO #IDDTPre2
FROM tbldata_claimsextract AS Claims
WHERE EXISTS
	(SELECT * FROM dbo.tbldata_IDDTServiceUtilizationDts AS IDDT
		WHERE IDDT.SUBNO = Claims.SUBNO AND Claims.SERVDATE BETWEEN IDDT.Pre730Dt AND IDDT.Pre365Dt)
-- Return records for IDDT participating agencies
AND PROVNO IN
	(
	SELECT UPID FROM tbldata_IDDTProviders
	)
GROUP BY brdPROCDESC

-- Insert Hospital Bed Day Expenses

INSERT INTO #IDDTPre2
SELECT @HospBedDaysTitle, SUM(NumDays), SUM(TotalCost), COUNT(DISTINCT UCInumber)
FROM tbdata_PCSUtilization AS PCS
WHERE EXISTS
	(SELECT * FROM dbo.tbldata_IDDTServiceUtilizationDts AS IDDT
		WHERE IDDT.SUBNO = PCS.UCInumber AND PCS.Movementdate BETWEEN IDDT.Pre730Dt AND IDDT.Pre365Dt)


-- Generate Qty and Net sums for services dates between IDDT enrollment and 1 yrs prior 

IF OBJECT_ID('tempdb..#IDDTPre1') IS NOT NULL	
	DROP TABLE #IDDTPre1


SELECT brdPROCDESC, SUM(QUANTITY) AS QtySum, SUM(NETAMT) AS NetAmtSum, COUNT(DISTINCT SUBNO) AS ClientCnt
INTO #IDDTPre1
FROM tbldata_claimsextract AS Claims
WHERE EXISTS
	(SELECT * FROM dbo.tbldata_IDDTServiceUtilizationDts AS IDDT
		WHERE IDDT.SUBNO = Claims.SUBNO AND Claims.SERVDATE BETWEEN IDDT.Pre365Dt AND IDDT.IDDT_MINEFFDATE)
-- Return records for IDDT participating agencies
AND PROVNO IN
	(
	SELECT UPID FROM tbldata_IDDTProviders
	)
GROUP BY brdPROCDESC

-- Insert Hospital Bed Day Expenses

INSERT INTO #IDDTPre1
SELECT @HospBedDaysTitle, SUM(NumDays), SUM(TotalCost), COUNT(DISTINCT UCInumber)
FROM tbdata_PCSUtilization AS PCS
WHERE EXISTS
	(SELECT * FROM dbo.tbldata_IDDTServiceUtilizationDts AS IDDT
		WHERE IDDT.SUBNO = PCS.UCInumber AND PCS.Movementdate BETWEEN IDDT.Pre365Dt AND IDDT.IDDT_MINEFFDATE)






-- Generate Qty and Net sums for services dates between IDDT Min Effective date and 1 yr (post 365)

IF OBJECT_ID('tempdb..#IDDTPost1') IS NOT NULL	
	DROP TABLE #IDDTPost1


SELECT brdPROCDESC, SUM(QUANTITY) AS QtySum, SUM(NETAMT) AS NetAmtSum, COUNT(DISTINCT SUBNO) AS ClientCnt
INTO #IDDTPost1
FROM tbldata_claimsextract AS Claims
WHERE EXISTS
	(SELECT * FROM dbo.tbldata_IDDTServiceUtilizationDts AS IDDT
		WHERE IDDT.SUBNO = Claims.SUBNO AND Claims.SERVDATE BETWEEN IDDT.IDDT_MINEFFDATE AND IDDT.Post365Dt)
-- Return records for IDDT participating agencies
AND PROVNO IN
	(
	SELECT UPID FROM tbldata_IDDTProviders
	)
GROUP BY brdPROCDESC

-- Insert Hospital Bed Day Expenses

INSERT INTO #IDDTPost1
SELECT @HospBedDaysTitle, SUM(NumDays), SUM(TotalCost), COUNT(DISTINCT UCInumber)
FROM tbdata_PCSUtilization AS PCS
WHERE EXISTS
	(SELECT * FROM dbo.tbldata_IDDTServiceUtilizationDts AS IDDT
		WHERE IDDT.SUBNO = PCS.UCInumber AND PCS.Movementdate BETWEEN  IDDT.IDDT_MINEFFDATE AND IDDT.Post365Dt)




-- Generate Qty and Net sums for services dates between 1 yr and 2 yrs after IDDT enrollment

IF OBJECT_ID('tempdb..#IDDTPost2') IS NOT NULL	
	DROP TABLE #IDDTPost2


SELECT brdPROCDESC, SUM(QUANTITY) AS QtySum, SUM(NETAMT) AS NetAmtSum, COUNT(DISTINCT SUBNO) AS ClientCnt
INTO #IDDTPost2
FROM tbldata_claimsextract AS Claims
WHERE EXISTS
	(SELECT * FROM dbo.tbldata_IDDTServiceUtilizationDts AS IDDT
		WHERE IDDT.SUBNO = Claims.SUBNO AND Claims.SERVDATE BETWEEN IDDT.Post365Dt AND IDDT.Post730Dt)
-- Return records for IDDT participating agencies
AND PROVNO IN
	(
	SELECT UPID FROM tbldata_IDDTProviders
	)
GROUP BY brdPROCDESC

-- Insert Hospital Bed Day Expenses

INSERT INTO #IDDTPost2
SELECT @HospBedDaysTitle, SUM(NumDays), SUM(TotalCost), COUNT(DISTINCT UCInumber)
FROM tbdata_PCSUtilization AS PCS
WHERE EXISTS
	(SELECT * FROM dbo.tbldata_IDDTServiceUtilizationDts AS IDDT
		WHERE IDDT.SUBNO = PCS.UCInumber AND PCS.Movementdate BETWEEN IDDT.Post365Dt AND IDDT.Post730Dt)



-- Generate Qty and Net sums for services dates between 2 yr and 3 yrs after IDDT enrollment

IF OBJECT_ID('tempdb..#IDDTPost3') IS NOT NULL	
	DROP TABLE #IDDTPost3


SELECT brdPROCDESC, SUM(QUANTITY) AS QtySum, SUM(NETAMT) AS NetAmtSum, COUNT(DISTINCT SUBNO) AS ClientCnt
INTO #IDDTPost3
FROM tbldata_claimsextract AS Claims
WHERE EXISTS
	(SELECT * FROM dbo.tbldata_IDDTServiceUtilizationDts AS IDDT
		WHERE IDDT.SUBNO = Claims.SUBNO AND Claims.SERVDATE BETWEEN IDDT.Post730Dt AND IDDT.Post1095Dt)
-- Return records for IDDT participating agencies
AND PROVNO IN
	(
	SELECT UPID FROM tbldata_IDDTProviders
	)
GROUP BY brdPROCDESC

-- Insert Hospital Bed Day Expenses

INSERT INTO #IDDTPost3
SELECT @HospBedDaysTitle, SUM(NumDays), SUM(TotalCost), COUNT(DISTINCT UCInumber)
FROM tbdata_PCSUtilization AS PCS
WHERE EXISTS
	(SELECT * FROM dbo.tbldata_IDDTServiceUtilizationDts AS IDDT
		WHERE IDDT.SUBNO = PCS.UCInumber AND PCS.Movementdate BETWEEN IDDT.Post730Dt AND IDDT.Post1095Dt)


-- Insert Service Utilization 

INSERT INTO dbo.tbldata_IDDTServiceUtilization
(PROCDESC,
Pre1095Qty,
Pre1095Cost,
Pre1095ClientCnt,
Pre730Qty,
Pre730Cost,
Pre730ClientCnt,
Pre365Qty,
Pre365Cost,
Pre365ClientCnt,
Post365Qty,
Post365Cost,
Post365ClientCnt,
Post730Qty,
Post730Cost,
Post730ClientCnt,
Post1095Qty,
Post1095Cost,
Post1095ClientCnt
)
SELECT Serv.brdPROCDESC, Pre3.QtySum, Pre3.NetAmtSum, Pre3.ClientCnt, Pre2.QtySum, Pre2.NetAmtSum, Pre2.ClientCnt,
 Pre1.QtySum, Pre1.NetAmtSum, Pre1.ClientCnt, Post1.QtySum, Post1.NetAmtSum, Post1.ClientCnt,
 Post2.QtySum, Post2.NetAmtSum, Post2.ClientCnt, Post3.QtySum, Post3.NetAmtSum, Post3.ClientCnt
FROM #IDDTServices AS Serv
	LEFT OUTER JOIN #IDDTPre3 AS Pre3
	ON Pre3.brdPROCDESC = Serv.brdPROCDESC
	LEFT OUTER JOIN #IDDTPre2 AS Pre2
	ON Pre2.brdPROCDESC = Serv.brdPROCDESC
	LEFT OUTER JOIN #IDDTPre1 AS Pre1
	ON Pre1.brdPROCDESC = Serv.brdPROCDESC
	LEFT OUTER JOIN #IDDTPost1 AS Post1
	ON Post1.brdPROCDESC = Serv.brdPROCDESC
	LEFT OUTER JOIN #IDDTPost2 AS Post2
	ON Post2.brdPROCDESC = Serv.brdPROCDESC
	LEFT OUTER JOIN #IDDTPost3 AS Post3
	ON Post3.brdPROCDESC = Serv.brdPROCDESC

-- Calculate Avg cost per client

UPDATE dbo.tbldata_IDDTServiceUtilization
SET Pre1095CostPerClient = Pre1095Cost/Pre1095ClientCnt

UPDATE dbo.tbldata_IDDTServiceUtilization
SET Pre730CostPerClient = Pre730Cost/Pre730ClientCnt

UPDATE dbo.tbldata_IDDTServiceUtilization
SET Pre365CostPerClient = Pre365Cost/Pre365ClientCnt

UPDATE dbo.tbldata_IDDTServiceUtilization
SET Post365CostPerClient = Post365Cost/Post365ClientCnt

UPDATE dbo.tbldata_IDDTServiceUtilization
SET Post730CostPerClient = Post730Cost/Post730ClientCnt

UPDATE dbo.tbldata_IDDTServiceUtilization
SET Post1095CostPerClient = Post1095Cost/Post1095ClientCnt

-- Return values

SELECT * FROM dbo.tbldata_IDDTServiceUtilization
	


---------- Unresolved Possible Issues/Problems ---------------------
-- Below is a list of noted possible issues that have not been adressed by this code
--
-- multiple enrollements into an IDDT program. This code select the first effective date and does not
--		take gaps into consideration.  This should not really be much of an issue unless there are large gaps
--
-- same client enrolled into IDDT programs by multiple providers.  This issue is similar to previous one. The Affiliation code
--		only applies to the client level not the provider (that is why the above code has to use a support table to filter on participating
--		providers)
--
-- multiple Boards/consortiums.  This code is assuming a single Board structure. 
--
-- PCS data is not very amenable to generating costs given an arbitrary date range.  I have used movement date
--		as the criteria even though that means you may get some costs that belong in the previous period