Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help in writing a query Expand / Collapse
Author
Message
Posted Sunday, October 6, 2013 11:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 2:36 AM
Points: 4, Visits: 8
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
Post #1501935
Posted Sunday, October 6, 2013 12:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 7,038, Visits: 12,951
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...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1501936
Posted Sunday, October 6, 2013 12:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 2:36 AM
Points: 4, Visits: 8
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


  Post Attachments 
database.txt (5 views, 17.88 KB)
Post #1501940
Posted Sunday, October 6, 2013 1:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 7,038, Visits: 12,951
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





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1501950
Posted Sunday, October 6, 2013 1:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 2:36 AM
Points: 4, Visits: 8
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 :) :)
Post #1501951
Posted Sunday, October 6, 2013 2:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 7,038, Visits: 12,951
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".




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1501954
Posted Monday, October 7, 2013 12:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 2:36 AM
Points: 4, Visits: 8
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 !!


  Post Attachments 
database1.txt (2 views, 18.58 KB)
1.xlsx (4 views, 12.17 KB)
Post #1501990
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse