Constantly polling SQL query to day and time for a weekly report

Question

Post reply

Constantly polling SQL query to day and time for a weekly report

brett.y

Ten Centuries

Points: 1310
More actions
December 27, 2010 at 10:59 am

#242801

Hi,
Complete beginner to SQL but what I am after is a query for a SCADA system. The "WHERE" clause needs to get data from a day and time to now but roll over when the start day comes around. So if the chosen start day is Monday 9:00 am the query gets data from the last Monday until now, i.e data is never more than a week old, on a Tuesday the data brought back would be for around 24 hours "ish", on a Friday five days of data. On a Monday at 11 am 2 hours of data would be queried. Hope this makes sense. Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Look at thiis posting by Lynn Pettis, you will most likely find the T-SQL you require.

https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

brett.y Ten Centuries Points: 1310 More actions · Answer 2

Ran these through SQL server management studio and it works as it should so I guess I can then do further DATEADD's etc to fine tune the date to precisely where I want it. Thanks massively for the help.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Your welcome ... if you come up with what you think is a good solution please post it in the forum so others may benefit

Again - thank for you response .. glad to know I was of some slight assistance

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

brett.y Ten Centuries Points: 1310 More actions · Answer 4

After looking at a few examples I have eventually come up with this:

set datefirst 5

declare @thisdate datetime

declare @fromdate datetime

select @thisdate = getdate()

if convert(varchar,getdate(),108)< '11:59:59' and datepart(dw,getdate())= '7'

begin

select @fromdate = dateadd(dd,0,datediff(dd,0,@thisdate)) - (datepart(dw,@thisdate)%7)-7

select dateadd(hh,12,@fromdate) as Result

end

else

begin

select @fromdate = dateadd(dd,0,datediff(dd,0,@thisdate)) - (datepart(dw,@thisdate)%7)

select dateadd(hh,12,@fromdate) as Result

end

I have found this gives a date for the last Thursday at 12:00. The if else clause prevents a report being ran on a Thursday morning trying to start from 12:00 that day onwards (into the future) but instead looks back to the last Thursday.