time related query - 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

General SQL Server Forums

New to SQL Server Programming

time related query

Author

Topic

bharti22
Starting Member

5 Posts

Posted - 2014-07-15 : 07:53:56

Hello,

I need to write down a sql query wherein in one particular day(user will enter manually), i need to find out a 15 minutes slot wherein purchase order's created or updated are the highest.
i.e. out of 96 slots(15 minute slot each)-I need to find the slot which has maximum number of Purchase orders created or updated.

Looking for an early response.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-15 : 08:21:20

something like this:

SELECT TOP (1) * FROM
(
	SELECT DATEADD(mi,DATEDIFF(mi,0,YourTimeColumn)/15*15,0) AS Slot, COUNT(*) N
	FROM YourTable
	GROUP BY DATEADD(mi,DATEDIFF(mi,0,YourTimeColumn)/15*15,0)
)s ORDER BY N DESC

bharti22
Starting Member

5 Posts

Posted - 2014-07-15 : 10:59:13

Hey,

Thanks but would this take out the slot which has maximum Purchase orders created /updated in that particular 15 minutes?

Thanks!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-15 : 11:55:27

quote:
Originally posted by bharti22

Hey,

Thanks but would this take out the slot which has maximum Purchase orders created /updated in that particular 15 minutes?

Thanks!

It should retrieve the slot which has the maximum Purchase orders created/updated. Try running the query to see what it returns.

bharti22
Starting Member

5 Posts

Posted - 2014-07-15 : 22:53:58

It is currently throwing out an error in From keyword and to be more specific-my table name is po_headers_all and Purchase prder number lies in column segment1 and we can get the last update date from column last_update_date. Also, can you let me know what is N in the query you gave?

bharti22
Starting Member

5 Posts

Posted - 2014-07-15 : 23:18:52

Also, it is not allowing me to use dateadd as I am running this in TOAD
:(

bharti22
Starting Member

5 Posts

Posted - 2014-07-15 : 23:24:25

Currently I am using below query to get the slots:
(select trunc(sysdate) + ((rownum-1)*15/60/24) slot
from dual
connect by rownum <= 96)

Now leftover work is to take out the slot which has maximum PO's created and updated.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-16 : 06:22:04

Looks like you are on an Oracle server. This forum is for Microsoft SQL Server and the queries I posted are for SQL Server. You might try asking in an Oracle forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources