records within last 2 hours between 9-5

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)

records within last 2 hours between 9-5

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-30 : 05:09:38

i have table

ref

refid
refcode

and table imageentries
id
date
refcode
disabled

I need to query all refcodes (that are not disabled=1) and are not in imageentries within the last 2 hours

the trick is it should only count last 2 hours if it's between 9 & 5 - otherwise it should be 3-5 of the last day

can someone help me with this

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-30 : 07:07:32

Here is an idea of how to do the 9-5 query, I didn't understand what you want if it is outside this time, but it shouldn't be too hard for you to do with this query.


If (select datepart(hh,getdate())) >= 9 and (select datepart(hh,getdate())) < 17
begin
	select r.refcode from ref r
	left join imageentries ie
		on ie.refcode = r.refcode
	where ie.refcode is null
	and r.[disabled] <> 1
	and r.[date] > DATEADD(HH,-2,ie.[date])
end
else
begin
....
end

EDIT: actually thinking about it and reading your post further, how would you know a post was made within a certain time if there is not a record in the imageentries table?!? You need a date in the ref table to do this properly along with a disabled field!

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-30 : 08:06:17

there is a date field in the imageentries table so I know by the date and refcode

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-30 : 08:08:48

No, you don't, not if a refcode doesn't exist in the imageentries table as any query relying on the refcode will not join to the imageentries table as there is simply no existing record for you to look at to see the date.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-30 : 08:13:47

refcode is always in the imageentries table

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-30 : 09:36:28

How when you are looking for refcodes that are not in the imageentries table?!?

If it really is the case that they will always exist, then the code should be:


If (select datepart(hh,getdate())) >= 9 and (select datepart(hh,getdate())) < 17
begin
	select r.refcode from ref r
	inner join imageentries ie
		on ie.refcode = r.refcode
	where ie.[disabled] <> 1
	and ie.[date] > DATEADD(HH,-2,ie.[date])
end
else
begin
....
end

Still not sure about outside these times as you haven't explained that bit clearly enough still, but you can work it out yourself I guess.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-30 : 13:45:41

quote:
Originally posted by RickD

How when you are looking for refcodes that are not in the imageentries table?!?

If it really is the case that they will always exist, then the code should be:

If (select datepart(hh,getdate())) >= 9 and (select datepart(hh,getdate())) < 17
begin
select r.refcode from ref r
inner join imageentries ie
on ie.refcode = r.refcode
where ie.[disabled] <> 1
and ie.[date] > DATEADD(HH,-2,ie.[date])
end
else
begin
....
end

Still not sure about outside these times as you haven't explained that bit clearly enough still, but you can work it out yourself I guess.

When would this condition not be true, except when ie.[date] is null?

and ie.[date] > DATEADD(HH,-2,ie.[date])

Do you really mean this?

and ie.[date] > DATEADD(HH,-2,getdate())

CODO ERGO SUM

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-31 : 04:15:24

Whoops, didn't spot that, but yes I did.

Thanks MVJ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources