Help w/ a SQL Query

In a sales software system that I built, a sales person belongs to a
department within the company. However, during their employment with the
company, they might work in several departments. So I implemented department
logging in the system.
CREATE TABLE [USER_DepartmentLog] (
[UserId] [int] NOT NULL ,
[DepartmentId] [int] NOT NULL ,
[DateTimeStarted] [datetime] NOT NULL ,
[DateTimeEnded] [datetime] NULL
)
GO
INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
DateTimeEnded) VALUES (33, 13, '06/24/2005', '05/01/2005')
INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
DateTimeEnded) VALUES (33, 12, '05/01/2005', NULL)
In this example, User 33 worked in Department 13 from 06/24/2005 to
05/01/2005
On 05/01/2005, they were transferred to Department 12
Each sales person books Jobs:
CREATE TABLE [Job] (
[Id] [int] NOT NULL,
[UserId] [int] NOT NULL,
[DateCreated] [datetime] NOT NULL
)
GO
INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(1, 33, '05/12/2005')
INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(2, 33, '05/01/2005')
INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(3, 33, '04/28/2005')
INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(4, 33, '04/05/2005')
I'd like to run a query that would return a list of jobs while at the same
time displaying the department that the sales person was in at the time they
entered the jobs. Here's what I would like my returned data to look like:
UserId JobId DepartmentId DateCreated
33 4 13 04/05/2005
33 3 13 04/28/2005
33 2 12 05/01/2005
33 1 12 05/12/2005
If a job was created on a date that the user transitioned from one
department to another, e.g. Job 2 was created on 05/01/2005 when the user
left Department 12 for Department 12, I'd like the job to appear under the
new Department.
Thank YouCORRECTION.
I meant '06/24/2002', not '06/24/2005'
INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
DateTimeEnded) VALUES (33, 13, '06/24/2002', '05/01/2005')
INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
DateTimeEnded) VALUES (33, 12, '05/01/2005', NULL)
In this example, User 33 worked in Department 13 from 06/24/2002 to
05/01/2005
On 05/01/2005, they were transferred to Department 12
"George Durzi" <gdurzi@.hotmail.com> wrote in message
news:%23wnB$nIXFHA.3340@.TK2MSFTNGP15.phx.gbl...
> In a sales software system that I built, a sales person belongs to a
> department within the company. However, during their employment with the
> company, they might work in several departments. So I implemented
> department logging in the system.
> CREATE TABLE [USER_DepartmentLog] (
> [UserId] [int] NOT NULL ,
> [DepartmentId] [int] NOT NULL ,
> [DateTimeStarted] [datetime] NOT NULL ,
> [DateTimeEnded] [datetime] NULL
> )
> GO
> INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
> DateTimeEnded) VALUES (33, 13, '06/24/2005', '05/01/2005')
> INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
> DateTimeEnded) VALUES (33, 12, '05/01/2005', NULL)
> In this example, User 33 worked in Department 13 from 06/24/2005 to
> 05/01/2005
> On 05/01/2005, they were transferred to Department 12
> Each sales person books Jobs:
> CREATE TABLE [Job] (
> [Id] [int] NOT NULL,
> [UserId] [int] NOT NULL,
> [DateCreated] [datetime] NOT NULL
> )
> GO
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(1, 33,
> '05/12/2005')
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(2, 33,
> '05/01/2005')
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(3, 33,
> '04/28/2005')
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(4, 33,
> '04/05/2005')
> I'd like to run a query that would return a list of jobs while at the same
> time displaying the department that the sales person was in at the time
> they entered the jobs. Here's what I would like my returned data to look
> like:
> UserId JobId DepartmentId DateCreated
> 33 4 13 04/05/2005
> 33 3 13 04/28/2005
> 33 2 12 05/01/2005
> 33 1 12 05/12/2005
>
> If a job was created on a date that the user transitioned from one
> department to another, e.g. Job 2 was created on 05/01/2005 when the user
> left Department 12 for Department 12, I'd like the job to appear under the
> new Department.
> Thank You
>|||Hi, George
Thank you for providing DDL, sample data and expected results. Try this
query:
SELECT Job.UserID, Job.ID as JobID, DepartmentID, DateCreated
FROM Job INNER JOIN USER_DepartmentLog
ON Job.UserID=USER_DepartmentLog.UserID
AND DateCreated>=DateTimeStarted
AND (DateCreated<DateTimeEnded OR DateTimeEnded IS NULL)
Razvan|||Try,
select
j.UserId,
j.[Id] as job_id,
u.DepartmentId,
j.DateCreated
from
job as j
inner join
USER_DepartmentLog as u
on j.userid = u.userid
and j.datecreated between u.DateTimeStarted and isnull(u.DateTimeEnded,
j.datecreated)
and j.datecreated != isnull(u.DateTimeEnded, dateadd(day, -1, j.datecreated)
)
order by
j.userid, j.datecreated
go
But better if you add a constraint to not allow a user to start working in
another department at the same time it is finishing working for another one
,
so it will force us to enter something like:
INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
DateTimeEnded)
VALUES (33, 13, '06/24/2002', '2005-05-01T08:00:00')
INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
DateTimeEnded)
VALUES (33, 12, '2005-05-01T08:00:01', NULL)
and the select statement will not need the expression:
...
and j.datecreated != isnull(u.DateTimeEnded, dateadd(day, -1, j.datecreated)
)
...
AMB
"George Durzi" wrote:

> CORRECTION.
> I meant '06/24/2002', not '06/24/2005'
> INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
> DateTimeEnded) VALUES (33, 13, '06/24/2002', '05/01/2005')
> INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
> DateTimeEnded) VALUES (33, 12, '05/01/2005', NULL)
> In this example, User 33 worked in Department 13 from 06/24/2002 to
> 05/01/2005
> On 05/01/2005, they were transferred to Department 12
>
>
> "George Durzi" <gdurzi@.hotmail.com> wrote in message
> news:%23wnB$nIXFHA.3340@.TK2MSFTNGP15.phx.gbl...
>
>|||Thank you both for your help
"George Durzi" <gdurzi@.hotmail.com> wrote in message
news:%23wnB$nIXFHA.3340@.TK2MSFTNGP15.phx.gbl...
> In a sales software system that I built, a sales person belongs to a
> department within the company. However, during their employment with the
> company, they might work in several departments. So I implemented
> department logging in the system.
> CREATE TABLE [USER_DepartmentLog] (
> [UserId] [int] NOT NULL ,
> [DepartmentId] [int] NOT NULL ,
> [DateTimeStarted] [datetime] NOT NULL ,
> [DateTimeEnded] [datetime] NULL
> )
> GO
> INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
> DateTimeEnded) VALUES (33, 13, '06/24/2005', '05/01/2005')
> INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
> DateTimeEnded) VALUES (33, 12, '05/01/2005', NULL)
> In this example, User 33 worked in Department 13 from 06/24/2005 to
> 05/01/2005
> On 05/01/2005, they were transferred to Department 12
> Each sales person books Jobs:
> CREATE TABLE [Job] (
> [Id] [int] NOT NULL,
> [UserId] [int] NOT NULL,
> [DateCreated] [datetime] NOT NULL
> )
> GO
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(1, 33,
> '05/12/2005')
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(2, 33,
> '05/01/2005')
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(3, 33,
> '04/28/2005')
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(4, 33,
> '04/05/2005')
> I'd like to run a query that would return a list of jobs while at the same
> time displaying the department that the sales person was in at the time
> they entered the jobs. Here's what I would like my returned data to look
> like:
> UserId JobId DepartmentId DateCreated
> 33 4 13 04/05/2005
> 33 3 13 04/28/2005
> 33 2 12 05/01/2005
> 33 1 12 05/12/2005
>
> If a job was created on a date that the user transitioned from one
> department to another, e.g. Job 2 was created on 05/01/2005 when the user
> left Department 12 for Department 12, I'd like the job to appear under the
> new Department.
> Thank You
>