SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL GETDATE() PROBLEM
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Terry2688
Starting Member

USA
3 Posts

Posted - 12/13/2012 :  15:29:42  Show Profile  Reply with Quote
CAN THIS QUERY BE MADE TO RUN ANYTIME WITH ACCURATE DATA RESULTS?


/* Data is collected at 15 min. intervals */
/* Query must be ran at 11:59pm because of the getdate() command */
/* If not the values in the first and last row will not be accurate */



SELECT
CONVERT(VARCHAR, dateadd(hour, DATEDIFF (HH, GETUTCDATE(), GETDATE()),[UTCDateTime]), 101) AS 'DATE'
,CAST (MAX(ActualValue) as numeric(5,2)) AS 'MAX OAT'
,CAST (MIN(ActualValue) as numeric(5,2)) AS 'MIN OAT'
,CAST(AVG(ActualValue) as numeric(5,2)) AS 'AVG OAT'

FROM
[JCIHistorianDB].[dbo].[tblPoint],[JCIHistorianDB].[dbo].[tblActualValueFloat]

WHere
[PointName]='gadmadx1:NAE77BREF-PE01/FC-1.77_1FEC06.OA_T.Present Value' and
PointSliceId=PointId and [UTCDateTime] > getdate()-30

GROUP BY CONVERT(VARCHAR, dateadd(hour, DATEDIFF (HH, GETUTCDATE(), GETDATE()),[UTCDateTime]), 101)

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/13/2012 :  20:28:44  Show Profile  Reply with Quote
quote:
Originally posted by Terry2688

CAN THIS QUERY BE MADE TO RUN ANYTIME WITH ACCURATE DATA RESULTS?
<snip>
The short answer is Yes. However, I can't give you any options because I don't know what accurate means in this scenario.

What are your requirements?

If you tell us what you want we can certianly help you.

Edited by - Lamprey on 12/13/2012 20:29:30
Go to Top of Page

Terry2688
Starting Member

USA
3 Posts

Posted - 12/13/2012 :  22:09:14  Show Profile  Reply with Quote
Data is entered every 15 minutes in the table 96 values total. If I run this query early in the day before all the data is entered the Avg,Max and Min results for my 1st and 30th row are incorrect.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 12/13/2012 :  22:22:43  Show Profile  Reply with Quote
can you post some sample data to illustrate this ?

please also include the expected result


KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/14/2012 :  01:36:20  Show Profile  Reply with Quote
quote:
Originally posted by Terry2688

Data is entered every 15 minutes in the table 96 values total. If I run this query early in the day before all the data is entered the Avg,Max and Min results for my 1st and 30th row are incorrect.


Why not add this logic as a job? in job add the step to check max date value in table to see if it has crossed the day and if yes, add logic to find aggregates by putting filter for the elapsed day range



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Terry2688
Starting Member

USA
3 Posts

Posted - 12/14/2012 :  10:07:12  Show Profile  Reply with Quote
visakh16,
Can you give an example of the logic?


Lamprey and khtan,
Here is the result from a getdate()-5 vs getdate()-7.
The query was executed earily today.
I would like for the current day not to show in the result until all data for that day has been entered.
The result for 12/09/2012 are different and I think it's because of the current time with the getdate() command.


--- DATE -- MAX - MIN - AVG
12/09/2012 81.19 65.93 73.44
12/10/2012 73.24 45.91 54.54
12/11/2012 63.80 41.75 50.30
12/12/2012 72.62 44.70 53.74
12/13/2012 76.78 45.56 57.59
12/14/2012 60.13 58.00 58.98

--- DATE -- MAX - MIN - AVG
12/07/2012 83.88 63.21 71.94
12/08/2012 86.82 68.06 73.78
12/09/2012 81.19 65.93 72.96
12/10/2012 73.24 45.91 54.54
12/11/2012 63.80 41.75 50.30
12/12/2012 72.62 44.70 53.74
12/13/2012 76.78 45.56 57.59
12/14/2012 60.13 58.00 58.98



Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 12/14/2012 :  10:28:32  Show Profile  Reply with Quote
add the following condition and it will exclude current day

	[UTCDateTime]	< dateadd(day, datediff(day, 0, getdate()), 0)


also change this

[UTCDateTime] > getdate()-30


to
[UTCDateTime] > dateadd(day, datediff(day, 0, getdate()), -30)


do note that getdate() will return the current date & time and your UTCDateTime contain datetime. Doing a getdate() - 30 will give you a date that 30 days before but at current time ! That's why your 1st record is wrong


KH
Time is always against us


Edited by - khtan on 12/14/2012 10:33:45
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/14/2012 :  12:54:26  Show Profile  Reply with Quote
to add to Khtan's post, if you specify the date range (excluding today) you should get what you want (also you may or may not want to use the SYSUTCDATETIME function instead of GETDATE, not sure what your requriements are):
WHERE
    [UTCDateTime] >= DATEADD(DAY, DATEDIFF(DAY, 0, SYSUTCDATETIME()), -30)
    AND [UTCDateTime] < DATEADD(DAY, DATEDIFF(DAY, 0, SYSUTCDATETIME()), 0)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/14/2012 :  14:20:31  Show Profile  Reply with Quote
quote:
Originally posted by Terry2688

visakh16,
Can you give an example of the logic?


Lamprey and khtan,
Here is the result from a getdate()-5 vs getdate()-7.
The query was executed earily today.
I would like for the current day not to show in the result until all data for that day has been entered.
The result for 12/09/2012 are different and I think it's because of the current time with the getdate() command.


--- DATE -- MAX - MIN - AVG
12/09/2012 81.19 65.93 73.44
12/10/2012 73.24 45.91 54.54
12/11/2012 63.80 41.75 50.30
12/12/2012 72.62 44.70 53.74
12/13/2012 76.78 45.56 57.59
12/14/2012 60.13 58.00 58.98

--- DATE -- MAX - MIN - AVG
12/07/2012 83.88 63.21 71.94
12/08/2012 86.82 68.06 73.78
12/09/2012 81.19 65.93 72.96
12/10/2012 73.24 45.91 54.54
12/11/2012 63.80 41.75 50.30
12/12/2012 72.62 44.70 53.74
12/13/2012 76.78 45.56 57.59
12/14/2012 60.13 58.00 58.98






see this to understand how to add date range logic

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000