"interval calculation" - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

"interval calculation"

Author

Topic

attman
Starting Member

16 Posts

Posted - 2010-01-31 : 09:54:56

Do these procedures sql is done? or another programming language to do it? (such as c # or vb.net) I am using asp.net platform.

Mytable1
ID--- Date--- DeviceID--- Values---
1--- 01.01.2010--- Device01--- 10
2--- 01.01.2010--- Device02--- 9
3--- 01.01.2010--- Device03--- 17
4--- 31.01.2010--- Device01--- 15
5--- 31.01.2010--- Device02--- 91
6--- 31.01.2010--- Device03--- 35

MyQuery1
GetLastRecords

GetLastRecords query the results:
ID--- Date--- DeviceID--- Values---
4--- 31.01.2010--- Device01--- 15
5--- 31.01.2010--- Device02--- 91
6--- 31.01.2010--- Device03--- 35
above the grid in a web page showing the results of GetLastRecords am.
I want to do the following from this grid:

Mytable2
ID--- DeviceID--- First_Date--- Last_Date--- First_Value--- Last_Value---
1--- Device01--- 01.01.2010--- 31.01.2010--- 10--- 15---
2--- Device02--- 01.01.2010--- 31.01.2010--- 9--- 91---
3--- Device03--- 01.01.2010--- 31.01.2010--- 17--- 35---
-
Criteria:
1-MyTable2 does not have value if the device: Results from GetLastRecords query, as well as the first and last value to be added.
2- If the value has been entered previously for the device in MyTbale2: Last_Date and Last_Value and values of the device in Mytable2, the new record to be added: First_Date and First_Value will be values. GetLastRecords query results Last_Date , Last_Value as to be added.

I apologize for my language mistakes.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-01 : 02:48:46

[code]SELECT ROW_NUMBER() OVER (ORDER BY DeviceID) AS ID,
DeviceID,
MAX(CASE WHEN fID = 1 THEN [Date] ELSE NULL END) AS FirstDate,
MAX(CASE WHEN lID = 1 THEN [Date] ELSE NULL END) AS LastDate,
MAX(CASE WHEN fID = 1 THEN [Values] ELSE NULL END) AS FirstValue,
MAX(CASE WHEN lID = 1 THEN [Values] ELSE NULL END) AS LastValue
FROM (
SELECT [Date],
DeviceID,
[Values],
ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY [Date]) AS fID,
ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY [Date] DESC) AS lID
FROM MyTable1
) AS d
WHERE 1 IN (fID, lID)
GROUP BY DeviceID
ORDER BY DeviceID[/code]

N 56°04'39.26"
E 12°55'05.63"

attman
Starting Member

16 Posts

Posted - 2010-02-01 : 11:09:25

Thanks Peso
but this is not wanted. I get it already. hourly data is added to the system. I would take the last value to the web page. this page: month or months for their first and last values I want to transfer to another table.
if the target table records associated with the device does not exist, the value in these pages as both the first and last values should be added. (target table = myTable2). If you have already registered a previous final value of the device as a new line will be added to the initial value, the values in the web page will be added as the final value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 12:01:49

so can you specify what solution are you exactly looking at?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources