February 26, 2016 at 12:52 pm
Kind people, help me, please, with something if you can!
Please, see two attached pictures.
The two attached pictures (if they have attached correctly)
show the result of my job. The result looks fine (as I want), but...
Using LEFT OUTER JOIN:
WITH cte AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY Id_hour) AS rn,
dTime
FROM HourStat_2
WHERE UVP001TP01_HWEnSum_kWh is not null
)
SELECT
Id_Hour,
dTime,
UVP001TP01_HWEnSum_kWh,
asd.OPT_1
FROM HourStat_2
LEFT OUTER JOIN
(
SELECT
T2.dTime AS Date2,
OPT_1 =
CASE
WHEN DATEDIFF(hh,T1.dTime,T2.dTime) > 1 THEN
'4'
ELSE
NULL
END
FROM cte AS T1, cte AS T2
WHERE T1.rn = (T2.rn - 1)
) AS asd
ON HourStat_2.dTime = asd.Date2
ORDER BY HourStat_2.Id_hour
Using OUTER APPLY:
ALTER FUNCTION MYFUNC(@DT as datetime)
RETURNS @MT TABLE (OPT_1 INT) AS
BEGIN
WITH cte AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY Id_hour) AS rn,
dTime
FROM HourStat_2
WHERE UVP001TP01_HWEnSum_kWh is not null AND dTime <= @dt
)
INSERT @MT
SELECT TOP 1
OPT_1 =
CASE
WHEN DATEDIFF(hh,T1.dTime,T2.dTime) > 1 THEN
'4'
ELSE
NULL
END
FROM cte AS T1, cte AS T2
WHERE T2.dTime = @dt AND T1.rn = (T2.rn - 1)
RETURN
END;
GO
SELECT Id_hour, dTime, UVP001TP01_HWEnSum_kWh, RES.OPT_1
FROM HourStat_2
OUTER APPLY
MYFUNC(dTime) AS RES
The task is to do the same job, as it is made for UVP001TP01_HWEnSum_kWh, (apply same processing script to the rest of columns) for the rest of columns.
But I am afraid of too large size of script (for my typing and for my vbs-application where I use it) =current_size*col_number, and also
too long processing time (this shown peace for one column takes about a minute).
So I am searching for the decision "How to...":
1) Reduce script size in case of applying it to the rest of columns
2) Reduce processing time by optimizing something in it.
An algorithm I've used:
1) SITUATION:
every 1 hour I write to DB an electrical energy quantity from some object at our plant.
some records may be lost (both) if server is rebooting in certain moment (the difference in rows: ID=1, Time > 1 hour) and if connection to some object is lost (row sequence by 'time' is saved, but lost values are NULL).
TASK is to mark fields (in my application) started after lost records by colors (for this I am adding OPT_1 column which contains my color marker)
2) STEP_1: I create "temp table" which doesn't contain any NULLs for certain object (column) and make countable column RowNumber - to repair (generate) consequence of row numering after server rebooting when it was the time for writing (to provide condition of fetching like rn1=rn2-1 even for row after deleting NULL-rows IDs).
STEP_2: connecting my new "temp table" to itself with shifting by like rn1=rn2-1.
STEP_3: count date_time difference between rows, creating countable column OPT_1, and I fill it by not the count of lost hours DTdiff, but I fill it by my marker '4' if DTdiff > 1.
STEP_4: I Add the calculated in my temp_table column 'OPT_1', which is made for the only column UVP001TP01_HWEnSum_kWh - for the only plant object for a while, to the original table.
Any additional information I will give if you ask, just say.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply