none
Need help in writing a query

    Question

  • Hi,

    I have the following tables:

    1. Units table:

    • ID
    • Serial number
    • Name
    • Location

    2. UnitsData table:

    • ID
    • UnitID (int)
    • DateOfData (datetime)
    • Value (float)

    I have to show the following information in a gridview and I do not succeed doing it through SQL query and need help:

    I have to show a grid with one line for every unit with the following information about it:

    1. Unit’s name.
    2. Unit’s serial number
    3. Unit’s location.
    4. Average of the value column for the unit in the last 30 days. 
    5. The latest DateOfData of the unit.
    6. The value in the latest dateofdata.
    7. true if the latest data was received in the last 48 hours. else - false
    8. true if I got the same value in different dates in the past 48 hours.

    I know it is a big question.

    I will appreciate any help about it.

    Thanks

    Sunday, October 06, 2013 5:22 PM

Answers

  • I'm not sure if I have understood the lastest point, and I not have a way to test the data output. Anyway, I have written this:

    WITH AvgValue
    AS
    (
    	SELECT 
    		D.UnitId, AVG(D.Value) AS AverageValue
    	FROM
    		UnitData D
    	WHERE
    		D.DateOfData > DATEADD(day, -30, CURRENT_TIMESTAMP)
    	GROUP BY
    		D.UnitId
    )
    SELECT
    	U.ID, U.Location, U.Name, U.SerialNumber, AvgValue.AverageValue,
    	L.LastValue, L.LastDateOfData, L.IsLastValueReceivedInTheLastest48Hours,
    	CONVERT( bit, CASE WHEN NOT EXISTS (
    		SELECT * 
    		FROM UnitData 
    		WHERE 
    			UnitData.UnitId = L.UnitId 
    			AND UnitData.Value <> L.LastValue
    			AND UnitData.DateOfData > DATEADD(HOUR, -48, CURRENT_TIMESTAMP)
    	) AND IsLastValueReceivedInTheLastest48Hours = 1 THEN 1
    	ELSE 0 END) AS LastValueWasConstantInTheLastest48Hours 
    FROM
    	Units U
    	LEFT OUTER JOIN AvgValue
    		ON U.ID = AvgValue.UnitId
    	OUTER APPLY
    	(
    		SELECT TOP (1)
    			UD.UnitId, UD.Value AS LastValue, UD.DateOfData AS LastDateOfData,
    			CONVERT(bit, 
    				CASE WHEN UD.DateOfData > DATEADD(HOUR, -48, CURRENT_TIMESTAMP) THEN 1 
    				ELSE 0 END
    			) AS IsLastValueReceivedInTheLastest48Hours
    		FROM
    			UnitData UD
    		WHERE
    			UD.UnitId = U.ID
    		ORDER BY
    			UD.DateOfData DESC
    	) L


    Sunday, October 06, 2013 6:27 PM

All replies

  • Try something like below

    SELECT u.Name,u.[Serial Number],u.Location,
    AVG(ud.Value) AS AvgValue,
    MAX(CASE WHEN Seq=1 THEN DateOfData END) AS LatestDateOfData,
    MAX(CASE WHEN Seq=1 THEN Value END) AS ValueAtLatestDate,
    CASE WHEN MAX(CASE WHEN Seq=1 THEN DateOfData END) >= DATEADD(hh,-48,GETDATE()) AND MAX(CASE WHEN Seq=1 THEN DateOfData END) < = GETDATE() THEN 1 ELSE 0 END AS Last48hourFlag,
    CASE WHEN COUNT(DISTINCT CASE WHEN DateOfData >= DATEADD(hh,-48,GETDATE()) AND DateOfData < = GETDATE() THEN Value ELSE NULL END) > 1 THEN 0 ELSE 1 END AS DistValue
    FROM Units u
    INNER JOIN (
    SELECT ROW_NUMBER() OVER (PARTITION BY UnitID ORDER BY DateOfData DESC) AS Seq,*
    FROM UnitsData
    ) ud
    ON ud.UnitID = u.ID
    GROUP BY u.Name,u.[Serial Number],u.Location

    Sunday, October 06, 2013 5:59 PM
  • Select u.ID, u.Name, u.[Serial Number], u.Location,
    Avg(case when DateDiff(day, DateOfData, Getdate()) <= 30
    then ud.Value else null end) 
    as AverageValueLast30Day,
    Avg(case when DateOfData = lud.LastDateOfData
    then ud.Value else null end) 
    as LastValue,
    Max(case when DateDiff(hour, LastDateOfData, getdate()) <=48
    then 1 else 0 end) as ValueInLast24Hours,
    case when
    (Count(case when DateDiff(hour, LastDateOfData, getdate()) <=48
    then us.Value else null end) 
    
    - Count(Distinct case when DateDiff(hour, LastDateOfData, getdate()) <=48
    then ud.Value else null end)) > 0
    then 1 else 0 end
    as SameValueTwice
    
    from Units u
    left outer join 
    UnitsData ud
    on u.ID = ud.UnitID
    left outer join 
    (Select UnitID, Max(DateOfData) as LastDateOfData
    from UnitsData
    Group by UnitID
    ) as lud
    on lud.UnitID = ud.UnitID
    group by u.ID, u.Name, u.[Serial Number], u.Location
    
    I don't have my server to test this, so please test it and point out its problems.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Sunday, October 06, 2013 5:59 PM
  • Hi

    PFB code

    select 1 ID,1 serial_number, 'Name1' Name,'Location1' Location into #units
    
    
    select 1 ID,1 UnitID, GETDATE()dateofdata ,1.00 value into #unitsData UNION ALL
    select 2 ID,1 UnitID, GETDATE()-31 dateofdata ,1.00 value  UNION ALL
    select 2 ID,1 UnitID, GETDATE()-3 dateofdata ,1.00 value
    
    
    select a.name,a.serial_number,a.location,a.AVG_Last30days,a.max_dateofdata,
    b.value max_dateofdata_value,
    case when MAX_dateofdata > GETDATE()-2 then 'True' else 'False' end,
    case when exists(select top 1 1 from #unitsdata c where a.unitid=c.unitid 
    and c.dateofdata<a.max_dateofdata and c.dateofdata > GETDATE()-2 and c.value=b.value ) then 'True' else 'False' end
      from
    (select a.ID Unitid,a.serial_number,a.name,a.location,
    AVG(case when dateofdata > getdate()-30 then value else null end)AVG_Last30days,
    MAX(dateofdata) max_dateofdata
    from  #units a INNER JOIN #unitsdata b on a.ID=b.UnitID
    group by a.ID,a.serial_number,a.name,a.location) a 
    inner join #unitsdata b on a.unitid=b.unitid and a.max_dateofdata=b.dateofdata
    
    

    Thanks

    Saravana Kumar C

    Sunday, October 06, 2013 6:17 PM
  • I'm not sure if I have understood the lastest point, and I not have a way to test the data output. Anyway, I have written this:

    WITH AvgValue
    AS
    (
    	SELECT 
    		D.UnitId, AVG(D.Value) AS AverageValue
    	FROM
    		UnitData D
    	WHERE
    		D.DateOfData > DATEADD(day, -30, CURRENT_TIMESTAMP)
    	GROUP BY
    		D.UnitId
    )
    SELECT
    	U.ID, U.Location, U.Name, U.SerialNumber, AvgValue.AverageValue,
    	L.LastValue, L.LastDateOfData, L.IsLastValueReceivedInTheLastest48Hours,
    	CONVERT( bit, CASE WHEN NOT EXISTS (
    		SELECT * 
    		FROM UnitData 
    		WHERE 
    			UnitData.UnitId = L.UnitId 
    			AND UnitData.Value <> L.LastValue
    			AND UnitData.DateOfData > DATEADD(HOUR, -48, CURRENT_TIMESTAMP)
    	) AND IsLastValueReceivedInTheLastest48Hours = 1 THEN 1
    	ELSE 0 END) AS LastValueWasConstantInTheLastest48Hours 
    FROM
    	Units U
    	LEFT OUTER JOIN AvgValue
    		ON U.ID = AvgValue.UnitId
    	OUTER APPLY
    	(
    		SELECT TOP (1)
    			UD.UnitId, UD.Value AS LastValue, UD.DateOfData AS LastDateOfData,
    			CONVERT(bit, 
    				CASE WHEN UD.DateOfData > DATEADD(HOUR, -48, CURRENT_TIMESTAMP) THEN 1 
    				ELSE 0 END
    			) AS IsLastValueReceivedInTheLastest48Hours
    		FROM
    			UnitData UD
    		WHERE
    			UD.UnitId = U.ID
    		ORDER BY
    			UD.DateOfData DESC
    	) L


    Sunday, October 06, 2013 6:27 PM
  • Thanks a lot.

    I tried Lopez solution and it works like a magic.

    I have another question that I do not know how to do:

    The average shall be calculated like this:

    1. for every day in the last 30 days: calculate the differences between adjecent values (adjecent in time) and make avarage of these differences.

    2. get every day's calculated avarage from step 1 and make average of the 30 days.

    Can you please change the above script of Lopez to do that ?

    I appreciate your effort !!! Thanks a lot

    Sunday, October 06, 2013 7:28 PM
  • What version of SQL Server are you using?

    I need to know it, because SQL Server 2012 introduces new window functions that will do much easier to write the query.

    Monday, October 07, 2013 9:06 AM
  • SQL Express 2008. Sorry :(
    Monday, October 07, 2013 12:19 PM
  • I have calculated the average of the absolute value of the difference:

    WITH Diffs
    AS
    (
    	SELECT 
    		UD.UnitID, CONVERT(datetime, CONVERT(date, UD.DateOfData)) AS DayDate,
    		ABS(UD.Value - P.PreviousValue) AS Diff
    
    	FROM
    		UnitsData UD
    		OUTER APPLY (
    			SELECT TOP (1) Prev.Value AS PreviousValue
    			FROM
    				UnitsData Prev
    			WHERE
    				Prev.UnitID = UD.UnitID
    			ORDER BY 
    				Prev.ID DESC
    		) P
    	WHERE
    		UD.DateOfData > DATEADD(day, -30, CURRENT_TIMESTAMP)
    ), 
    DAD -- Day Average Differences
    AS
    (
    	SELECT
    		D.UnitID, D.DayDate, AVG(D.Diff) AS DayAvgDiff
    	FROM
    		Diffs D
    	GROUP BY
    		D.UnitID, D.DayDate
    ), 
    L30DAD -- Last 30 days average over day diff average 
    AS
    (
    	SELECT
    		DAD.UnitID, AVG(DAD.DayAvgDiff) AS Last30DaysDiffsAvg
    	FROM
    		DAD
    	GROUP BY
    		DAD.UnitID
    ),
    AvgValue
    AS
    (
    	SELECT 
    		D.UnitId, AVG(D.Value) AS AverageValue
    	FROM
    		UnitsData D
    	WHERE
    		D.DateOfData > DATEADD(day, -30, CURRENT_TIMESTAMP)
    	GROUP BY
    		D.UnitId
    )
    SELECT
    	U.ID, U.Location, U.Name, U.SerialNumber, 
    	AvgValue.AverageValue, L30DAD.Last30DaysDiffsAvg,
    	L.LastValue, L.LastDateOfData, L.IsLastValueReceivedInTheLastest48Hours,
    	CONVERT( bit, CASE WHEN NOT EXISTS (
    		SELECT * 
    		FROM UnitsData 
    		WHERE 
    			UnitsData.UnitId = L.UnitId 
    			AND UnitsData.Value <> L.LastValue
    			AND UnitsData.DateOfData > DATEADD(HOUR, -48, CURRENT_TIMESTAMP)
    	) AND IsLastValueReceivedInTheLastest48Hours = 1 THEN 1
    	ELSE 0 END) AS LastValueWasConstantInTheLastest48Hours 
    FROM
    	Units U
    	LEFT OUTER JOIN AvgValue
    		ON U.ID = AvgValue.UnitId
    	LEFT OUTER JOIN L30DAD
    		ON U.ID = L30DAD.UnitID
    	OUTER APPLY
    	(
    		SELECT TOP (1)
    			UD.UnitId, UD.Value AS LastValue, UD.DateOfData AS LastDateOfData,
    			CONVERT(bit, 
    				CASE WHEN UD.DateOfData > DATEADD(HOUR, -48, CURRENT_TIMESTAMP) THEN 1 
    				ELSE 0 END
    			) AS IsLastValueReceivedInTheLastest48Hours
    		FROM
    			UnitsData UD
    		WHERE
    			UD.UnitId = U.ID
    		ORDER BY
    			UD.DateOfData DESC
    	) L
    
    

    Monday, October 07, 2013 3:54 PM
  • Thanks you very much !!

    It works (but still not what I need. but I do not want to nag).

    I appreciate very much your work and answers.

    If it is still an option to finish, attached is xls file that shows the steps how to calculate the avarage by steps in excel.

    https://docs.google.com/file/d/0B8WjLVs5XJiVWlEzMUV6UnQ0SWs/edit?usp=sharing

    Thanks a lot again


    • Edited by YI1010 Tuesday, October 08, 2013 5:58 PM
    Tuesday, October 08, 2013 5:40 PM