Query By Time - 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 2000 Forums

SQL Server Development (2000)

Query By Time

Author

Topic

vegasvic
Starting Member

4 Posts

Posted - 2005-10-13 : 15:28:02

Hello
I need to have a query search for records that are time stamped 30 minutes earlier than the current system time.

here is the query I am using

SELECT item_id,item_desc,sum(print_quantity) as qty
FROM dbo.pathguide_pick_ticket_view
WHERE (print_date >= GETDATE() - 1) AND (location_id = 100004) AND (last_maintained_by = 'amazon_picks') OR
(last_maintained_by = 'ecomm_picks')
group by item_id ,item_desc
order by item_id asc

I am using this query in a VB6 program the will print a report of things printed in the last 30 min.

Ideally I would like to pass a variable to set the time to diffrent intervals.

I just need to know how to parse the date out

thanks
Vgeas Vic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-13 : 16:26:49

Where did you come up with
WHERE (print_date >= GETDATE() - 1)

That will select items printed in the last 24 hours.

Try this:
WHERE (print_date >= dateadd(minute,-30,GETDATE()) )

CODO ERGO SUM

vegasvic
Starting Member

4 Posts

Posted - 2005-10-13 : 16:42:27

the original request was for a 24 hour period. then they changed there minds and mow want every 30 min....I tried your suggestion but it did not work

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-13 : 18:05:34

Well, dateadd(minute,-30,GETDATE()) returns the time 30 minutes before the current system time.

Maybe your print_date column only contains the date without the time. If that is the case, I don't see hou you are going to find what you need.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-14 : 00:52:41

If print_date is DateTime Column, then try this

DateDiff(minute,print_date,GetDate())<=30

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-14 : 01:12:53

quote:
Originally posted by madhivanan

If print_date is DateTime Column, then try this

DateDiff(minute,print_date,GetDate())<=30

Madhivanan

Failing to plan is Planning to fail

Michael's "WHERE (print_date >= dateadd(minute,-30,GETDATE()) )" is logically equivalent to this, but has the advantage of being able to use an index on the print_date column. By embedding print_date in the DateDiff function the ability to use an index is lost.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-14 : 01:29:33

Thanks blindman

Madhivanan

Failing to plan is Planning to fail

vegasvic
Starting Member

4 Posts

Posted - 2005-10-14 : 08:22:06

thanks for all the input guys...the column is a datetime datatype.

These both worked (yesterday I had a brain fade when I thought they did not, long day of staring at data)
until i added the
and (last_maintained_by = 'amazon_picks') OR
(last_maintained_by = 'ecomm_picks')to the where clause then I recived results dates from yesterday

this works i only get records from today within the last 30 min

SELECT item_id,item_desc,sum(print_quantity) as qty,print_date,last_maintained_by
FROM dbo.pathguide_pick_ticket_view
WHERE (print_date >= dateadd(minute,-30,GETDATE()) )
AND (location_id = 100004)
group by item_id ,item_desc,print_date,last_maintained_by
order by qty desc

2005-10-14 08:21:17.000
2005-10-14 08:20:30.000
2005-10-14 08:21:10.000
2005-10-14 08:20:13.000
2005-10-14 08:20:19.000
2005-10-14 08:20:36.000
2005-10-14 08:20:24.000
2005-10-14 08:20:43.000
2005-10-14 08:21:18.000
2005-10-14 08:20:10.000
2005-10-14 08:20:15.000
2005-10-14 08:21:33.000

this returns records from yesterday

SELECT item_id,item_desc,sum(print_quantity) as qty,print_date,last_maintained_by
FROM dbo.pathguide_pick_ticket_view
WHERE (print_date >= dateadd(minute,-30,GETDATE()) )
AND (location_id = 100004) and
(last_maintained_by = 'amazon_picks') OR
(last_maintained_by = 'ecomm_picks')
group by item_id ,item_desc,print_date,last_maintained_by
order by qty desc

2005-10-13 09:44:19.000
2005-10-13 09:44:32.000
2005-10-13 09:44:28.000
2005-10-13 09:44:15.000
2005-10-13 09:44:41.000
2005-10-13 09:44:49.000
2005-10-13 09:44:23.000
2005-10-13 09:44:37.000
2005-10-13 09:44:45.000
2005-10-13 09:44:53.000
2005-10-13 16:36:14.000
2005-10-13 16:36:34.000
2005-10-13 16:36:10.000
2005-10-13 16:36:24.000
2005-10-13 16:36:02.000
2005-10-13 16:36:19.000
2005-10-13 16:36:38.000

this is running against a view just as an fyi

thanks for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources