Scribd
Upload
3 views3 pages

Generate Null Report Procedure

The document defines two stored procedures in SQL: 'generate_null_report_DOPS_2047' and 'generate_missing_days_DOPS_2047'. The first procedure creates a report of usage points with null intervals for a specified month in 2022, while the second procedure identifies and inserts missing days for meter readings into a temporary table. Both procedures utilize various SQL operations including joins, conditional checks, and loops to manipulate and retrieve data from specified tables.

Uploaded by

parveenkatyal559
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
3 views3 pages

Generate Null Report Procedure

The document defines two stored procedures in SQL: 'generate_null_report_DOPS_2047' and 'generate_missing_days_DOPS_2047'. The first procedure creates a report of usage points with null intervals for a specified month in 2022, while the second procedure identifies and inserts missing days for meter readings into a temporary table. Both procedures utilize various SQL operations including joins, conditional checks, and loops to manipulate and retrieve data from specified tables.

Uploaded by

parveenkatyal559
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

DELIMITER $$

DROP PROCEDURE IF EXISTS generate_null_report_DOPS_2047;

CREATE PROCEDURE generate_null_report_DOPS_2047(p_month BIGINT)


BEGIN
DROP TABLE IF EXISTS all_nulls;
CREATE TABLE all_nulls AS
SELECT m.usage_point, m.unit_id, m.register_type, id.*
FROM interval_data_5mins partition (pnmax) id
INNER JOIN channel m ON m.meter_id = id.meter_id
WHERE m.unit_id IN ('1', '91a8a37e-9481-11e6-b623-6f6ccec676fa', 'b7dbe5fc-c775-
11e6-9e9c-18760ad0f457')
AND year(id.usage_date) = 2022 and MONTH(id.usage_date) = p_month
and (id.interval_1 is NULL or id.interval_2 is NULL or id.interval_3 is NULL or
id.interval_4 is NULL or id.interval_5 is NULL or id.interval_6 is NULL or
id.interval_7 is NULL or id.interval_8 is NULL or id.interval_9 is NULL or
id.interval_10 is NULL or id.interval_11 is NULL or id.interval_12 is NULL or
id.interval_13 is NULL or id.interval_14 is NULL or id.interval_15 is NULL or
id.interval_16 is NULL or id.interval_17 is NULL or id.interval_18 is NULL or
id.interval_19 is NULL or id.interval_20 is NULL or id.interval_21 is NULL or
id.interval_22 is NULL or id.interval_23 is NULL or id.interval_24 is NULL or
id.interval_25 is NULL or id.interval_26 is NULL or id.interval_27 is NULL or
id.interval_28 is NULL or id.interval_29 is NULL or id.interval_30 is NULL or
id.interval_31 is NULL or id.interval_32 is NULL or id.interval_33 is NULL or
id.interval_34 is NULL or id.interval_35 is NULL or id.interval_36 is NULL or
id.interval_37 is NULL or id.interval_38 is NULL or id.interval_39 is NULL or
id.interval_40 is NULL or id.interval_41 is NULL or id.interval_42 is NULL or
id.interval_43 is NULL or id.interval_44 is NULL or id.interval_45 is NULL or
id.interval_46 is NULL or id.interval_47 is NULL or id.interval_48 is NULL or
id.interval_49 is NULL or id.interval_50 is NULL or id.interval_51 is NULL or
id.interval_52 is NULL or id.interval_53 is NULL or id.interval_54 is NULL or
id.interval_55 is NULL or id.interval_56 is NULL or id.interval_57 is NULL or
id.interval_58 is NULL or id.interval_59 is NULL or id.interval_60 is NULL or
id.interval_61 is NULL or id.interval_62 is NULL or id.interval_63 is NULL or
id.interval_64 is NULL or id.interval_65 is NULL or id.interval_66 is NULL or
id.interval_67 is NULL or id.interval_68 is NULL or id.interval_69 is NULL or
id.interval_70 is NULL or id.interval_71 is NULL or id.interval_72 is NULL or
id.interval_73 is NULL or id.interval_74 is NULL or id.interval_75 is NULL or
id.interval_76 is NULL or id.interval_77 is NULL or id.interval_78 is NULL or
id.interval_79 is NULL or id.interval_80 is NULL or id.interval_81 is NULL or
id.interval_82 is NULL or id.interval_83 is NULL or id.interval_84 is NULL or
id.interval_85 is NULL or id.interval_86 is NULL or id.interval_87 is NULL or
id.interval_88 is NULL or id.interval_89 is NULL or id.interval_90 is NULL or
id.interval_91 is NULL or id.interval_92 is NULL or id.interval_93 is NULL or
id.interval_94 is NULL or id.interval_95 is NULL or id.interval_96 is NULL or
id.interval_97 is NULL or id.interval_98 is NULL or id.interval_99 is NULL or
id.interval_100 is NULL or id.interval_101 is NULL or id.interval_102 is NULL or
id.interval_103 is NULL or id.interval_104 is NULL or id.interval_105 is NULL or
id.interval_106 is NULL or id.interval_107 is NULL or id.interval_108 is NULL or
id.interval_109 is NULL or id.interval_110 is NULL or id.interval_111 is NULL or
id.interval_112 is NULL or id.interval_113 is NULL or id.interval_114 is NULL or
id.interval_115 is NULL or id.interval_116 is NULL or id.interval_117 is NULL or
id.interval_118 is NULL or id.interval_119 is NULL or id.interval_120 is NULL or
id.interval_121 is NULL or id.interval_122 is NULL or id.interval_123 is NULL or
id.interval_124 is NULL or id.interval_125 is NULL or id.interval_126 is NULL or
id.interval_127 is NULL or id.interval_128 is NULL or id.interval_129 is NULL or
id.interval_130 is NULL or id.interval_131 is NULL or id.interval_132 is NULL or
id.interval_133 is NULL or id.interval_134 is NULL or id.interval_135 is NULL or
id.interval_136 is NULL or id.interval_137 is NULL or id.interval_138 is NULL or
id.interval_139 is NULL or id.interval_140 is NULL or id.interval_141 is NULL or
id.interval_142 is NULL or id.interval_143 is NULL or id.interval_144 is NULL or
id.interval_145 is NULL or id.interval_146 is NULL or id.interval_147 is NULL or
id.interval_148 is NULL or id.interval_149 is NULL or id.interval_150 is NULL or
id.interval_151 is NULL or id.interval_152 is NULL or id.interval_153 is NULL or
id.interval_154 is NULL or id.interval_155 is NULL or id.interval_156 is NULL or
id.interval_157 is NULL or id.interval_158 is NULL or id.interval_159 is NULL or
id.interval_160 is NULL or id.interval_161 is NULL or id.interval_162 is NULL or
id.interval_163 is NULL or id.interval_164 is NULL or id.interval_165 is NULL or
id.interval_166 is NULL or id.interval_167 is NULL or id.interval_168 is NULL or
id.interval_169 is NULL or id.interval_170 is NULL or id.interval_171 is NULL or
id.interval_172 is NULL or id.interval_173 is NULL or id.interval_174 is NULL or
id.interval_175 is NULL or id.interval_176 is NULL or id.interval_177 is NULL or
id.interval_178 is NULL or id.interval_179 is NULL or id.interval_180 is NULL or
id.interval_181 is NULL or id.interval_182 is NULL or id.interval_183 is NULL or
id.interval_184 is NULL or id.interval_185 is NULL or id.interval_186 is NULL or
id.interval_187 is NULL or id.interval_188 is NULL or id.interval_189 is NULL or
id.interval_190 is NULL or id.interval_191 is NULL or id.interval_192 is NULL or
id.interval_193 is NULL or id.interval_194 is NULL or id.interval_195 is NULL or
id.interval_196 is NULL or id.interval_197 is NULL or id.interval_198 is NULL or
id.interval_199 is NULL or id.interval_200 is NULL or id.interval_201 is NULL or
id.interval_202 is NULL or id.interval_203 is NULL or id.interval_204 is NULL or
id.interval_205 is NULL or id.interval_206 is NULL or id.interval_207 is NULL or
id.interval_208 is NULL or id.interval_209 is NULL or id.interval_210 is NULL or
id.interval_211 is NULL or id.interval_212 is NULL or id.interval_213 is NULL or
id.interval_214 is NULL or id.interval_215 is NULL or id.interval_216 is NULL or
id.interval_217 is NULL or id.interval_218 is NULL or id.interval_219 is NULL or
id.interval_220 is NULL or id.interval_221 is NULL or id.interval_222 is NULL or
id.interval_223 is NULL or id.interval_224 is NULL or id.interval_225 is NULL or
id.interval_226 is NULL or id.interval_227 is NULL or id.interval_228 is NULL or
id.interval_229 is NULL or id.interval_230 is NULL or id.interval_231 is NULL or
id.interval_232 is NULL or id.interval_233 is NULL or id.interval_234 is NULL or
id.interval_235 is NULL or id.interval_236 is NULL or id.interval_237 is NULL or
id.interval_238 is NULL or id.interval_239 is NULL or id.interval_240 is NULL or
id.interval_241 is NULL or id.interval_242 is NULL or id.interval_243 is NULL or
id.interval_244 is NULL or id.interval_245 is NULL or id.interval_246 is NULL or
id.interval_247 is NULL or id.interval_248 is NULL or id.interval_249 is NULL or
id.interval_250 is NULL or id.interval_251 is NULL or id.interval_252 is NULL or
id.interval_253 is NULL or id.interval_254 is NULL or id.interval_255 is NULL or
id.interval_256 is NULL or id.interval_257 is NULL or id.interval_258 is NULL or
id.interval_259 is NULL or id.interval_260 is NULL or id.interval_261 is NULL or
id.interval_262 is NULL or id.interval_263 is NULL or id.interval_264 is NULL or
id.interval_265 is NULL or id.interval_266 is NULL or id.interval_267 is NULL or
id.interval_268 is NULL or id.interval_269 is NULL or id.interval_270 is NULL or
id.interval_271 is NULL or id.interval_272 is NULL or id.interval_273 is NULL or
id.interval_274 is NULL or id.interval_275 is NULL or id.interval_276 is NULL or
id.interval_277 is NULL or id.interval_278 is NULL or id.interval_279 is NULL or
id.interval_280 is NULL or id.interval_281 is NULL or id.interval_282 is NULL or
id.interval_283 is NULL or id.interval_284 is NULL or id.interval_285 is NULL or
id.interval_286 is NULL or id.interval_287 is NULL or id.interval_288 is Null )
;
DELIMITER $$
drop procedure if exists generate_missing_days_DOPS_2047 $$
create procedure generate_missing_days_DOPS_2047(p_month integer)
BEGIN
declare currentdate date;
declare gap_start_date date;
declare gap_end_date date;
set currentdate = adddate("2022-12-01", interval +0 month);
set gap_start_date = adddate("2022-12-01", interval +0 month);
set gap_end_date = adddate(gap_start_date, interval +1 month);
select currentdate;
select gap_start_date;
select gap_end_date;

while currentdate < gap_end_date DO


insert into all_dates values (currentdate) on duplicate key update
gap_date = currentdate;
set currentdate = adddate(currentdate, 1);
end while;
insert into temp_missing_dates_meter_id
select m.meter_id, m.usage_point, m.unit_id, ad.gap_date
from (
select meter_id, usage_date
from (
select interval_data.meter_id, interval_data.usage_date
from interval_data partition(p2022)
inner join channel on channel.meter_id =
interval_data.meter_id
inner join service_points on service_points.id =
channel.service_point_id
inner join user_service_point on
user_service_point.service_point_id = service_points.id
where cast(usage_date as date) between gap_start_date and
gap_end_date
and user_service_point.service_to_date is NULL) b
group by meter_id
) a
cross join (
select gap_date
from all_dates) ad
left join (
select meter_id, usage_date
from interval_data partition(p2022)
where cast(usage_date as date) between gap_start_date and gap_end_date) id
on a.meter_id = id.meter_id and ad.gap_date = cast(id.usage_date as date)
inner join channel m
on m.meter_id = a.meter_id
where id.usage_date is null;

END $$
DELIMITER ;
~

You might also like