SQLQuery7.sql

--/**** PHASE 1:  creations des tables *******/

--IF OBJECT_ID(N'[dbo].[FactTable]') IS NOT NULL
--	DROP TABLE [dbo].[FactTable]

--IF OBJECT_ID(N'[dbo].[dimPatient]') IS NOT NULL
--	DROP TABLE [dbo].[dimPatient]

--IF OBJECT_ID(N'[dbo].[dimPhysician]') IS NOT NULL
--	DROP TABLE [dbo].[dimPhysician]

--set DATEFORMAT dmy;

--IF OBJECT_ID(N'[dbo].[dimDatePost]') IS NOT NULL
--	DROP TABLE [dbo].[dimDatePost]

--IF OBJECT_ID(N'[dbo].[dimCPTCode]') IS NOT NULL
--	DROP TABLE [dbo].[dimCPTCode]

--IF OBJECT_ID(N'[dbo].[dimTransaction]') IS NOT NULL
--	DROP TABLE [dbo].[dimTransaction]

--IF OBJECT_ID(N'[dbo].[dimLocation]') IS NOT NULL
--	DROP TABLE [dbo].[dimLocation]

--IF OBJECT_ID(N'[dbo].[dimDiagnosisCode]') IS NOT NULL
--	DROP TABLE [dbo].[dimDiagnosisCode]

--IF OBJECT_ID(N'[dbo].[dimPayer]') IS NOT NULL
--	DROP TABLE [dbo].[dimPayer]

--create table [dbo].[FactTable](
--	[FactTablePK] VARCHAR(255) NOT NULL PRIMARY KEY,
--	[dimPatientPK] VARCHAR(255) NOT NULL,
--	[dimPhysicianPK] VARCHAR(255) NOT NULL,
--	[dimDatePostPK] VARCHAR(255) NOT NULL,
--	[dimCPTCodePK] VARCHAR(255) NOT NULL,
--	[dimPayerPK] VARCHAR(255) NOT NULL,
--	[dimTransactionPK] VARCHAR(255) NOT NULL,
--	[dimLocationPK] VARCHAR(255) NOT NULL,
--	[PatientNumber] VARCHAR(255) NOT NULL,
--	[dimDiagnosisCodePK] VARCHAR(255) NOT NULL,
--	[CPTUnits] DECIMAL(12,2) NULL DEFAULT 0,
--	[GrossCharge] DECIMAL(12,2) NULL DEFAULT 0,
--	[Payment] DECIMAL(12,2) NULL DEFAULT 0,
--	[Adjustment] DECIMAL(12,2) NULL DEFAULT 0,
--	[AR] DECIMAL(12,2) NULL DEFAULT 0
--)

--CREATE TABLE [dbo].[dimPatient](
--	[dimPatientPK] VARCHAR(255) NOT NULL PRIMARY KEY,
--	[PatientNumber] VARCHAR(50) NULL, 
--	[FirstName] VARCHAR(255) NULL,
--	[LastName] VARCHAR(255) NULL,
--	[Email] VARCHAR(255) NULL,
--	[PatientGender] VARCHAR(50) NULL,
--	[PatientAge] VARCHAR(25) NULL,
--	[City] VARCHAR(125) NULL,
--	[State] VARCHAR(50)
--)

--CREATE TABLE [dbo].[dimPhysician](
--	[dimPhysicianPK] VARCHAR(255) NOT NULL PRIMARY KEY,
--	[ProviderNpi] VARCHAR(25) NULL,
--	[ProviderName] VARCHAR(125) NULL,
--	[ProviderSpecialty] VARCHAR(50),
--	[ProviderFTE] DECIMAL(5,2)
--)

--CREATE TABLE [dbo].[dimDatePost](
--	[dimDatePostPK] VARCHAR(255) NOT NULL PRIMARY KEY,
--	[Date] VARCHAR(50) NULL,
--	[Year] VARCHAR(10) NULL,
--	[Month] VARCHAR(125) NULL,
--	[MonthPeriod] VARCHAR(255) NULL,
--	[MonthYear] VARCHAR(125) NULL,
--	[DAY] VARCHAR(25) NULL,
--	[DAYNAME] VARCHAR(125)
--)

--CREATE TABLE [dbo].[dimCPTCode](
--	[dimCPTCodePK] VARCHAR(255) NOT NULL PRIMARY KEY,
--	[CptCode] VARCHAR(255) NULL,
--	[CptDesc] VARCHAR(255) NULL,
--	[CptGrouping] VARCHAR(125)
--)

--CREATE TABLE [dbo].[dimTransaction](
--	[dimTransactionPK] VARCHAR(255) NOT NULL PRIMARY KEY,
--	[TransactionType] VARCHAR(125) NULL,
--	[Transaction] VARCHAR(512) NULL,
--	[AdjustmentReason] VARCHAR(125) NULL
--)

--CREATE TABLE [dbo].[dimLocation](
--	[dimLocationPK] VARCHAR(255) NOT NULL PRIMARY KEY,
--	[LocationName] VARCHAR(125) NULL
--)

--CREATE TABLE [dbo].[dimDiagnosisCode](
--	[dimDiagnosisCodePK] VARCHAR(255) NOT NULL PRIMARY KEY,
--	[DiagnosisCode] VARCHAR(125) NULL,
--	[DiagnosisCodeDescription] VARCHAR(255) NULL,
--	[DiagnosisCodeGroup] VARCHAR (255) NULL
--)

--CREATE TABLE [dbo].[dimPayer](
--	[dimPayerPK] VARCHAR(255) NOT NULL PRIMARY KEY,
--	[PayerName] VARCHAR(125) NULL
--)


--/***** PHASE 2: Verifications de l'insertion *****/
--select * from dbo.FactTable;

/****** PHASE 3: AJOUT DE CONTRAINTES******/

--ALTER TABLE [dbo].[FactTable] 
--ADD 
--CONSTRAINT FK_Patient FOREIGN KEY (dimPatientPK) REFERENCES dbo.dimPatient (dimPatientPK),
--CONSTRAINT FK_Physician FOREIGN KEY (dimPhysicianPK) REFERENCES dbo.dimPhysician (dimPhysicianPK),
--CONSTRAINT FK_DatePost FOREIGN KEY (dimDatePostPK) REFERENCES dbo.dimDatePost (dimDatePostPK),
--CONSTRAINT FK_CPTCode FOREIGN KEY (dimCPTCodePK) REFERENCES dbo.dimCPTCode (dimCPTCodePK),
--CONSTRAINT FK_Payer FOREIGN KEY (dimPayerPK) REFERENCES dbo.dimPayer (dimPayerPK),
--CONSTRAINT FK_Transation FOREIGN KEY (dimTransactionPK) REFERENCES dbo.dimTransaction (dimTransactionPK),
--CONSTRAINT FK_Location FOREIGN KEY (dimLocationPK) REFERENCES dbo.dimLocation (dimLocationPK),
--CONSTRAINT FK_DiagnosisCode FOREIGN KEY (dimDiagnosisCodePK) REFERENCES dbo.dimDiagnosisCode (dimDiagnosisCodePK);

/******** PHASE 4: Explorations des donnés  ********/

--select * from dbo.FactTable;


/*
what's the meaning of cptunits /grosscharge /AR /Adjustments
*/

--select * from dbo.dimDiagnosisCode order by diagnosisCode;

--select * from dbo.dimTransaction order by [Transaction]; -- why transactions have dupes, is it meant to be a measure or dimension


/*
important columns to analyze

- measure columns in FactTable
- dimPhysician (ProviderSpecialty <=> ProviderFTE)
*/



/********** PHASE 5: Joins to gather data **********/

--- full info about the patient:

--select
--	facttablepk,firstname,lastname,email,patientgender,patientage,city,state
--from 
--	dbo.FactTable
--join 
--	dbo.dimPatient
--on dbo.FactTable.dimPatientPK = dbo.dimPatient.dimPatientPK;

---- localisation & date of service

--SELECT 
--	facttablepk,dbo.dimdatepost.dimdatepostpk,LocationName,Date
--FROM 
--	DBO.FACTTABLE
--INNER JOIN 
--	DBO.DIMLOCATION
--ON 
--	DBO.FACTTABLE.DIMLOCATIONPK = DBO.DIMLOCATION.DIMLOCATIONPK
--INNER JOIN 
--	DBO.DIMDATEPOST
--ON
--	DBO.FACTTABLE.DIMDATEPOSTPK = DBO.DIMDATEPOST.DIMDATEPOSTPK;

---- info about physicians & charge type

--select facttablepk,providername,providerspecialty,providerfte,CPTUnits,GrossCharge,Payment,Adjustment,AR,transactiontype
--from dbo.facttable
--inner join dbo.dimphysician 
--on dbo.facttable.dimphysicianpk = dbo.dimphysician.dimphysicianpk
--inner join dbo.dimtransaction
--on dbo.facttable.dimtransactionpk = dbo.dimtransaction.dimtransactionpk;


---- code de diagnostics & CPT

--select facttablepk,cptcode,cptdesc,diagnosiscode,diagnosiscodedescription
--from dbo.facttable
--inner join dbo.dimcptcode
--on dbo.facttable.dimcptcodepk = dbo.dimcptcode.dimcptcodepk
--inner join dbo.dimdiagnosiscode
--on dbo.facttable.dimdiagnosiscodepk = dbo.dimdiagnosiscode.dimdiagnosiscodepk;

----- transaction, payment & adjustments

--select facttablepk,payment,adjustment,transactiontype,[transaction]
--from dbo.facttable
--inner join dbo.dimtransaction 
--on dbo.facttable.dimtransactionpk = dbo.dimtransaction.dimtransactionpk;

/******** PHASE 6: 10 SQL QUERIES **********/

-- requete 1 : nombre de ligne ou la charge brut est supérieur à 100$
--select count(facttablepk) from dbo.FactTable where grosscharge >= 100;

-- requete 2: Nombre de patients uniques

--select count(distinct patientnumber) from dbo.dimpatient;

-- requete 3: Nombre de codes CPT par groupe

--select count(CPTCode) as countCPTCode,CptGrouping from dimCPTCode group by CptGrouping;
---- verifcation : check number of lines
--select * from dimcptcode where cptgrouping like N'Nursing%'; -- verify count for a group to check the query above is correct 

-- requete 4: Médecins ayant soumis une réclamation Medicare

--select
--	providername,providernpi,providerspecialty,providerfte
--from 
--	dimphysician
--where
--	dimphysicianpk in
--(select 
--	distinct dimphysicianpk
--from 
--	facttable 
--inner join 
--	dimpayer 
--on facttable.dimpayerpk=dimpayer.dimpayerpk
--where
--	payername=N'medicare');

--requete 5: code cpt with more than 100 unit

--select 
--	cptcode,cptdesc
--from 
--	facttable 
--inner join 
--	dimcptcode
--on
--	facttable.dimcptcodepk=dimcptcode.dimcptcodepk
--where cptunits >=100 ;

-- requete 6: Spécialité médicale ayant reçu le plus de paiements

--select ProviderSpecialty,MonthYear from FactTable
--inner join dimDatePost 
--on FactTable.dimDatePostPK=dimDatePost.dimDatePostPK 
--inner join dimPhysician
--on FactTable.dimPhysicianPK=dimPhysician.dimPhysicianPK
--where ProviderSpecialty = (
--	select top 1 ProviderSpecialty from FactTable
--	inner join dimPhysician 
--	on FactTable.dimPhysicianPK=dimPhysician.dimPhysicianPK
--	group by ProviderSpecialty
--	order by -sum(Payment) desc
--);

-- requete 7: Unités CPT pour diagnostics commençant par J

select count(distinct f.CPTUnits) AS CPTNumber 
from FactTable f
join dimDiagnosisCode d
on d.dimDiagnosisCodePK = f.dimDiagnosisCodePK
where d.DiagnosisCode LIKE 'J%'

-- requete 8:Rapport démographique des patients

SELECT 
    FirstName + ' ' + LastName AS FullName,
    Email,
    PatientAge AS Age,
    City,
    State,
    CASE 
        WHEN PatientAge < 18 THEN '-18'
        WHEN PatientAge BETWEEN 18 AND 65 THEN '18~65'
        ELSE '65+'
    END AS AgeGroup
FROM dimPatient
ORDER BY PatientAge DESC;
--requete 9:  Last one
select 
    p.patientnumber,
    ph.providername,
    dt.date,
    l.locationname,
    t.transactiontype,
    f.grosscharge,
    f.payment,
    f.adjustment,
    f.ar
from facttable f
join dimpatient p 
    on f.dimpatientpk = p.dimpatientpk
join dimphysician ph
    on f.dimphysicianpk = ph.dimphysicianpk
join dimtransaction t
    on f.dimtransactionpk = t.dimtransactionpk
join dimlocation l
    on f.dimlocationpk = l.dimlocationpk
join dimdatepost dt
    on f.dimDatePostPK=dt.dimdatepostpk
where p.patientnumber = '21385921'
order by dt.date;