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 required fro Reporting service webparts

Hi,
Since this is related to sharepoint reporting service web parts, I am
posting in both.
In place of report explorer I need to create my custom explorer. I have
figured out that report explorer implements ICellProvider.
I tried using it in my custom Report explorer, but unfortunately the URL
I am trying to pass is creating problems.
I can see the "report manager" in report viewer web part if my custom
explorer provides the cell data in following form:
"http://server/ReportS?/Users+Folders/domain+user/My
Reports/ChangeManagement/Matrix&rs:Command=Renderrc:Area=Report"
I would appreciate if any one can point out what data (and its format)
Report viewer web part expect as connection parameter.
Thanks
AmitAmit,
Are you using the MS webparts which just has the Viewer and the
explorer?
If so I have some third party web parts that do the parameters etc
which may be an easier solution to your problem.
Kind Regards
Dan Orchard
dan.orchard@.idow.co.uk
Amit Saxena wrote:
> Hi,
> Since this is related to sharepoint reporting service web parts, I am
> posting in both.
> In place of report explorer I need to create my custom explorer. I
> have figured out that report explorer implements ICellProvider. I
> tried using it in my custom Report explorer, but unfortunately the
> URL I am trying to pass is creating problems.
> I can see the "report manager" in report viewer web part if my custom
> explorer provides the cell data in following form:
> "http://server/ReportS?/Users+Folders/domain+user/My
> Reports/ChangeManagement/Matrix&rs:Command=Renderrc:Area=Report"
>
> I would appreciate if any one can point out what data (and its
> format) Report viewer web part expect as connection parameter.
>
> Thanks
> Amit|||Hi Dan,
I am using MS webparts. I will appreciate if you can tell me about other
webparts too.
Amit
Dan Orchard wrote:
> Amit,
> Are you using the MS webparts which just has the Viewer and the
> explorer?
> If so I have some third party web parts that do the parameters etc
> which may be an easier solution to your problem.
> Kind Regards
> Dan Orchard
> dan.orchard@.idow.co.uk
>
> Amit Saxena wrote:
>
>>Hi,
>>Since this is related to sharepoint reporting service web parts, I am
>>posting in both.
>>In place of report explorer I need to create my custom explorer. I
>>have figured out that report explorer implements ICellProvider. I
>>tried using it in my custom Report explorer, but unfortunately the
>>URL I am trying to pass is creating problems.
>>I can see the "report manager" in report viewer web part if my custom
>>explorer provides the cell data in following form:
>>"http://server/ReportS?/Users+Folders/domain+user/My
>>Reports/ChangeManagement/Matrix&rs:Command=Renderrc:Area=Report"
>>
>>I would appreciate if any one can point out what data (and its
>>format) Report viewer web part expect as connection parameter.
>>
>>Thanks
>>Amit
>