October 6, 2013 at 11:47 am
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
October 6, 2013 at 12:00 pm
Sounds like homework...
Would you please show us what you've tried so far and where you get stuck?
To refer to the most recent row, you could either use an outer join or try the CROSS APPLY approach (if you're familiar with it).
A good starting point would be ready to use table definition and some sample data as described in the first link in my signature...
October 6, 2013 at 12:11 pm
Hi,
Attached is a script to build the tables with data.
(rename to .sql of course).
I tried to do for the start something like:
SELECT Unit.Name, Unit.SerialNumber, Unit.Location, UD.PowerConsumptionValue, LastDate
FROM Units Unit
CROSS APPLY (
SELECT TOP 1 UD.PowerConsumptionValue, MAX(UD.DateOfData) AS LastDate
FROM UnitsData UD
WHERE Unit.ID = UD.UnitID
GROUP BY UD.PowerConsumptionValue
ORDER BY LastDate DESC
) UD
But it returns to me only one unit and not the others.
Appreciate your kindly help.
Thanks
October 6, 2013 at 1:18 pm
The reason for getting just one row ist the CROSS APPLY: there are only data of UnitId2005 in the UnitsData table, eliminating the remaining rows.
I broke my approach down into three sections:
UD30 to get the data for question 4,
UD48 for questions 7 and 8 and
UD for question 5 and 6 (since the lates value might be older tha 30 days for a specific Unit).
SELECT
Unit.Name,
Unit.SerialNumber,
Unit.Location,
UD30.AverageValue,
UD.LastDate,
UD.LastValue,
CASE WHEN UD48.Cnt IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END AS LT48hrs,
CASE WHEN UD48.Cnt >=2 THEN 'TRUE' ELSE 'FALSE' END AS SameValue
FROM Units Unit
OUTER APPLY -- get the values for the 30 day average
(
SELECT TOP 1
UnitID,
AVG(PowerConsumptionValue) AS AverageValue
FROM UnitsData UD2
WHERE Unit.ID = UD2.UnitID
AND DateOfData>= DATEADD(dd,-30,getdate())
GROUP BY UnitID
) UD30
OUTER APPLY
(
SELECT x.UnitId, Max(cnt) AS Cnt
FROM
(
SELECT UnitId,PowerConsumptionValue, COUNT(*) AS Cnt
FROM UnitsData
WHERE DateOfData > DATEADD(hh,-48,GETDATE())
GROUP BY UnitId,PowerConsumptionValue
)x
WHERE x.UnitId = Unit.Id
GROUP BY x.UnitId
)UD48
OUTER APPLY
(
SELECT TOP 1
DateOfData AS LastDate,
PowerConsumptionValue AS LastValue
FROM UnitsData UD
WHERE UD.UnitId = Unit.Id
ORDER BY UD.DateOfData DESC, ID DESC
) UD
October 6, 2013 at 1:33 pm
Thanks a lot Lutz.
It works like a charm.
Appreciate your effort.
If I may I have another question:
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.
What shall I change in the script of yours to do that ?
I appreciate your effort !!! Thanks a lot 🙂 🙂
October 6, 2013 at 2:00 pm
Do you have sample data showing the "intermediate values" "step by step"?
I'm having a hard time to understand differences between adjecent values (adjecent in time), especially, since there are duplicate time values...
A few sample data would really help.
My approach most probably will be a CTE with ROW_NUMBER and a "self reference" to this cte with an offset of +1 to get the two values required for the "adjecent values differences".
October 7, 2013 at 12:00 am
Hi.
First of all - many thanks for your time. really appreciate it.
Attached is a script to build the UnitsData table with correct data
Attached an excel that shows the calculation steps
Thanks a lot !!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply