· 10 years ago · Sep 27, 2016, 09:42 PM
1/* $header
2================================================================
3 ©2016. Tenet HealthSystem Medical, Inc. All rights reserved.
4================================================================
5$author ryan.heberling
6$created 08/31/2016
7$peer_reviewed last.first ( 00/00/2016 )
8$qa_performed last.first ( 00/00/2016 )
9$sales_force SF#51784
10$description This script copies all cqm measures from a specified provider to all other providers.
11================================================================
12$mod date name SF#xxxx description
13$mod date name SF#xxxx description
14*/
15--**Backup Tables--------------------------------------------------------------------------------------------------------------
16--Prod
17--/*
18EXEC ten_Backup 'cqm_provider_measure_xref'
19 , '51784 - Copy CQM Measures'
20 , '';
21
22--*/
23--Test
24/*
25select * into cqm_provider_measure_xref_08312016 from cqm_provider_measure_xref ;
26
27Select * from cqm_provider_measure_xref_08312016;
28*/
29--**---------------------------------------------------------------------------------------------------------------------------
30DECLARE @pi_source_provider_id UNIQUEIDENTIFIER
31 --, @pi_target_provider_id UNIQUEIDENTIFIER
32 , @pi_user_id INT
33 , @po_result_msg VARCHAR(50)
34 , @next_id INT
35
36----------------------------------------------------------------------------
37----------------************** Set Parameters **************----------------
38---->** Source Provider -- edit first and last name, run query, and confirm that only 1 provider_id displays
39SET @pi_source_provider_id = (
40 SELECT provider_id
41 FROM provider_mstr
42 WHERE last_name LIKE '%mcpherson%'
43 AND first_name LIKE '%milton%'
44 AND delete_ind <> 'Y'
45 );
46---->** Created by and Modified by User -- edit first and last name, run query, and confirm that only 1 provider_id displays
47SET @pi_user_id = (
48 SELECT user_id
49 FROM user_mstr
50 WHERE last_name LIKE '%heberling%'
51 AND first_name LIKE '%ryan%'
52 );
53----------------************** ************** **************----------------
54----------------------------------------------------------------------------
55--** Find next provider_measure_id **---------------
56SET @next_id = (
57 SELECT MAX(provider_measure_id)
58 FROM cqm_provider_measure_xref
59 );--select @next_id; --38195
60
61----------------------------------------------------------------------------
62--** Only run if source provider is configured
63IF EXISTS (
64 SELECT 1
65 FROM cqm_provider_measure_xref
66 WHERE provider_id = @pi_source_provider_id
67 )
68BEGIN
69 ----------------------------------------------------------------------------
70 --** Drop temp tables if they exist
71 IF OBJECT_ID('tempdb..#temp_provider_list') IS NOT NULL
72 DROP TABLE #temp_provider_list;
73
74 IF OBJECT_ID('tempdb..#temp_cqm_final_list') IS NOT NULL
75 DROP TABLE #temp_cqm_final_list;
76
77 IF OBJECT_ID('tempdb..#temp_cqm_init_list') IS NOT NULL
78 DROP TABLE #temp_cqm_init_list;
79
80 ----------------------------------------------------------------------------
81 --** Build provider list
82 SELECT DISTINCT pm.provider_id
83 INTO #temp_provider_list
84 FROM provider_mstr pm
85 INNER JOIN provider_practice_mstr ppm
86 ON ppm.provider_id = pm.provider_id
87 AND ppm.delete_ind <> 'Y'
88 AND ppm.attending_ind = 'Y'
89 AND pm.delete_IND <> 'Y'
90 INNER JOIN practice pr
91 ON ppm.practice_id = pr.practice_id
92 AND pr.delete_ind <> 'Y';--3887
93 ----------------------------------------------------------------------------
94 --** Create cqm initial list from source provider
95
96 CREATE TABLE #temp_cqm_init_list (
97 provider_measure_id INT
98 , provider_id UNIQUEIDENTIFIER
99 , measure_id INT
100 , created_by INT
101 , modified_by INT
102 , create_timestamp DATETIME
103 , modify_timestamp DATETIME
104 , measure_system VARCHAR(25)
105 );
106
107 INSERT INTO #temp_cqm_init_list (
108 provider_measure_id
109 , provider_id
110 , measure_id
111 , created_by
112 , modified_by
113 , create_timestamp
114 , modify_timestamp
115 , measure_system
116 )
117 SELECT provider_measure_id
118 , provider_id
119 , measure_id
120 , created_by
121 , modified_by
122 , create_timestamp
123 , modify_timestamp
124 , measure_system
125 FROM cqm_provider_measure_xref
126 WHERE provider_id = @pi_source_provider_id;
127
128 ----------------------------------------------------------------------------
129 --** Insert initial listing into final list, using a cross join,
130 --which duplicates all recours from source provider, for all providers in provider listing
131 --/*
132 SELECT 1 AS 'provider_measure_id'
133 , pm.provider_id AS 'provider_id'
134 , tcl.measure_id AS 'measure_id'
135 , @pi_user_id AS 'created_by'
136 , @pi_user_id AS 'modified_by'
137 , getdate() AS 'create_timestamp'
138 , getdate() AS 'modify_timestamp'
139 , tcl.measure_system AS 'measure_system'
140 INTO #temp_cqm_final_list
141 FROM #temp_cqm_init_list tcl
142 , #temp_provider_list pm
143 WHERE pm.provider_id <> @pi_source_provider_id;
144
145 ----------------------------------------------------------------------------
146 --** Remove any records from final listing that would be duplicates
147 --**DEBUG --select * from
148 DELETE tfl
149 FROM #temp_cqm_final_list tfl
150 INNER JOIN cqm_provider_measure_xref cpm
151 ON cpm.provider_id = tfl.provider_id
152 AND cpm.measure_id = tfl.measure_id;
153
154 ----------------------------------------------------------------------------
155 --** Insert final listing into cqm_provider_measure_xref, with new sequential provider_measure_id's
156 --/*
157 INSERT INTO cqm_provider_measure_xref (
158 provider_measure_id
159 , provider_id
160 , measure_id
161 , created_by
162 , modified_by
163 , create_timestamp
164 , modify_timestamp
165 , measure_system
166 )
167 SELECT DISTINCT ROW_NUMBER() OVER (
168 ORDER BY convert(VARCHAR(36), tfl.provider_id)
169 , tfl.measure_id
170 ) + @next_id AS 'provider_measure_id'
171 , provider_id
172 , measure_id
173 , created_by
174 , modified_by
175 , create_timestamp
176 , modify_timestamp
177 , measure_system
178 FROM #temp_cqm_final_list tfl;
179
180 --*/
181 IF OBJECT_ID('tempdb..#temp_cqm_init_list') IS NOT NULL
182 DROP TABLE #temp_cqm_init_list;
183
184 IF OBJECT_ID('tempdb..#temp_provider_list') IS NOT NULL
185 DROP TABLE #temp_provider_list;
186
187 IF OBJECT_ID('tempdb..#temp_cqm_final_list') IS NOT NULL
188 DROP TABLE #temp_cqm_final_list;
189END
190 ----------------------------------------------------------------------------
191 --** RESTORE SCRIPT
192 /*
193 truncate table cqm_provider_measure_xref;
194
195 INSERT INTO cqm_provider_measure_xref (
196 provider_measure_id
197 , measure_id
198 , provider_id
199 , created_by
200 , modified_by
201 , create_timestamp
202 , modify_timestamp
203 --,row_timestamp
204 , measure_system
205 )
206 SELECT
207 provider_measure_id
208 , measure_id
209 , provider_id
210 , created_by
211 , modified_by
212 , create_timestamp
213 , modify_timestamp
214 --, row_timestamp
215 , measure_system
216 FROM cqm_provider_measure_xref_08312016;
217 */