Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help in writing a query


Need help in writing a query

Author
Message
ido 83432
ido 83432
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
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
ido 83432
ido 83432
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Attachments
database.txt (5 views, 17.00 KB)
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
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
ido 83432
ido 83432
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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 Smile Smile
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
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
ido 83432
ido 83432
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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 !!
Attachments
database1.txt (2 views, 18.00 KB)
1.xlsx (4 views, 12.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search