Sql Query Help Understanding

Hello,

I am looking at a store procedure in a SQL database and am trying to figure out what this bit of code is doing. Can anybody give me a dig out.

I know its getting a flowmeter reading from yesterday and the day before. Its getting the difference between these values which is the daily flowrate value for yesterday.

The code with SELECT COUNT(*)*999999 is where im really lost. What is the 999999 value for?

The flowmeter value rolls over at 999999.

DECLARE @YEAR INT
DECLARE @MONTH INT
DECLARE @DAY INT

DECLARE @YEAR2 INT
DECLARE @MONTH2 INT
DECLARE @DAY2 INT

DECLARE @40FI0011_TOT DECIMAL(18,3)
--Table to sort data for 40FI0011
DECLARE @TBL_Report TABLE (
LoggedValue Decimal(18,3), DateTime datetime, RowNum INT)
--General Upload table
DECLARE @TBL_Upload TABLE (DateTime datetime, Area varchar(50), Volume Decimal(18,3))
--The day we are reporting on
SELECT @YEAR = datepart(YYYY, dateadd (dd, -@DaysAgo, getdate()))
SELECT @MONTH = datepart(mm, dateadd (dd, -@DaysAgo, getdate()))
SELECT @DAY = datepart(dd, dateadd (dd, -@DaysAgo, getdate()))
--The day before
SELECT @YEAR2 = datepart(YYYY, dateadd (dd, -(@DaysAgo+1), getdate()))
SELECT @MONTH2 = datepart(mm, dateadd (dd, -(@DaysAgo+1), getdate()))
SELECT @DAY2 = datepart(dd, dateadd (dd, -(@DaysAgo+1), getdate()))

--In case the SP is ran twice - start by clearing out data for the selected day
DELETE FROM Utilities_Water_Usage
Where datepart (yyyy,date_time) = @YEAR
and datepart (mm,date_time) =@MONTH
and datepart (dd,date_time) =@DAY

--INSERT LAST VALUE FROM THE DAY BEFORE
INSERT INTO @TBL_Report
select TOP 1 LoggedValue, DateTime, ROW_NUMBER() OVER(ORDER BY DateTime DESC) AS RowNum
from dbo.Utilities_Water_FlowMeters
where FlowMeter = '40FI0011' and LoggedValue is not null
and datepart (yyyy,datetime) = @YEAR2
and datepart (mm,datetime) =@MONTH2
and datepart (dd,datetime) =@DAY2

--INSERT ALL VALUES FROM DAY BEING REPORTED

INSERT INTO @TBL_Report
select LoggedValue, DateTime,
(ROW_NUMBER() OVER(ORDER BY DateTime asc))+1 AS RowNum --Add 1 to a/c for last value from the day before
from dbo.Utilities_Water_FlowMeters
where FlowMeter = '40FI0011' and LoggedValue is not null
and datepart (yyyy,datetime) = @YEAR
and datepart (mm,datetime) =@MONTH
and datepart (dd,datetime) =@DAY

SELECT @40FI0011_TOT = (
SELECT COUNT(*)*999999
FROM @TBL_Report as a
inner join @TBL_Report as b
on a.RowNum = b.RowNum -1
where (b.LoggedValue - a.LoggedValue) < 0)

+ --Add the last value logged in the day
(SELECT TOP 1 LoggedValue FROM @TBL_Report order by Datetime desc)

- --Subtract the last value logged the day before
(SELECT TOP 1 LoggedValue FROM @TBL_Report order by Datetime asc)
INSERT INTO dbo.Utilities_Water_Usage (Volume, Area, Date_Time)
--INSERT INTO @TBL_Upload (Volume, Area, Date_Time)
SELECT @40FI0011_TOT as Volume, '40FI0011' as Area, dateadd(dd,-@DaysAgo,getdate()) as Date_Time

UNION ALL

SELECT(
--Last Value from the previous Day
(SELECT top 1 LoggedValue FROM
dbo.Utilities_Water_FlowMeters
where flowMeter = 'MAG_CW_1'
and datepart (yyyy,datetime) = @YEAR
and datepart (mm,datetime) =@MONTH
and datepart (dd,datetime) =@DAY
and loggedValue is not null
order by DateTime desc)

-

--Last Value from the Day we are reporting on
(SELECT top 1 LoggedValue FROM
dbo.Utilities_Water_FlowMeters
where flowMeter = 'MAG_CW_1'
and datepart (yyyy,datetime) = @YEAR2
and datepart (mm,datetime) =@MONTH2
and datepart (dd,datetime) =@DAY2
and loggedValue is not null
order by DateTime desc)
) * 1000 as Volume, 'MAG_CW_1' as Area, dateadd(dd,-@DaysAgo,getdate()) as Date_Time

is this for microsoft sql server?

YES.
I know this query gets all values for yesterday and the last value for the day before. I gets the difference between both values and thats the daily flowrate. The rollover value in the PLC is 999999.

So somedays the value at the start of the day is 999900, and is 200 then at the end of the day.
If the normal maths were done in this case, the daily value would be incorrrect.
The query above sees to hndle this rollover but I dont understand how it does it.

you'll have a much better chance of getting a reply in the microsoft sql server forum

thread moved