How to distinct 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 2005 Forums

Transact-SQL (2005)

How to distinct per day

Author

Topic

xtradi
Starting Member

7 Posts

Posted - 2010-03-04 : 21:09:46

i have table like this
NIK NAME JAM
04809 WIDODO 2008-09-17 10:57:59.000
04809 WIDODO 2008-09-17 12:09:47.000
04809 WIDODO 2008-09-17 18:30:40.000
04809 WIDODO 2008-09-17 18:33:52.000
04809 WIDODO 2008-09-18 06:37:17.000
04809 WIDODO 2008-09-18 16:47:38.000

i want to be able to output table like this
NIK NAME Date Min Max
04809 WIDODO 2008-09-17 10:57:59.000 18:33:52.000
04809 WIDODO 2008-09-18 06:37:17.000 16:47:38.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-04 : 22:08:19

[code]
select NIK, NAME, Date = dateadd(day, datediff(day, 0, JAM), 0), Min = min(JAM), Max = max(JAM)
from atable
group by NIK, NAME, dateadd(day, datediff(day, 0, JAM), 0)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

xtradi
Starting Member

7 Posts

Posted - 2010-03-04 : 23:20:05

sorry but i don't get any result with that query. Thanks for trying

and for further information the table name is 'vtes'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-04 : 23:49:35

change @vtes in the query to your actual table name


-- Sample Table
DECLARE @vtes TABLE
(
	[NIK]	varchar(5),
	[NAME] 	varchar(10),
	[JAM]	datetime
)

-- Sample Data
INSERT INTO @vtes
SELECT	'04809', 'WIDODO', '2008-09-17 10:57:59.000' UNION ALL
SELECT	'04809', 'WIDODO', '2008-09-17 12:09:47.000' UNION ALL
SELECT	'04809', 'WIDODO', '2008-09-17 18:30:40.000' UNION ALL
SELECT	'04809', 'WIDODO', '2008-09-17 18:33:52.000' UNION ALL
SELECT	'04809', 'WIDODO', '2008-09-18 06:37:17.000' UNION ALL
SELECT	'04809', 'WIDODO', '2008-09-18 16:47:38.000'

-- Query
SELECT NIK, NAME, Date = DATEADD(DAY, DATEDIFF(DAY, 0, JAM), 0), MIN = MIN(JAM), MAX = MAX(JAM)
FROM   @vtes
GROUP BY NIK, NAME, DATEADD(DAY, DATEDIFF(DAY, 0, JAM), 0)

-- Result
/*
NIK   NAME       Date                     Min                      Max
----- ---------- ------------------------ ------------------------ -----------------------
04809 WIDODO     2008-09-17 00:00:00.000  2008-09-17 10:57:59.000  2008-09-17 18:33:52.000
04809 WIDODO     2008-09-18 00:00:00.000  2008-09-18 06:37:17.000  2008-09-18 16:47:38.000

(2 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

xtradi
Starting Member

7 Posts

Posted - 2010-03-05 : 01:30:02

Thank you there is some error in 'vtes' so there is no result. Thank you for the help :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 09:55:42

quote:
Originally posted by xtradi

Thank you there is some error in 'vtes' so there is no result. Thank you for the help :)

what error? post the error you're getting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xtradi
Starting Member

7 Posts

Posted - 2010-03-05 : 17:48:14

error on my part not on your SQL and i already fixed it.

my problem is solved thanks to your query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources