#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    2
    Rep Power
    0

    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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    is this for microsoft sql server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    2
    Rep Power
    0
    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.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    you'll have a much better chance of getting a reply in the microsoft sql server forum

    thread moved
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo