Help with a Query

A simple sales activity tracking system. We collect information about
Clients, and sales Activity related to the clients.
Here's some simplified DDL and some insert statements
CREATE TABLE [Client] (
Id [int] NOT NULL ,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
Id
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
CREATE TABLE [Activity] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[ClientId] [int] NOT NULL ,
[UserIdEnteredBy] [int] NOT NULL ,
[DateofActivity] [datetime] NOT NULL ,
CONSTRAINT [PK_Activity] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Activity_Client] FOREIGN KEY
(
[ClientId]
) REFERENCES [Client] (
[Id]
)
) ON [PRIMARY]
END
GO
INSERT INTO Client (Id) VALUES (1)
INSERT INTO Client (Id) VALUES (2)
INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
VALUES (1, 1, '01/01/2005')
INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
VALUES (1, 2, '02/01/2005')
INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
VALUES (1, 2, '03/01/2005')
INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
VALUES (1, 1, '04/01/2005')
I want a list of all Clients, and the LastContacted Date, and who the
user who LastContacted them was.
Here's what I have so far:
SELECT
C.Id,
A.[UserIdEnteredBy] AS LastContactedBy,
A.[DateofActivity] AS LastContacted
FROM
Client C
LEFT OUTER JOIN Activity A ON C.Id = A.[ClientId]
JOIN (SELECT ClientId, Max(Id) AS MaxActivityId From Activity GROUP BY
ClientId) L
ON A.Id = L.MaxActivityId AND A.ClientId = L.ClientId
This gives me:
Id LastContactedBy LastContacted
---
1 1 2005-04-01 00:00:00.000
However, I want to return:
Id LastContactedBy LastContacted
---
1 1 2005-04-01 00:00:00.000
2 NULL NULL
This has got to be something with my joins, but I can't figure it out.
Thanks!Hi,
Try this instead...
Select Client.ID,
Activity.UserIDEnteredBy,
Activity.DateOfActivity

