grouping help - 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)

grouping help

Author

Topic

seba588587
Starting Member

1 Post

Posted - 2010-08-23 : 04:45:34

Hello,
I have a following table

CREATE TABLE [dbo].[Events]
(
[id] [int] NOT NULL,
[date] [datetime] NOT NULL,
[deleted] [bit] NOT NULL
)

and rows

INSERT INTO [Events] VALUES (1, '2010-08-23 01:00:00', 0)
INSERT INTO [Events] VALUES (2, '2010-08-23 01:22:00', 0)
INSERT INTO [Events] VALUES (3, '2010-08-23 02:30:00', 1)
INSERT INTO [Events] VALUES (4, '2010-08-23 10:00:00', 0)
INSERT INTO [Events] VALUES (5, '2010-08-23 10:01:00', 0)
INSERT INTO [Events] VALUES (6, '2010-08-23 11:14:00', 0)

INSERT INTO [Events] VALUES (7, '2010-08-24 06:34:00', 0)
INSERT INTO [Events] VALUES (8, '2010-08-24 07:09:00', 0)
INSERT INTO [Events] VALUES (9, '2010-08-24 09:59:00', 0)
INSERT INTO [Events] VALUES (10, '2010-08-24 10:00:00', 1)
INSERT INTO [Events] VALUES (11, '2010-08-24 10:01:00', 0)
INSERT INTO [Events] VALUES (12, '2010-08-24 18:50:23', 0)

the table contains identity, info about a date and it it is deleted. More over I have specified a time 10:00 (hour=10, minute=0, seconds=0).

I would like to write sql query to select id and date of one event per day that fulfills conditions
1. not deleted
2. time is equal or greater to 10:00.
Requested output of the query would be:
4 '2010-08-23 10:00:00'
11 '2010-08-24 10:01:00'

I have try to use group by date but it turned out that I can not select id because it is not used in group by clasue.
CAST(
CAST(DATEPART(year, @date) AS varchar)
+ '-'
+ CAST(DATEPART(month, @date) AS varchar)
+ '-'
+ CAST(DATEPART(day, @date) AS varchar)
+ ' 0:0:0'
AS datetime);

Do you have an idea how to achieve that?
Regards,

Sebastian

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-23 : 04:59:29

select columns from your_table
where [deleted]=0 and datepart(hour,[date])>=10

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 05:06:35

quote:
Originally posted by madhivanan

select columns from your_table
where [deleted]=0 and datepart(hour,[date])>=10

Madhivanan

Failing to plan is Planning to fail

this will give all undeleted records that happened after 10
I think what OP wants is


SELECT t1.[id],
t1.[date],
t1.[deleted]
FROM (SELECT DISTINCT DATEADD(dd,DATEDIFF(dd,0,[date]),'10:00') AS [date]
      FROM Table)t
CROSS APPLY (SELECT TOP 1 [id],
[date],
[deleted]
FROM Table
WHERE [date]>=t.[date]
AND [deleted]=0
ORDER BY [date]
)t1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources