Help with a Query

9 Sep 2005 6:46 PM

george.durzi

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!

9 Sep 2005 6:55 PM

Barry

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

9 Sep 2005 8:01 PM

george.durzi

Thank you Barry, I'll check this out.

9 Sep 2005 7:24 PM

JP

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.du***@gmail.com> wrote in message
Show quote

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!
>

9 Sep 2005 8:12 PM

--CELKO--

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));

>> I want a list of all Clients, and the LastContacted Date, and who the user who LastContacted them was. <<

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.