The SQL processing method of the report hourly usage after the field DCS instrument clear 0 (column change)

Recently, I was in the project, at the scene met customers gas meter accumulation of millions of per day, one months to accumulate the degree of the meter will exceed the upper limit of the instrument, so customer 25th must be cleared every month. This way the meter's degrees are re-calculated from zero. In the calculation of gas output per hour, the scene if it is manual copy number does not matter, only need to do a simple formula to calculate the "output" = "The reading of the table of zero"-"last hour reading" + "current clears 0 after reading" can be. But for a software system, no one's brain intervention can be so simple, he needs to know whether the meter is clear, to know when the 0 clear, but also need to know the reading of 0. Know that these three points in order to provide software methods to automatically handle the zero situation.

I at the scene to the customer production department developed a 24-hour, hourly hourly gas production report, open every day, automatically calculate the hourly gas usage, such as

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/52/AF/wKioL1Rkvaui3KpNAAGnKQcwBNo334.jpg "title=" normal data " alt= "Wkiol1rkvaui3kpnaagnkqcwbno334.jpg"/>

Then through the real-time library software in accordance with the whole point to collect all the hour data, this in the instrument is not clear 0 continuous accumulation is no problem, but once cleared, the following conditions will appear, see Red Box

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/52/B3/wKiom1RkvxuSF_YYAAFHCxoYZvA486.jpg "title=" Problem "alt = "Wkiom1rkvxusf_yyaafhcxoyzva486.jpg"/>

Because this data is every hour of the hourly collection, regardless of whether or not in two hours between the zero, the system according to the reading to the minus will calculate the wrong hour of data, the current data collection table is hourly, the structure is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/52/B5/wKioL1Rkw0GyjqnqAADcjEsA9d4394.jpg "title=" 5.jpg " alt= "Wkiol1rkw0gyjqnqaadcjesa9d4394.jpg"/>

According to the algorithm formula mentioned above: "Output" = "reading of the time table"-"Last hour reading" + "current clears 0 after reading", need to know whether the meter is clear, still need to know when the clear of 0, but also needs to know the reading of 0 time. Then the system to timely response to clear 0 time, if not in time, will cause relatively large errors. I used a relatively simple method, re-real-time library system to collect gas readings per minute, once cleared, 1 minutes can be reflected, will not cause a lot of error, users can accept, at the same time than according to the seconds to collect, will greatly reduce the table data, the performance of the report is not very significant.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/52/B6/wKioL1Rkw_vxxShxAARp99yTXT8989.jpg "title=" 6.jpg " alt= "Wkiol1rkw_vxxshxaarp99ytxt8989.jpg"/>

Provide this table, after the next minute data subtraction, once a negative value, I can know that the meter at this time (hours + minutes) to zero, while finding the last minute of reading, and the first minute of the degree M0 can calculate the amount of use before 0, plus M59 readings, You get the amount of time spent on the hour of zeroing. Mention we can know that at 10:8 Zero, then 10 points of use is m8-m0+m59.

Through the above analysis, our ideas come out,

1, find out the data point of zero, the number of minutes before the subtraction

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/52/BA/wKiom1Rkxbnx4sUFAAJp8TVs80c982.jpg "title=" 7.jpg " alt= "Wkiom1rkxbnx4sufaajp8tvs80c982.jpg"/>

2, in order to facilitate the calculation, through a simple SQL operation can be positioned to the time of the zero, we need to reverse the column to the line, while getting analysed by sub. Through the Unpivot method.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/52/B9/wKioL1Rkx2XwDh-pAAFQ_e_RgTk021.jpg "title=" invert "alt = "Wkiol1rkx2xwdh-paafq_e_rgtk021.jpg"/>

3, through the post-subtraction for the assignment to the point of zeroing and the point in time, in which hour

SELECT top 1 SUBSTRING (Mname,2,len (Mname)-2) mins1, DATEPART (hh,[date]), SUBSTRING (Mname,2,len (Mname)-2)-1 MINS0, DATEPART (hh,[date]) occhfrom unpvt WHERE mvalues<0;

4, positioning to the point of time, through the point of time please find the Minutes table inside the readings, to calculate, because the field is calculated, here with the dynamic SQL

DECLARE @m1value decimal (22,6), @m0value decimal (22,6), @m59value decimal (22,6);   Set @qzerosql = ' Select top 1 @m1value =m ' [email protected] + ', @m0value =m0, @m59value =m59 ' + ' from tblxxxx ' + ' where CONVERT (varchar), [date],120] [email protected] and DATEPART (hh,[date]) [email protected] '; EXEC sp_executesql @qzerosql, N ' @m1value decimal (22,6) output, @m0value decimal (22,6) output, @m59value decimal (22,6) Output, @occurdate date, @OCCH int, @pointname varchar, @m1value output, @m0value output, @m59value output,@ Occurdate, @OCCH;

5, finally, return the calculated data and the time point of the data, on the report through the point of time in the report cell processing can be judged.

SET @ResetHourValue = (select @m1value [email protected] [email protected]); Set @[email protected];

The key to deal with this problem is to locate the 0 point in time, by using SQL Sever to make a career change method. If you have other simple and feasible methods, please also enlighten us, O (∩_∩) o Thank you

This article is from the "7286556" blog, please be sure to keep this source http://7296556.blog.51cto.com/7286556/1576270

The SQL processing method of the report hourly usage after the field DCS instrument clear 0 (column change)