>From Client
Left Outer Join Activity on Client.ID = Activity.ClientID
Where Activity.DateOfActivity = (select Max(Activity2.dateofactivity)
from Activity Activity2
where Activity2.ClientID = Client.ID)
or Activity.UserIDEnteredBy IS Null
HTH
Barry|||Try
select c.*,tmp.* from client c left join (SELECT ClientId, Max(Id) AS
MaxActivityId From Activity GROUP BY
ClientId) tmp on c.id=tmp.clientid
Cheers,
JP
----
A program is a device used to convert,
data into error messages
----
<george.durzi@.gmail.com> wrote in message
news:1126291592.558676.297110@.g49g2000cwa.googlegroups.com...
>A simple sales activity tracking system. We collect information about
> Clients, and sales Activity related to the clients.
> Here's some simplified DDL and some insert statements
> CREATE TABLE [Client] (
> Id [int] NOT NULL ,
> CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
> (
> Id
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> END
> GO
> CREATE TABLE [Activity] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [ClientId] [int] NOT NULL ,
> [UserIdEnteredBy] [int] NOT NULL ,
> [DateofActivity] [datetime] NOT NULL ,
> CONSTRAINT [PK_Activity] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Activity_Client] FOREIGN KEY
> (
> [ClientId]
> ) REFERENCES [Client] (
> [Id]
> )
> ) ON [PRIMARY]
> END
> GO
> INSERT INTO Client (Id) VALUES (1)
> INSERT INTO Client (Id) VALUES (2)
> INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
> VALUES (1, 1, '01/01/2005')
> INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
> VALUES (1, 2, '02/01/2005')
> INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
> VALUES (1, 2, '03/01/2005')
> INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
> VALUES (1, 1, '04/01/2005')
> I want a list of all Clients, and the LastContacted Date, and who the
> user who LastContacted them was.
> Here's what I have so far:
> SELECT
> C.Id,
> A.[UserIdEnteredBy] AS LastContactedBy,
> A.[DateofActivity] AS LastContacted
> FROM
> Client C
> LEFT OUTER JOIN Activity A ON C.Id = A.[ClientId]
> JOIN (SELECT ClientId, Max(Id) AS MaxActivityId From Activity GROUP BY
> ClientId) L
> ON A.Id = L.MaxActivityId AND A.ClientId = L.ClientId
> This gives me:
> Id LastContactedBy LastContacted
> ---
> 1 1 2005-04-01 00:00:00.000
> However, I want to return:
> Id LastContactedBy LastContacted
> ---
> 1 1 2005-04-01 00:00:00.000
> 2 NULL NULL
> This has got to be something with my joins, but I can't figure it out.
> Thanks!
>|||Thank you Barry, I'll check this out.|||Why did the client's id change names from table to table? Why did you
avoid real keys in favor proprietary, non-relational record numbering?
Is this what you meant, if you had a proper data model and had followed
ISO-11179 namign conventions? .
CREATE TABLE Clients
(client_id INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE ClientActivity
(client_id INTEGER NOT NULL
REFERENCES Clients (client_id)
ON UPDATE CASCADE,
contact_user_id INTEGER NOT NULL,
REFERENCES Users (user_id)
ON UPDATE CASCADE,
contact_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (client_id, contact_user_id, activity_date));
SELECT client_id, contact_user_id, activity_date
FROM ClientActivity AS A1
WHERE A1.contact_date
= (SELECT MAX(A2.contact_date)
FROM ClientActivity AS A
WHERE A1.client_id = A2.client_id);
I am assuming that a client has to have had at least one contact. If
nothing else, to sign them as a client! There is no need for the
outer join or the Clients table.

Help w/ Data Structure

In a simple sales contact management software that I developed, users "own"
companies that are part of their portfolio. This is denoted by a row in the
UserCompany table. I use a join table because multiple users can own the
same company.
CREATE TABLE UserCompany
(
UserId int,
CompanyId int,
TargetCompany bit
)
The TargetCompany column is used to denote whether or not a certain company
is an important company in the specified user's portfolio. We run many
reports which only include a user's target companies.
I realized though, that if I run one of these reports for a date range that
is in the past, the data will always be based on the target companies
currently in UserCompany. i.e. Because of the data structure, I have no way
of telling if a user's company used to be a target company sometime in the
past.
Can someone suggest how I could modify this data structure to be able to
historically track when companies where denoted as target companies for a
certain user?
So, if Company A was denoted as a target company for User 1, between
01/01/2004, and 12/31/2004, but not later, how would I show that?
Thank YouHow about adding columns for StartOfInterestDate and EndOfInterest date,
denoting when the user had an interest in a particular company. You should
then be able to modify your query to include this within your criteria.
Cheers,
James Goodman
"George Durzi" <gdurzi@.hotmail.com> wrote in message
news:OyQAupkUFHA.580@.TK2MSFTNGP15.phx.gbl...
> In a simple sales contact management software that I developed, users
> "own" companies that are part of their portfolio. This is denoted by a row
> in the UserCompany table. I use a join table because multiple users can
> own the same company.
> CREATE TABLE UserCompany
> (
> UserId int,
> CompanyId int,
> TargetCompany bit
> )
> The TargetCompany column is used to denote whether or not a certain
> company is an important company in the specified user's portfolio. We run
> many reports which only include a user's target companies.
> I realized though, that if I run one of these reports for a date range
> that is in the past, the data will always be based on the target companies
> currently in UserCompany. i.e. Because of the data structure, I have no
> way of telling if a user's company used to be a target company sometime in
> the past.
> Can someone suggest how I could modify this data structure to be able to
> historically track when companies where denoted as target companies for a
> certain user?
> So, if Company A was denoted as a target company for User 1, between
> 01/01/2004, and 12/31/2004, but not later, how would I show that?
> Thank You
>|||Nice idea.
Let's say a company is a target company for 1 yr, then not a target company
for some time, and back to being a target company (phew)
Do you think this would be handled best with multiple rows for the same
User/Company?
"James Goodman" <jamesATnorton-associates.co.ukREMOVE> wrote in message
news:%23PhVr5kUFHA.3344@.TK2MSFTNGP10.phx.gbl...
> How about adding columns for StartOfInterestDate and EndOfInterest date,
> denoting when the user had an interest in a particular company. You should
> then be able to modify your query to include this within your criteria.
> --
> Cheers,
> James Goodman
> "George Durzi" <gdurzi@.hotmail.com> wrote in message
> news:OyQAupkUFHA.580@.TK2MSFTNGP15.phx.gbl...
>|||Yes, sounds like you need a separate log table.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"George Durzi" <gdurzi@.hotmail.com> wrote in message
news:uW46R7kUFHA.1148@.tk2msftngp13.phx.gbl...
> Nice idea.
> Let's say a company is a target company for 1 yr, then not a target
> company for some time, and back to being a target company (phew)
> Do you think this would be handled best with multiple rows for the same
> User/Company?
> "James Goodman" <jamesATnorton-associates.co.ukREMOVE> wrote in message
> news:%23PhVr5kUFHA.3344@.TK2MSFTNGP10.phx.gbl...
>|||Thank you both for your help.
George
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:enaeB$kUFHA.3176@.TK2MSFTNGP12.phx.gbl...
> Yes, sounds like you need a separate log table.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "George Durzi" <gdurzi@.hotmail.com> wrote in message
> news:uW46R7kUFHA.1148@.tk2msftngp13.phx.gbl...
>|||CREATE TABLE UserCompanyHistory
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON UPDATE CASCADE,
company_id INTEGER NOT NULL
REFERENCES Companies(company_id)
ON UPDATE CASCADE,
company_priority INTEGER NOT NULL
CHECK (company_priority > 0),
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
end_date DATETIME, -- null means current\
CHECK(start_date, end_date),
PRIMARY KEY (user_id, company_id, start_date)) ;
Do not use the BIT datatype; use a prioirty number instead. Time is in
durations, so add start and end times to the table. Now for some
views:
CREATE VIEW UserCompany (user_id, company_id, company_priority)
AS SELECT user_id, company_id, user_id, company_priority
FROM UserCompanyHistory
WHERE end_date IS NULL;
CREATE VIEW UserBestCompany (user_id, company_id)
AS SELECT user_id, company_id, user_id
FROM UserCompanyHistory AS H1
WHERE company_priority = 1;
or if you do not maintain a nice ordering in the priority column:
CREATE VIEW UserBestCompany (user_id, company_id)
AS SELECT user_id, company_id, user_id
FROM UserCompanyHistory AS H1 WHERE company_priority
= (SELECT MIN(company_priority)
FROM UserCompanyHistory AS H2
WHERE H1.user_id = H2.user_id _
You will need some triggers to maintain the history table integrity,
but they are not tricky

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
>