Help with my first difficult query

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

General SQL Server Forums

New to SQL Server Programming

Help with my first difficult query

Author

Topic

maarten.del
Starting Member

17 Posts

Posted - 2009-08-06 : 08:50:13

Hi,

I want to make a query, i think only the sql-pro's can make. (And I'm not a pro)

I have a table like this but much bigger:

RtuTimestamp RtuName TagName MeasValue

2009-08-06 08:30:00.000 st241 ST241ATE01F 103623
2009-08-06 08:30:00.000 st144 ST144AWP01_AVG 29035
2009-08-06 08:30:00.000 st144 ST144ASA01_AVG 0
2009-08-06 08:30:00.000 st144 ST144AQD02_AVG 2303
2009-08-06 08:30:00.000 st144 ST144AQD01_AVG 65512,09
2009-08-06 08:30:00.000 st144 ST144AQA01_AVG 63425,13
2009-08-06 08:30:00.000 st144 ST144ADA01_AVG 376
2009-08-06 08:30:00.000 st143 ST143AWP01_AVG 5,010444
2009-08-06 08:30:00.000 st143 ST143ASA01_AVG 87
2009-08-06 08:30:00.000 st143 ST143AQD01_AVG 21,89999
2009-08-06 08:30:00.000 st143 ST143AQA01_AVG 18,12213
2009-08-06 08:30:00.000 st143 ST143ADA01_AVG 357,0222
2009-08-06 08:30:00.000 st141 ST141ASA01_AVG 1
2009-08-06 08:30:00.000 st141 ST141AQA01_AVG 0,5777778
2009-08-06 08:30:00.000 st141 ST141ADA11_AVG 6,780222

Now I want a query that gives me:

- RtuTimestamp, RtuName & Measvalue
- Latest Measvalue in time (based on RtuTimestamp) for every day only for the past 10 days and for every Tagname like '%ATE%' (see first record)

Can someone help me please?

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 09:32:12

Are you using SQL Server 2000, 2005 or 2008?

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

maarten.del
Starting Member

17 Posts

Posted - 2009-08-06 : 09:34:37

2005

I found already something

Select Distinct

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 09:52:10

[code]DECLARE @Sample TABLE
(
RtuTimestamp DATETIME,
RtuName VARCHAR(20),
TagName VARCHAR(20),
MeasValue DECIMAL(15, 8)
)

INSERT @Sample
SELECT '2009-08-06 08:30', 'st241', 'ST241ATE01F', 103623 UNION ALL
SELECT '2009-08-06 08:30', 'st144', 'ST144AWP01_AVG', 29035 UNION ALL
SELECT '2009-08-06 08:30', 'st144', 'ST144ASA01_AVG', 0 UNION ALL
SELECT '2009-08-06 08:30', 'st144', 'ST144AQD02_AVG', 2303 UNION ALL
SELECT '2009-08-06 08:30', 'st144', 'ST144AQD01_AVG', 65512.09 UNION ALL
SELECT '2009-08-06 08:30', 'st144', 'ST144AQA01_AVG', 63425.13 UNION ALL
SELECT '2009-08-06 08:30', 'st144', 'ST144ADA01_AVG', 376 UNION ALL
SELECT '2009-08-06 08:30', 'st143', 'ST143AWP01_AVG', 5.010444 UNION ALL
SELECT '2009-08-06 08:30', 'st143', 'ST143ASA01_AVG', 87 UNION ALL
SELECT '2009-08-06 08:30', 'st143', 'ST143AQD01_AVG', 21.89999 UNION ALL
SELECT '2009-08-06 08:30', 'st143', 'ST143AQA01_AVG', 18.12213 UNION ALL
SELECT '2009-08-06 08:30', 'st143', 'ST143ADA01_AVG', 357.0222 UNION ALL
SELECT '2009-08-06 08:30', 'st141', 'ST141ASA01_AVG', 1 UNION ALL
SELECT '2009-08-06 08:30', 'st141', 'ST141AQA01_AVG', 0.5777778 UNION ALL
SELECT '2009-08-06 08:30', 'st141', 'ST141ADA11_AVG', 6.780222

SELECT RtuTimeStamp,
RtuName,
MeasValue
FROM (
SELECT RtuTimeStamp,
RtuName,
MeasValue,
ROW_NUMBER() OVER (PARTITION BY DATEDIFF(DAY, 0, RtuTimeStamp) ORDER BY RtuTimeStamp DESC) AS recID
FROM @Sample
WHERE RtuTimeStamp >= DATEADD(DAY, DATEDIFF(DAY, 9, GETDATE()), 0)
AND RtuTimeStamp < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)
AND TagName LIKE '%ATE%'
) AS d
WHERE recID = 1[/code]

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

maarten.del
Starting Member

17 Posts

Posted - 2009-08-07 : 04:08:05

Hi,

Thanks for the query!

When i do this:

--------------------------------------------------------
select RtuTimeStamp,
TagName,
MeasValue
from (select RtuTimeStamp,
TagName,
MeasValue,
ROW_NUMBER() OVER (PARTITION BY DATEDIFF(DAY, 0, RtuTimeStamp) ORDER BY RtuTimeStamp DESC) AS recID
from Hislog
where RtuTimestamp >= DATEADD(Day, datediff(day, 9, getdate()), 0)
and RtuTimestamp < Dateadd(day, datediff(day, 0, Getdate()),1)
and TagName like '%ATE%'
) AS d
where recID = 1
--------------------------------------------------------

Then i get this:

2009-07-29 23:45:00.000 ST202ATE01F 5616
2009-07-30 23:45:00.000 ST202ATE01F 5745
2009-07-31 23:45:00.000 ST202ATE01F 5883
2009-08-01 23:45:00.000 ST202ATE01F 6024
2009-08-02 23:45:00.000 ST202ATE01F 6145
2009-08-03 23:45:00.000 ST203ATE01F 19834
2009-08-04 23:45:00.000 ST203ATE01F 20897
2009-08-05 23:45:00.000 ST202ATE01F 6635
2009-08-06 23:45:00.000 ST202ATE01F 6794
2009-08-07 08:45:00.000 ST203ATE01F 739

This is very fine, but i need something else

Now i have for every day, the latest value of a tagname at random like '%ATE%'

I want for every tagname like '%ATE%' every day the latest value. I think we need to turn it around. Now i have different days and multiple times ST202ATE01F.

Now i want for every ST202ATE01F (or other tagname like '%ATE%') every day the latest value.

I know its not very easy to understand (especially with my english) but the one who solves my query gets an imaginary statue.

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-07 : 04:23:04

You get the latest record for every day, containing %ATE%

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

maarten.del
Starting Member

17 Posts

Posted - 2009-08-07 : 04:37:10

I changed your code and we are getting a step closer to the result.

-----------------------------------------------------------
select RtuTimeStamp,
TagName,
MeasValue
from (
select RtuTimeStamp,
TagName,
MeasValue,
ROW_NUMBER() OVER (PARTITION BY TagName ORDER BY RtuTimeStamp DESC) AS recID
from Hislog
where RtuTimestamp >= DATEADD(Day, datediff(day, 9, getdate()), 0)
and RtuTimestamp < Dateadd(day, datediff(day, 1, Getdate()),1)
and TagName like '%ATE%'
) AS d
where recID = 1 order by Tagname

-----------------------------------------------------------

and i get this as result:

-----------------------------------------------------------

2009-08-06 23:45:00.000 ST202ATE01F 6794
2009-08-06 23:45:00.000 ST203ATE01F 495
2009-08-06 23:45:00.000 ST204ATE01F 188870
2009-08-06 23:45:00.000 ST205ATE01F 35659
2009-08-06 23:45:00.000 ST206ATE01F 61505
2009-08-06 06:15:00.000 ST206ATE01R 2581
2009-08-06 23:45:00.000 ST207ATE01F 193716
2009-08-06 23:45:00.000 ST208ATE01F 25370
2009-08-05 15:30:00.000 ST209ATE01F 13
2009-08-06 23:45:00.000 ST211ATE01F 1468
2009-08-06 23:45:00.000 ST212ATE01F 114535
2009-08-06 23:45:00.000 ST213ATE01F 65540
2009-08-06 23:45:00.000 ST214ATE01F 25845
2009-08-06 23:45:00.000 ST215ATE01F 34308
2009-08-06 23:45:00.000 ST216ATE01F 67874
2009-08-06 22:45:00.000 ST217ATE01F 12250
2009-08-06 23:45:00.000 ST218ATE01F 4443
2009-08-06 23:45:00.000 ST219ATE01R 65788
2009-08-06 23:45:00.000 ST220ATE01F 7602
2009-08-06 23:45:00.000 ST221ATE01F 3742
2009-08-06 23:45:00.000 ST222ATE01F 4459
2009-08-06 23:45:00.000 ST241ATE01F 104940
2009-08-06 23:45:00.000 ST242ATE01F 100088
2009-08-06 23:45:00.000 ST243ATE01F 53727
2009-08-06 23:45:00.000 ST244ATE01F 18778

-----------------------------------------------------------

Very good, but now i want the same result for 10 days before today. So the result must be 10 times longer then this query. You see that the lastest value it's almost always 23:45:00.000.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-07 : 04:43:14

ROW_NUMBER() OVER (PARTITION BY TagName, DATEDIFF(DAY, 0, RtuTimeStamp) ORDER BY RtuTimeStamp DESC) AS recID
from Hislog

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

maarten.del
Starting Member

17 Posts

Posted - 2009-08-07 : 04:49:15

OMG,

You solved my problem!!!

Where do you work?

Where do you want to get your imaginary statue?

Thanks alot!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-07 : 05:10:43

Mail your imaginary statue to:

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

I'm sure there is a mountain of them there already from other Lost Yaks!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-07 : 06:58:10

http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=N+56%C2%B004'39.26%22,+E+12%C2%B055'05.63%22&sll=37.0625,-95.677068&sspn=39.507908,76.552734&ie=UTF8&t=h&z=16

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources