CTE and joins and time schedule

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)

CTE and joins and time schedule

Author

Topic

johnyEU
Starting Member

2 Posts

Posted - 2009-08-20 : 16:47:38

How can I retrieve the following results?

user | date | login time | logoff time

I wish to print out every day in a given time period with matching login and logoff time which is written is table history. If there's a multiple login for same user and for same day then SQL should select minimal date for login and maximum date for logoff.

This is a user table:

CREATE TABLE [dbo].[User](
[id] [int] IDENTITY(1,1) NOT NULL,
[username] [varchar](25) NOT NULL,
[firstname] [nvarchar](50) NOT NULL,
[lastname] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This is a history table:

CREATE TABLE [dbo].[History](
[id] [int] IDENTITY(1,1) NOT NULL,
[user_id] [int] NOT NULL,
[login_time] [datetime] NOT NULL,
[logoff_time] [datetime] NOT NULL,
CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED
(
[id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

So far I can print out this, but for only one user:

date | login time | logoff time

This is a SQL (using CTE on SQL Server 2008):

with CTE(d) as
(
select d = convert(datetime, '20090801')
union all
select d = d + 1 from CTE where d < '20090831'
)

select
d as date,
(
select min(h.login_time)
from history h
where
h.user_id = 1 and
h.login_time >= d and
h.login_time < dateadd(d, 1, d)
) as login_time,
(
select max(h.logoff_time)
from history h
where
h.user_id = 1 and
h.logoff_time >= d and
h.logoff_time < dateadd(d, 1, d)
) as logoff_time
from CTE
option (maxrecursion 370)

I should somehow include this, but I'm stuck :-(

select *
from [user] u
where
exists (
select *
from history h
where
h.user_id = u.id and
h.login_time >= '20090801' and
h.login_time < '20090901'
)

Here's the example of data in history table:

id user_id login_time logoff_time
1 1 2009-08-20 06:00:01.000 2009-08-20 22:07:58.230
2 1 2009-08-20 22:10:15.137 2009-08-20 23:15:15.000
3 2 2009-08-20 22:08:20.103 2009-08-20 22:08:20.103
4 2 2009-08-20 22:08:23.340 2009-08-20 22:08:23.340
5 2 2009-08-21 14:30:30.120 2009-08-21 19:20:30.000

Desired output would be:

user date login_time logoff_time
john 2009-08-01 NULL NULL
john 2009-08-02 NULL NULL
...
john 2009-08-08 2009-08-20 06:00:01.000 2009-08-20 23:15:15.000
...
john 2009-08-31 NULL NULL
merry 2009-08-01 NULL NULL
merry 2009-08-02 NULL NULL
...
merry 2009-08-20 2009-08-20 22:08:20.103 2009-08-20 22:08:23.340
merry 2009-08-21 2009-08-21 14:30:30.120 2009-08-21 19:20:30.000
...
merry 2009-08-31 NULL NULL

johnyEU
Starting Member

2 Posts

Posted - 2009-08-20 : 17:37:43

Solved

Solution is here:
http://stackoverflow.com/questions/1308680/sql-server-2008-sql-question-regarding-cte-and-joins/1308976#1308976

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources