Skip to content

Instantly share code, notes, and snippets.

@abmathewks

abmathewks/SQL_Script

Created October 6, 2024 19:36
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save abmathewks/ff91046cbc60f8ea14b83c66f19f4e7c to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save abmathewks/ff91046cbc60f8ea14b83c66f19f4e7c to your computer and use it in GitHub Desktop.
Download ZIP
Raw
SQL_Script
/*
#callQ: List of queued members with ZipCode and TimeZoneCode
*/
IF OBJECT_ID('[tempdb]..[#callQ]') IS NOT NULL
DROP TABLE #callQ;
SELECT TOP 50000
q.MemberAcesId
, ZipCode = c.Zipcode
, TimeZone = c.TimeZoneCode
INTO #callQ
FROM CIM.CallCenter.CallQueueMembers q
LEFT JOIN [CIM].[CallCenter].[CallMemberDetails] d ---- to get zip code
ON q.MemberAcesId = d.ACESId
LEFT JOIN [CIM].[Click].[ZipCodeStatisticalAreas] c ----- to get time zone
ON d.ZipCode = c.Zipcode
WHERE c.TimeZoneCode IN ('EST','CST','MST','PST') ------- excludes Alaska, Hawaii and Puerto Rico
----AND CallQueueStatusTypeId NOT IN (7,8) ------ Check if this is the right criterion
AND c.TimeZoneCode IS NOT NULL
ORDER BY q.MemberAcesId;
SELECT DISTINCT TimeZone FROM #callQ; ---- Check that all time zones are represented
SELECT * FROM #callQ;
IF OBJECT_ID('[tempdb]..[#TimeAdjZip]') IS NOT NULL
DROP TABLE #TimeAdjZip;
SELECT DISTINCT
p.ZipCode
, p.City
, AZ_UTC_Adj = -7
, Local_UTC_Adj = p.UTC_Adj
, p.DST
INTO #TimeAdjZip
FROM [PIA].[dbo].[TimeAdjByZipCodes] AS p WITH (NOLOCK);
SELECT TOP 100 * FROM #TimeAdjZip;
---WHERE dst =0;
---WHERE ZipCode IN (14221, 34203, 92683, 85258)
--SELECT TOP 10000 *
--FROM #callQ AS c
--LEFT JOIN #TimeAdjZip AS t
--ON c.ZipCode=t.ZipCode
--WHERE t.DST=0;
/*
#DST_history: DST changes between 2018 and 2020
*/
IF OBJECT_ID('[tempdb]..[#DST_history]') IS NOT NULL
DROP TABLE #DST_history;
SELECT
[Year]
,DST_Date = [Date]
,DST_Start = [DateTimeStamp Start]
,DST_End = [DateTimeStamp END]
,Time_Change = [Time Change]
, DST=1
INTO #DST_history
FROM [PIA].[dbo].[tblESTTimeAdjustments]
WHERE YEAR BETWEEN 2018 AND 2020;
SELECT * FROM #DST_history;
---- Example for ZipCode=85001 ----------
/*
SELECT *
FROM #TimeAdjZip
LEFT JOIN #DST_history
ON #DST_history.DST = #TimeAdjZip.DST
WHERE ZipCode=85001
ORDER BY #DST_history.DST;
*/
----
/*
#TimeSlot : time slots by time zone
SELECT
#TimeAdjZip.*
, #DST_history.[Time Change]
, AZ_To_Local = -(AZ_UTC_Adj-Local_UTC_Adj-DST)
FROM #TimeAdjZip
LEFT JOIN #DST_history
ON #DST_history.CurrentAZDate = #TimeAdjZip.CurrentAZDate
WHERE #DST_history.CurrentAZDate BETWEEN CAST([#DST_history].[DateTimeStamp Start] AS DATE) AND CAST ([#DST_history].[DateTimeStamp END] AS DATE)
AND ZipCode IN (14221, 34203, 92683, 85258)
*/
IF OBJECT_ID('[tempdb]..[#TimeSlot]') IS NOT NULL
DROP TABLE dbo.#TimeSlot
CREATE TABLE dbo.#TimeSlot
(
TimeZone VARCHAR(5) NOT NULL,
TimeSlotNum INT NOT NULL,
TimeSlotStart TIME NOT NULL,
TimeSlotEnd TIME NOT NULL
);
INSERT INTO #TimeSlot
(
TimeZone,
TimeSlotNum,
TimeSlotStart,
TimeSlotEnd
)
VALUES
('EST', '1', '8 am', '11 am'),
('EST', '2', '11 am', '2 pm'),
('EST', '3', '2 pm', '5 pm'),
('EST', '4', '5 pm', '8 pm'),
('CST', '1', '8 am', '11 am'),
('CST', '2', '11 am', '2 pm'),
('CST', '3', '2 pm', '5 pm'),
('CST', '4', '5 pm', '8 pm'),
('MST', '1', '8 am', '11 am'),
('MST', '2', '11 am', '2 pm'),
('MST', '3', '2 pm', '5 pm'),
('MST', '4', '5 pm', '8 pm'),
('PST', '1', '8 am', '11 am'),
('PST', '2', '11 am', '2 pm'),
('PST', '3', '2 pm', '5 pm'),
('PST', '4', '5 pm', '8 pm');
SELECT * FROM #TimeSlot;
-------
/*
#CallHistory_2019
Provides Call History of:
- Current year (2019) converting BookingDtTm to Local time accounting for DST of all members queued for a phone call
*/
IF OBJECT_ID('[tempdb]..[#CallHistory_2019]') IS NOT NULL
DROP TABLE dbo.#CallHistory_2019;
SELECT TOP 10000
cid.MemberAcesId
, ZipCode = c.Zipcode
, TimeZone = c.TimeZoneCode
, CASE
WHEN h.Time_Change='+1 hour (DST start)' THEN DATEADD(hour,-(t.AZ_UTC_Adj - t.Local_UTC_Adj - t.DST),cid.BookingDatetime)
ELSE DATEADD(hour,-(t.AZ_UTC_Adj - t.Local_UTC_Adj),cid.BookingDatetime)
END AS LocalDtTm
, Contact = r.IsContactRule
, Consent = r.IsSvisitRule
INTO #CallHistory_2019
FROM #callQ q
LEFT JOIN [CIM].[RPT].[vwRptCCACallInteractionDetail] cid
ON cid.MemberAcesId = q.MemberAcesId
LEFT JOIN [CIM].[RPT].[vwRptCCACallDispositionCountRules] r
ON cid.CallIntDisp=r.CallDispositionName
LEFT JOIN [CIM].[CallCenter].[CallMemberDetails] d
ON cid.MemberAcesId = d.ACESId
LEFT JOIN [CIM].[Click].[ZipCodeStatisticalAreas] c
ON d.ZipCode = c.Zipcode
AND c.TimeZoneCode IN ('EST','CST','MST','PST')
LEFT JOIN #TimeAdjZip t
ON t.ZipCode = c.Zipcode
LEFT JOIN #DST_history h
ON h.DST = t.DST
AND cid.BookingDatetime BETWEEN h.DST_Start AND h.DST_End
WHERE YEAR (cid.BookingDatetime)>= 2019 ---- change this to get a different period
AND c.TimeZoneCode IS NOT NULL
AND cid.CallIntDispDesc NOT IN -------- Excludes dials which should not be considered as contacts for our purposes
(
'Incomplete Visit', 'Member Rescheduled', 'Rescheduled', 'Completed Visit', 'Member Canceled',
'Provider Cancelled', 'Provider Rescheduled', 'Deceased'
,'Mailing', 'Canceled', 'Details Added' -- These 3 were added but were not in member inventory query
)
AND cid.CallTransactionType IN ( 'OutboundCall', 'OutboundPredictiveCall' ) ---- Only outbound calls
AND c.Zipcode IS NOT NULL
AND cid.ctMinutes > 0;
SELECT * FROM #CallHistory_2019
ORDER BY MemberAcesId, LocalDtTm;
/*
#ConsentHistory (it will be useful below to compute date of last consent)
Provides History of Consents for:
- Current and previous year (2018 and 2019) converting BookingDtTm to Local time accounting for DST of all members queued for a phone call
*/
IF OBJECT_ID('[tempdb]..[#ConsentHistory]') IS NOT NULL
DROP TABLE dbo.#ConsentHistory;
SELECT TOP 100000
cid.MemberAcesId
, ZipCode = c.Zipcode
, TimeZone = c.TimeZoneCode
, CASE
WHEN h.Time_Change='+1 hour (DST start)' THEN DATEADD(hour,-(t.AZ_UTC_Adj - t.Local_UTC_Adj - t.DST),cid.BookingDatetime)
ELSE DATEADD(hour,-(t.AZ_UTC_Adj - t.Local_UTC_Adj),cid.BookingDatetime)
END AS LocalDtTm
INTO #ConsentHistory
FROM #callQ q
LEFT JOIN [CIM].[RPT].[vwRptCCACallInteractionDetail] cid
ON cid.MemberAcesId = q.MemberAcesId
LEFT JOIN [CIM].[RPT].[vwRptCCACallDispositionCountRules] r
ON cid.CallIntDisp=r.CallDispositionName
LEFT JOIN [CIM].[CallCenter].[CallMemberDetails] d
ON cid.MemberAcesId = d.ACESId
LEFT JOIN [CIM].[Click].[ZipCodeStatisticalAreas] c
ON d.ZipCode = c.Zipcode
AND c.TimeZoneCode IN ('EST','CST','MST','PST')
LEFT JOIN #TimeAdjZip t
ON t.ZipCode = c.Zipcode
LEFT JOIN #DST_history h
ON h.DST = t.DST
AND cid.BookingDatetime BETWEEN h.DST_Start AND h.DST_End
WHERE YEAR (cid.BookingDatetime)>= 2018 ---- change this to get a different period
AND r.IsSvisitRule=1
AND c.TimeZoneCode IS NOT NULL
AND cid.CallIntDispDesc NOT IN -------- Excludes dials which should not be considered as contacts for our purposes
(
'Incomplete Visit', 'Member Rescheduled', 'Rescheduled', 'Completed Visit', 'Member Canceled',
'Provider Cancelled', 'Provider Rescheduled', 'Deceased'
,'Mailing', 'Canceled', 'Details Added' -- These 3 were added but were not in member inventory query
)
AND cid.CallTransactionType IN ( 'OutboundCall', 'OutboundPredictiveCall' ) ---- Only outbound calls
AND c.Zipcode IS NOT NULL
AND cid.ctMinutes > 0;
SELECT * FROM #ConsentHistory
ORDER BY MemberAcesId, LocalDtTm;
/*
#ConsentHistoryTimeSlot
Consent History including Time Slot
*/
IF OBJECT_ID('[tempdb]..[#ConsentHistoryTimeSlot]') IS NOT NULL
DROP TABLE dbo.#ConsentHistoryTimeSlot;
SELECT
ch.MemberAcesId
, ch.TimeZone
, ch.LocalDtTm
, s.TimeSlotNum
INTO #ConsentHistoryTimeSlot
FROM #ConsentHistory ch
LEFT JOIN #TimeSlot s
ON ch.TimeZone=s.TimeZone
AND CAST (ch.LocalDtTm AS TIME) BETWEEN s.TimeSlotStart AND s.TimeSlotEnd;
SELECT * FROM #ConsentHistoryTimeSlot;
/*
#lastConsent : Date and time slot in which last consent took place IN local time
going as far back as the beginning of the previous calendar year
*/
IF OBJECT_ID('[tempdb]..[#lastConsent]') IS NOT NULL
DROP TABLE #lastConsent;
SELECT
ct.MemberAcesId
, ct.TimeZone
, LastConsentDtTm = MAX(ct.LocalDtTm)
, ct.TimeSlotNum
INTO #lastConsent
FROM #ConsentHistoryTimeSlot ct
GROUP BY ct.MemberAcesId, ct.TimeZone, ct.TimeSlotNum;
SELECT * FROM #lastConsent;
---SELECT * FROM #CallHistory;
/*
#CallHistory of
- Current and previous year (2018 and 2019) converting BookingDtTm to Local time accounting for DST of all members queued for a phone call
*/
IF OBJECT_ID('[tempdb]..[#CallHistory]') IS NOT NULL
DROP TABLE dbo.#CallHistory;
SELECT TOP 10000
cid.MemberAcesId
, ZipCode = c.Zipcode
, TimeZone = c.TimeZoneCode
, CASE
WHEN h.Time_Change='+1 hour (DST start)' THEN DATEADD(hour,-(t.AZ_UTC_Adj - t.Local_UTC_Adj - t.DST),cid.BookingDatetime)
ELSE DATEADD(hour,-(t.AZ_UTC_Adj - t.Local_UTC_Adj),cid.BookingDatetime)
END AS LocalDtTm
INTO #CallHistory
FROM #callQ q
LEFT JOIN [CIM].[RPT].[vwRptCCACallInteractionDetail] cid
ON cid.MemberAcesId = q.MemberAcesId
LEFT JOIN [CIM].[RPT].[vwRptCCACallDispositionCountRules] r
ON cid.CallIntDisp=r.CallDispositionName
LEFT JOIN [CIM].[CallCenter].[CallMemberDetails] d
ON cid.MemberAcesId = d.ACESId
LEFT JOIN [CIM].[Click].[ZipCodeStatisticalAreas] c
ON d.ZipCode = c.Zipcode
AND c.TimeZoneCode IN ('EST','CST','MST','PST')
LEFT JOIN #TimeAdjZip t
ON t.ZipCode = c.Zipcode
LEFT JOIN #DST_history h
ON h.DST = t.DST
AND cid.BookingDatetime BETWEEN h.DST_Start AND h.DST_End
WHERE cid.BookingDatetime >= '1/1/' + CONVERT(VARCHAR(4), YEAR(GETDATE()) - 1) ---- gets all obs since jan 1st of previous calendar year
AND c.TimeZoneCode IS NOT NULL
AND cid.CallIntDispDesc NOT IN -------- Excludes dials which should not be considered as contacts for our purposes
(
'Incomplete Visit', 'Member Rescheduled', 'Rescheduled', 'Completed Visit', 'Member Canceled',
'Provider Cancelled', 'Provider Rescheduled', 'Deceased'
,'Mailing', 'Canceled', 'Details Added' -- These 3 were added but were not in member inventory query
)
AND cid.CallTransactionType IN ( 'OutboundCall', 'OutboundPredictiveCall' ) ---- Only outbound calls
AND c.Zipcode IS NOT NULL
AND cid.ctMinutes > 0;
SELECT * FROM #CallHistory;
/*
#CallHistoryTSlot: call history with time slot
*/
IF OBJECT_ID('[tempdb]..[#CallHistoryTSlot]') IS NOT NULL
DROP TABLE dbo.#CallHistoryTSlot;
SELECT
ch.*
, s.TimeSlotNum
INTO #CallHistoryTSlot
FROM #CallHistory ch
LEFT JOIN #TimeSlot s
ON s.TimeZone = ch.TimeZone
AND CAST (ch.LocalDtTm AS TIME) BETWEEN s.TimeSlotStart AND s.TimeSlotEnd;
SELECT * FROM #CallHistoryTSlot;
--SELECT * FROM #ConsentHistoryTimeSlot;
/*
#LastConsentSlotCount
Number of times Matrix has called a member at the time slot of his or her last consent
*/
IF OBJECT_ID('[tempdb]..[#LastConsentSlotCount]') IS NOT NULL
DROP TABLE dbo.#LastConsentSlotCount;
SELECT
hts.MemberAcesId, hts.TimeSlotNum, COUNT(*) AS LastConsentSlotCount
INTO #LastConsentSlotCount
FROM #CallHistoryTSlot hts --- call history per member and time slot since Jan 1st of previous calendar year
INNER JOIN #ConsentHistoryTimeSlot cts --- time slot of last consent
ON hts.MemberAcesId=cts.MemberAcesId
AND cts.TimeSlotNum = hts.TimeSlotNum
GROUP BY hts.MemberAcesId, hts.TimeSlotNum;
SELECT * FROM #LastConsentSlotCount;
/*
#CallHistory_TSlot2019
Call History of current year including time slot
*/
IF OBJECT_ID('[tempdb]..[#CallHistoryTSlot_2019]') IS NOT NULL
DROP TABLE dbo.#CallHistoryTSlot_2019;
SELECT
ch.*
, s.TimeSlotNum
INTO #CallHistoryTSlot_2019
FROM #CallHistory_2019 ch
LEFT JOIN #TimeSlot s
ON s.TimeZone = ch.TimeZone
AND CAST (ch.LocalDtTm AS TIME) BETWEEN s.TimeSlotStart AND s.TimeSlotEnd;
SELECT * FROM #CallHistoryTSlot_2019;
/*
#memTotalCount: Number of attempts per member in 2019
*/
IF OBJECT_ID('[tempdb]..[#memTotalCount]') IS NOT NULL
DROP TABLE #memTotalCount;
SELECT
MemberAcesId
, TotalAttempts = COUNT(*)
INTO #memTotalCount
FROM #CallHistoryTSlot_2019
GROUP BY MemberAcesId;
SELECT * FROM #memTotalCount;
/*
#memSlotCount: Number of attempts per member per time slot in 2019
*/
IF OBJECT_ID('[tempdb]..[#memSlotCount]') IS NOT NULL
DROP TABLE #memSlotCount;
SELECT MemberAcesId,
TimeSlotNum,
SlotAttempts = COUNT(*)
INTO #memSlotCount
FROM #CallHistoryTSlot_2019
GROUP BY MemberAcesId, TimeSlotNum;
SELECT * FROM #memSlotCount;
/*
#memZ: Z scores per member in 2019
*/
IF OBJECT_ID('[tempdb]..[#memZ]') IS NOT NULL
DROP TABLE #memZ;
SELECT
msc.MemberAcesId
, msc.TimeSlotNum
, msc.SlotAttempts
, mtc.TotalAttempts
, z = 1.0 * msc.SlotAttempts/ mtc.TotalAttempts
INTO #memZ
FROM #memTotalCount mtc
LEFT JOIN #memSlotCount msc
ON msc.MemberAcesId = mtc.MemberAcesId
ORDER BY msc.MemberAcesId, z;
SELECT * FROM #memZ;
/*
#memZRank: Ranks members in descending order according to their Z scores for 2019
*/
IF OBJECT_ID('[tempdb]..[#memZRank]') IS NOT NULL
DROP TABLE dbo.#memZRank;
SELECT z.MemberAcesId
, c.TimeZoneCode
, z.TimeSlotNum
, z.SlotAttempts
, z.TotalAttempts
, ROW_NUMBER() OVER (PARTITION BY MemberAcesId ORDER BY z DESC) AS [Slot_Rank]
INTO #memZRank
FROM #memZ z
LEFT JOIN [CIM].[CallCenter].[CallMemberDetails] d
ON z.MemberAcesId = d.ACESId
LEFT JOIN [CIM].[Click].[ZipCodeStatisticalAreas] c
ON d.ZipCode = c.Zipcode
--AND c.TimeZoneCode IN ('EST','CST','MST','PST')
ORDER BY MemberAcesId, TimeSlotNum;
SELECT * FROM #memZRank;
/*
Generate a rank of the contact rate of each time zone BY TIME SLOT from highest to lowest
for the current year
#ContactTimeZone_2019
*/
IF OBJECT_ID('[tempdb]..[#ContactTimeZone_2019]') IS NOT NULL
DROP TABLE dbo.#ContactTimeZone_2019;
SELECT
TimeZone
, TimeSlotNum
--- , Contact_Count = SUM(Contact)
, Dial_Count = Count (Contact)
, Contact_Rate = SUM(Contact) * 1.0 / COUNT(Contact)
INTO #ContactTimeZone_2019
FROM #CallHistoryTSlot_2019
where TimeSlotNum IS NOT null
GROUP BY TimeZone, TimeSlotNum
ORDER BY Contact_Rate DESC;
SELECT * FROM #ContactTimeZone_2019;
/*
#RankNoHistory
*/
IF OBJECT_ID('[tempdb]..[#RankNoHistory]') IS NOT NULL
DROP TABLE dbo.#RankNoHistory;
SELECT
ctz.TimeZone
, ctz.TimeSlotNum
, ctz.Contact_Rate
, ROW_NUMBER() OVER (PARTITION BY ctz.TimeZone ORDER BY Contact_Rate DESC) AS [Slot_Rank]
INTO #RankNoHistory
FROM #ContactTimeZone_2019 ctz;
------
SELECT * FROM #RankNoHistory
ORDER BY TimeZone;
/*
#memRankNoHistory : Ranking of members with no call history
*/
IF OBJECT_ID('[tempdb]..[#memRankNoHistory]') IS NOT NULL
DROP TABLE dbo.#memRankNoHistory;
SELECT
q.MemberAcesId
, q.TimeZone
, rnh.TimeSlotNum
, rnh.Slot_Rank
, algorithmType = 1
INTO #memRankNoHistory
FROM #callQ q
LEFT JOIN #memZ z
ON q.MemberAcesId=z.MemberAcesId
LEFT JOIN #RankNoHistory rnh
ON q.TimeZone=rnh.TimeZone
WHERE z.MemberAcesId IS NULL ------ Gets us the members with no call history this year
ORDER BY q.MemberAcesId;
SELECT * FROM #memRankNoHistory;
/*
#memRankNoConsent : Ranking of members with call history but no consent
*/
IF OBJECT_ID('[tempdb]..[#memRankNoConsent]') IS NOT NULL
DROP TABLE dbo.#memRankNoConsent;
SELECT
z.MemberAcesId
, zr.TimeZoneCode
, zr.TimeSlotNum
, zr.Slot_Rank
, algorithmType = 2
INTO #memRankNoConsent
FROM #memZ z
LEFT JOIN #LastConsentSlotCount cc
ON z.MemberAcesId=cc.MemberAcesId
LEFT JOIN #memZRank zr
ON z.MemberAcesId=zr.MemberAcesId
AND z.TimeSlotNum=zr.TimeSlotNum
WHERE cc.MemberAcesId IS NULL ---- Gets us the members with no previous consent
ORDER BY z.MemberAcesId;
SELECT * FROM #memRankNoConsent;
--SELECT * FROM #memZ;
--SELECT * FROM #LastConsentSlotCount;
--SELECT * FROM #memZRank;
/*
#memRankConsentTypeI: members with previous consent but with 4 or more dials at the time slot of their last consent
*/
IF OBJECT_ID('[tempdb]..[#memRankConsentTypeI]') IS NOT NULL
DROP TABLE dbo.#memRankConsentTypeI;
SELECT
z.MemberAcesId
, zr.TimeZoneCode
, zr.TimeSlotNum
, zr.Slot_Rank
, algorithmType = 3
INTO #memRankConsentTypeI
FROM #memZ z
LEFT JOIN #LastConsentSlotCount cc
ON z.MemberAcesId=cc.MemberAcesId
LEFT JOIN #memZRank zr
ON z.MemberAcesId=zr.MemberAcesId
AND z.TimeSlotNum=zr.TimeSlotNum
WHERE cc.LastConsentSlotCount >= 4 ---- Gets us the members with previous consent but with 4 or more dials at the time slot of their last consent
ORDER BY z.MemberAcesId;
SELECT * FROM #memRankConsentTypeI;
/*
#memRankConsentTypeII: members with previous consent but less than 4 dials at the time slot of their last consent
*/
IF OBJECT_ID('[tempdb]..[#memRankConsentTypeII]') IS NOT NULL
DROP TABLE dbo.#memRankConsentTypeII;
SELECT
sq.MemberAcesId
, sq.TimeZoneCode
, sq.TimeSlotNum
, ROW_NUMBER() OVER (PARTITION BY sq.MemberAcesId ORDER BY sq.MemberAcesId, sq.First DESC, sq.z_Rank) AS [Slot_Rank]
, algorithmType = 4
INTO #memRankConsentTypeII
FROM
(SELECT TOP 100 PERCENT
z.MemberAcesId
, zr.TimeZoneCode
, zr.TimeSlotNum
, LastConsentSlot = cc.TimeSlotNum
, z_Rank = zr.Slot_Rank
, CASE WHEN z.TimeSlotNum=cc.TimeSlotNum THEN 1
ELSE 0
END AS First
FROM #memZ z
LEFT JOIN #LastConsentSlotCount cc
ON z.MemberAcesId=cc.MemberAcesId
LEFT JOIN #memZRank zr
ON z.MemberAcesId=zr.MemberAcesId
AND z.TimeSlotNum=zr.TimeSlotNum
WHERE cc.LastConsentSlotCount < 4
ORDER BY z.MemberAcesId, first DESC, z_Rank) AS sq
ORDER BY sq.MemberAcesId;
SELECT * FROM #memRankConsentTypeII;
--
SELECT * FROM #memRankNoHistory;
SELECT * FROM #memRankNoConsent;
SELECT * FROM #memRankConsentTypeI;
SELECT * FROM #memRankConsentTypeII;
/*
Final Table: #DataPreparation
*/
IF OBJECT_ID('[tempdb]..[#DataPreparation]') IS NOT NULL
DROP TABLE dbo.#DataPreparation;
SELECT *
INTO #DataPreparation
FROM #memRankNoHistory
UNION
SELECT *
FROM #memRankNoConsent
UNION
SELECT *
FROM #memRankConsentTypeI
UNION
SELECT *
FROM #memRankConsentTypeII
ORDER BY MemberAcesId, algorithmType;
SELECT * FROM #DataPreparation; ----- Final Output
---- Checks below
SELECT * FROM #DataPreparation
WHERE algorithmType=1;
SELECT * FROM #DataPreparation
WHERE algorithmType=2;
SELECT * FROM #DataPreparation
WHERE algorithmType=3;
SELECT * FROM #DataPreparation
WHERE algorithmType=4;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment