First X records per day - 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 2008 Forums

Transact-SQL (2008)

First X records per day

Author

Topic

mvdongen
Starting Member

4 Posts

Posted - 2010-09-27 : 11:19:52

Hi everyone,

I'm new to this forum but I've read a lot of good posts on here already. Unfortunatly those haven't solved the issue I'm dealing with, so it's time to start my own thread.

I have a table with a huge ammount of tasks that are performed on a specific time each day (about 65k records). Now I want to be able to display only the first (let's say 5) records of each day. What I've come up with so far is:

SELECT * FROM tblTask_log t1
WHERE [task_log_id] IN
(
SELECT TOP 1 [task_log_id] FROM tblTask_log t2
WHERE t1.[task_log_start] = t2.[task_log_start]
order by [task_log_start] DESC
)
order by t1.[task_log_start]

But this still shows all the records of each day, so I'm clearly doing something wrong. What might also make things more complicated is the fact that date and time are in the same field, like this:

13-9-2010 7:00:00
13-9-2010 8:00:00
15-9-2010 7:00:00
15-9-2010 8:00:00
17-9-2010 8:00:00
17-9-2010 8:00:00
18-9-2010 7:00:00
18-9-2010 8:00:00

So when I say I want the first 5 of each day, I mean the earliest 5 of every day.

I really hope you guys can help me out with this one. Please let me know if you need any additional information.

Thanks in advance.

Marvin

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-27 : 11:36:13

Well this would be slow but....



SET DATEFORMAT dmy

DECLARE @tblTaskLog TABLE (
	[task_log_start] DATETIME
	, [description] VARCHAR(512)
	)

INSERT @tblTaskLog ([task_Log_start], [description])
      SELECT '13-9-2010 7:00:00', 'boo'
UNION SELECT '13-9-2010 8:00:00', 'stu'
UNION SELECT '15-9-2010 7:00:00', 'crew'
UNION SELECT '15-9-2010 8:00:00', 'aloo'
UNION SELECT '17-9-2010 8:00:00', 'shoo'
UNION SELECT '17-9-2010 8:00:00', 'woo'
UNION SELECT '18-9-2010 7:00:00', 'bar'
UNION SELECT '18-9-2010 8:00:00', 'foo'


SELECT
	dy.[Day]
	, tsk.[Log_Entry_Date]
	, tsk.[Log_Entry]
FROM
	(
	SELECT DISTINCT
		DATEADD(DAY, DATEDIFF(DAY, [task_log_start], '20100101'), '20100101') AS [Day]
	FROM
		@tblTaskLog
	)
	AS dy

	CROSS APPLY (
		SELECT TOP 5
			[task_Log_start] AS [Log_Entry_Date]
			, [description] AS [Log_Entry]
		FROM
			@tblTaskLog
		WHERE
			DATEADD(DAY, DATEDIFF(DAY, [task_log_start], '20100101'), '20100101') = dy.[Day]
		ORDER BY
			[task_Log_start] ASC
		)
		AS tsk

It would help if you had a calender table of dates. Then you wouldn't need to do the first derived table -- you'd just link to the calender table.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-27 : 13:47:53

Another solution ...

DECLARE @tblTaskLog TABLE (
[task_log_start] DATETIME
, [description] VARCHAR(512)
)

INSERT @tblTaskLog ([task_Log_start], [description])
SELECT '13-9-2010 7:00:00', 'boo'
UNION SELECT '13-9-2010 8:00:00', 'stu'
UNION SELECT '15-9-2010 7:00:00', 'crew'
UNION SELECT '15-9-2010 8:00:00', 'aloo'
UNION SELECT '17-9-2010 9:00:00', 'shoo'
UNION SELECT '17-9-2010 8:00:00', 'woo'
UNION SELECT '18-9-2010 7:00:00', 'bar'
UNION SELECT '18-9-2010 8:00:00', 'foo'
-- borrow from Transact Charlie

;with temp as(select *, rn = row_number() over(partition by convert(varchar(10), task_Log_start, 101) order by task_Log_start) from @tblTaskLog)
select task_Log_start, [description] from temp where rn <= 5 order by task_Log_start desc

mvdongen
Starting Member

4 Posts

Posted - 2010-09-27 : 14:00:56

Thanks for both your replies!

If I interpret your code correctly you are both hardcoding the dates I posted. But these dates change constantly so query needs to be flexible. The query will also be run very often, so if you can suggest a small change to the database to make things a lot easier, that would be great. Does the problem lie with the combination of both date and time in the same field?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-27 : 14:35:46

namman's @tblTaskLog was just so he had some data to work with, based on our sample. If you replace @tblTaksLog with the name of your actual table, it should work just fine. If you still need help, post your full requirements.

Jim

Everyday I learn something that somebody else already knew

mvdongen
Starting Member

4 Posts

Posted - 2010-10-01 : 09:30:19

Just to make things for difficult (*sigh*), our application runs on both SQL and Access databases. Both queries you guys provided don't seem to work with Access. Any new ideas would really be appreciated.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-01 : 10:01:09

huh?

Where's the data stored?

Best advice -- migrate the access database to sqlserver. (Though I know this will be very hard).

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mvdongen
Starting Member

4 Posts

Posted - 2010-10-01 : 10:12:21

I mean the application is designed to run on -either- SQL or Access, depending on the customers needs. Is there no way to achieve this in Access-compatible SQL?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-01 : 10:22:15

ah I see.

Not sure but I don't think there would be a nice performant way to do it that is common to both. Access supports a very restrictive subset of the TSQL that sqlserver supports.

There are some access experts on here though. Maybe one of them can help.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources