Help with complex summary of data please

Hi, I'm fairly new to RS and am looking for pointers to help me with
the following situation.
I have a system that tracks the activity of our warehouse and drivers.
Basically, it logs when an order is entered and when it leaves the
warehouse. There are various types of orders and seven warehouses. I
have a view which gives me all the detail I need including the
TimeToSend of each order (the difference between order time and send
time) all in one record per order. I have to create a report that
summarises all of this data on a daily basis by providing the
following:
At the entire company level and then broken down by each warehouse for
each day:
- #orders of TypeA where TimeToSend < X minutes with a % of Total
orders
- #orders of TypeA where TimeToSend > X and < Y minutes with a % of
Total orders
- #orders of TypeA where TimeToSend >Y minutes with a % of Total
orders
- AVG TimeToSend for the day for TypeA orders
- #orders of TypeB where TimeToSend < X minutes with a % of Total
orders
- #orders of TypeB where TimeToSend > X and < Y minutes with a % of
Total orders
- #orders of TypeB where TimeToSend >Y minutes with a % of Total
orders
- AVG TimeToSend for the day for TypeB orders
I have achieved the above using two datasets and two tables in my
report aligned next to each other and sorted so the warehouses appear
next to each other.
I have been asked to add additional summary data for each warehouse.
Such as:
- Count of orders of TypeC
- Best AVG TimeToSend of all days (i.e. best day ever)
Now, I can get these new data items from more datasets and add more
tables to the report but am I going about this the right way. Ideally,
having more than one dataset per table would be great. Or if I could
link the tables somehow.
Should I look at trying to return all the data in one dataset? Is that
even possible? My sql is fairly good but I don't know how to combine
the disparate data together.
Any suggestions would be appreciated.Hi,
I am not sure whether I have understood you fully, however, I think you may
integrate the data in another new Table first or a temp table?
If you want to use multiple datasets, it is not a easy job as Data Regions
(Table, Matrix, Chart, and List) are bound to a single data set. You can
refer to other data sets but the reference must be wrapped in an aggregate
such as First(Fields!FieldName.Value, "DataSetX").
Your options are to write a single query, using SQL Server's OpenRowset
functionality, that joins the multiple data sources into a single data set.
The other choice is to use multiple side by side tables though this might
introduce formatting issues.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Help with an error message

I keep getting this error message when I try to restore a database backup that I have made.

System.Data.SqlClient.SqlError: Directory lookup for the file "D:\Databases\GTT_Data.MDF" failed with the operating system error 5(error not found). (Microsoft.SqlServer.Express.Smo)

What I have done is created a backup of my sql sever database and I am now trying to restore it into sql server express for someone to play around with without it affecting my actual database. This is where the error messages are coming from.

Please help

Are you getting this during the restore ? If the machines are not the same for backup and restore you might not have the drives / folders available as they are on the original system. If so you either have to create those folders or Restore the database with the WITH MOVE option to specify a new destination. (thats because the original location is tored in the backup file).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Yes its during the restore. Yes the machines are not the same. Please could you give me further details on how to restore the databse with the WITH MOVE option.

Thanks for your help

|||Hi,

look in your BOL (Books online, the Online help of SQL Server)

There is a point Restore a database and move Files which will help you

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/877ecd57-3f2e-4237-890a-08f16e944ef1.htm

RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH NORECOVERY,
MOVE 'MyAdvWorks' TO
'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf',
MOVE 'MyAdvWorksLog1'
TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf'
RESTORE LOG MyAdvWorks
FROM MyAdvWorksLog1
WITH RECOVERY

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Help with a Query

Here is some drastically stripped down DDL for a Help Desk system I
wrote. I only left the relevant columns, and didn't script any of the
relationships, etc.
CREATE TABLE [HelpDesk_Issue] ([Id] [int])
GO
INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
GO
CREATE TABLE [HelpDesk_IssueHistory] (
[Id] [int],
[IssueId] [int],
[UserIdEnteredBy] [int],
[DateEntered] [datetime]
GO
INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
[DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
GO
INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
[DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
GO
INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
[DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
GO
HelpDesk_Issue is a table containing Help Desk issue entries, and
HelpDesk_IssueHistory is a table containing modification history
records for the Help Desk issues.
I want to write a query to retrieve values for LastUpdated, and
LastUpdatedBy.
LastUpdated is pretty easy. I might simply be brainfarting on not
knowing how to do a HAVING properly, but the only way I can retrieve
LastUpdatedBy is:
SELECT
LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
Where IssueId = I.[Id]),
LastUpdatedBy =
(
Select
UserIdEnteredBy
From
HelpDesk_IssueHistory
Where
Id =
(
Select
MAX(H.Id)
From
HelpDesk_IssueHistory H
Where
IssueId = I.[Id]
)
)
FROM
HelpDesk_Issue I
My result set should be:
Date Entered UserIdEnteredBy
2004-11-05 15:25:18.120 3
This query works, but is unacceptably slow, and there's got to be a
cleaner way of doing it.
Thank you!Hi
Maybe something like:
SELECT I.id, I.DateEntered, I.UserIdEnteredBy AS LastUpdatedBy
FROM HelpDesk_Issue I
JOIN (Select Id, MAX(DateEntered) AS LatestDateEntered From
HelpDesk_IssueHistory GROUP BY Id ) L ON I.id = L.id and I.DateEntered =
L.LatestDateEntered
Assuming that DateEntered is unique!
John
<george.durzi@.gmail.com> wrote in message
news:1122230790.079548.152910@.g44g2000cwa.googlegroups.com...
> Here is some drastically stripped down DDL for a Help Desk system I
> wrote. I only left the relevant columns, and didn't script any of the
> relationships, etc.
> CREATE TABLE [HelpDesk_Issue] ([Id] [int])
> GO
> INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
> GO
> CREATE TABLE [HelpDesk_IssueHistory] (
> [Id] [int],
> [IssueId] [int],
> [UserIdEnteredBy] [int],
> [DateEntered] [datetime]
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
> GO
> HelpDesk_Issue is a table containing Help Desk issue entries, and
> HelpDesk_IssueHistory is a table containing modification history
> records for the Help Desk issues.
> I want to write a query to retrieve values for LastUpdated, and
> LastUpdatedBy.
> LastUpdated is pretty easy. I might simply be brainfarting on not
> knowing how to do a HAVING properly, but the only way I can retrieve
> LastUpdatedBy is:
> SELECT
> LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
> Where IssueId = I.[Id]),
> LastUpdatedBy =
> (
> Select
> UserIdEnteredBy
> From
> HelpDesk_IssueHistory
> Where
> Id =
> (
> Select
> MAX(H.Id)
> From
> HelpDesk_IssueHistory H
> Where
> IssueId = I.[Id]
> )
> )
> FROM
> HelpDesk_Issue I
> My result set should be:
> Date Entered UserIdEnteredBy
> 2004-11-05 15:25:18.120 3
> This query works, but is unacceptably slow, and there's got to be a
> cleaner way of doing it.
> Thank you!
>|||george.durzi@.gmail.com wrote:
> Here is some drastically stripped down DDL for a Help Desk system I
> wrote. I only left the relevant columns, and didn't script any of the
> relationships, etc.
> CREATE TABLE [HelpDesk_Issue] ([Id] [int])
> GO
> INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
> GO
> CREATE TABLE [HelpDesk_IssueHistory] (
> [Id] [int],
> [IssueId] [int],
> [UserIdEnteredBy] [int],
> [DateEntered] [datetime]
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
> GO
> HelpDesk_Issue is a table containing Help Desk issue entries, and
> HelpDesk_IssueHistory is a table containing modification history
> records for the Help Desk issues.
> I want to write a query to retrieve values for LastUpdated, and
> LastUpdatedBy.
> LastUpdated is pretty easy. I might simply be brainfarting on not
> knowing how to do a HAVING properly, but the only way I can retrieve
> LastUpdatedBy is:
> SELECT
> LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
> Where IssueId = I.[Id]),
> LastUpdatedBy =
> (
> Select
> UserIdEnteredBy
> From
> HelpDesk_IssueHistory
> Where
> Id =
> (
> Select
> MAX(H.Id)
> From
> HelpDesk_IssueHistory H
> Where
> IssueId = I.[Id]
> )
> )
> FROM
> HelpDesk_Issue I
> My result set should be:
> Date Entered UserIdEnteredBy
> 2004-11-05 15:25:18.120 3
> This query works, but is unacceptably slow, and there's got to be a
> cleaner way of doing it.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Hmm..., Your query seems to be "saying" get the row w/ the latest date
and then get the user ID associated w/ the highest IssueHistory ID
number, which doesn't make much sense. From your limited DDL the
HelpDesk_IssueHistory ID column seems to be unnecessary (how is it an
attribute of the entity HelpDesk_IssueHistory?); therefore, that's why
your query doesn't make much sense to me.
If you just want to find the users who entered the last history item on
each issue try:
SELECT DateEntered, UserIDEnteredBy
FROM HelpDesk_IssueHistory As H
WHERE DateEntered = (SELECT MAX(DateEntered)
FROM HelpDesk_IssueHistory
WHERE IssueID = H.IssueID)
If you wanted the last entry of a specific issue use the above query as
the SQL statement in a stored procedure w/ a parameter of @.issue_id INT
and change the subquery's select clause to:
WHERE IssueID = @.issue_id
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQuPwQIechKqOuFEgEQKoIgCgyRRhsqCibrj+
zwfoQQYrlPTLkWcAoJmJ
50uIn26qiIk4AFnDVinfq+CN
=OEk/
--END PGP SIGNATURE--|||Thank you both for taking the time to reply on a Sunday.
"MGFoster" wrote:

> george.durzi@.gmail.com wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Hmm..., Your query seems to be "saying" get the row w/ the latest date
> and then get the user ID associated w/ the highest IssueHistory ID
> number, which doesn't make much sense. From your limited DDL the
> HelpDesk_IssueHistory ID column seems to be unnecessary (how is it an
> attribute of the entity HelpDesk_IssueHistory?); therefore, that's why
> your query doesn't make much sense to me.
> If you just want to find the users who entered the last history item on
> each issue try:
> SELECT DateEntered, UserIDEnteredBy
> FROM HelpDesk_IssueHistory As H
> WHERE DateEntered = (SELECT MAX(DateEntered)
> FROM HelpDesk_IssueHistory
> WHERE IssueID = H.IssueID)
> If you wanted the last entry of a specific issue use the above query as
> the SQL statement in a stored procedure w/ a parameter of @.issue_id INT
> and change the subquery's select clause to:
> WHERE IssueID = @.issue_id
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQuPwQIechKqOuFEgEQKoIgCgyRRhsqCibrj+
zwfoQQYrlPTLkWcAoJmJ
> 50uIn26qiIk4AFnDVinfq+CN
> =OEk/
> --END PGP SIGNATURE--
>|||Hey guys, sorry, still having a little trouble with this.
How would you tackle this if you couldn't guarantee that DateEntered was
unique. That's why I included the Id column in HelpDesk_IssueHistory. It's a
n
identity column, I forgot to note that on my DDL.
The query I wrote fetches the id of the latest history record, then uses
that to fetch the User who entered the records. However, it's unacceptably
slow.
Thank you
"george.durzi@.gmail.com" wrote:

> Here is some drastically stripped down DDL for a Help Desk system I
> wrote. I only left the relevant columns, and didn't script any of the
> relationships, etc.
> CREATE TABLE [HelpDesk_Issue] ([Id] [int])
> GO
> INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
> GO
> CREATE TABLE [HelpDesk_IssueHistory] (
> [Id] [int],
> [IssueId] [int],
> [UserIdEnteredBy] [int],
> [DateEntered] [datetime]
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
> GO
> HelpDesk_Issue is a table containing Help Desk issue entries, and
> HelpDesk_IssueHistory is a table containing modification history
> records for the Help Desk issues.
> I want to write a query to retrieve values for LastUpdated, and
> LastUpdatedBy.
> LastUpdated is pretty easy. I might simply be brainfarting on not
> knowing how to do a HAVING properly, but the only way I can retrieve
> LastUpdatedBy is:
> SELECT
> LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
> Where IssueId = I.[Id]),
> LastUpdatedBy =
> (
> Select
> UserIdEnteredBy
> From
> HelpDesk_IssueHistory
> Where
> Id =
> (
> Select
> MAX(H.Id)
> From
> HelpDesk_IssueHistory H
> Where
> IssueId = I.[Id]
> )
> )
> FROM
> HelpDesk_Issue I
> My result set should be:
> Date Entered UserIdEnteredBy
> 2004-11-05 15:25:18.120 3
> This query works, but is unacceptably slow, and there's got to be a
> cleaner way of doing it.
> Thank you!
>|||The reason DateEntered isn't unique is that even though I am inserting two
history records right after each other in separate db calls, I'm still
getting consecutive history records with the same datetime value.
I'm using GETDATE() within the insert sp. This isn't happening all the time,
only on about 40 of my 8000 records, but thus causing the queries you
recommended to break.
Perhaps I can handle for the server being too fast, by not using getdate,
and instead handling it on the presentation layer, and adding a time tick to
the next insert, in order to guarantee uniqueness
"George Durzi" wrote:
> Hey guys, sorry, still having a little trouble with this.
> How would you tackle this if you couldn't guarantee that DateEntered was
> unique. That's why I included the Id column in HelpDesk_IssueHistory. It's
an
> identity column, I forgot to note that on my DDL.
> The query I wrote fetches the id of the latest history record, then uses
> that to fetch the User who entered the records. However, it's unacceptably
> slow.
> Thank you
> "george.durzi@.gmail.com" wrote:
>|||Hi
Datatime is accurate one three-hundredth of a second, therefore it is
possible to get duplicates under a heavy load, although your identity
will be unique and you can (probably) use that instead and ignore the
datetime column.
e.g.
SELECT I.IssueId, I.DateEntered AS LastUpdatedBy, I.UserIdEnteredBy AS
LastUpdatedBy
FROM HelpDesk_Issue I
JOIN (Select IssueId, MAX(Id) AS LatestId From
HelpDesk_IssueHistory GROUP BY IssueId ) L ON I.IssueId = L.IssueId and
I.Id =
L.LatestId
OR
SELECT H.IssueId, H.DateEntered, H.UserIDEnteredBy
FROM HelpDesk_IssueHistory As H
WHERE H.Id = (SELECT MAX(Id)
FROM HelpDesk_IssueHistory S
WHERE S.IssueID = H.IssueID)
John|||Thanks again John, works perfectly
"John Bell" wrote:

> Hi
> Datatime is accurate one three-hundredth of a second, therefore it is
> possible to get duplicates under a heavy load, although your identity
> will be unique and you can (probably) use that instead and ignore the
> datetime column.
> e.g.
> SELECT I.IssueId, I.DateEntered AS LastUpdatedBy, I.UserIdEnteredBy AS
> LastUpdatedBy
> FROM HelpDesk_Issue I
> JOIN (Select IssueId, MAX(Id) AS LatestId From
> HelpDesk_IssueHistory GROUP BY IssueId ) L ON I.IssueId = L.IssueId and
> I.Id =
> L.LatestId
> OR
> SELECT H.IssueId, H.DateEntered, H.UserIDEnteredBy
> FROM HelpDesk_IssueHistory As H
> WHERE H.Id = (SELECT MAX(Id)
> FROM HelpDesk_IssueHistory S
> WHERE S.IssueID = H.IssueID)
> John
>

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 with a max length table constraint

Table File_Paths (physical system file paths)

The columns represent parts of the path.

How can I set up a constraint that the total concatenated length of all the columns within a row is less than 260 chars?

Thanks

ALTER TABLE SomeTable WITH NOCHECK
ADD CONSTRAINT SomeCheck CHECK (LEN(Col1+ co2 + col 3) <= 260)

HTH, Jens Suessmeyer.

|||

Right on target. Thanks

For some reason, when it gets saved a bunch of unnecessary parentheses both square and round get added.

The square brackets help if there are white spaces but the program just forces them. Likewise with the round the len() function did not need the items enclosed individually either.

Is there wa way to turn that off in SSMS?

|||The database engine modifies expressions specified in constraints, defaults, computed column etc. There is no way to control this behavior or suppress it. This is even more so in SQL Server 2005. So you should not rely on the scripting for your DDL. Instead it is better to maintain the scripts yourself in source code control system.|||

Thanks.

A newbie needs hand holding and the GUI does that. I am sure eventually I will get more independent but until then...

Umachandar Jayachandran - MS wrote:

you should not rely on the scripting for your DDL. Instead it is better to maintain the scripts yourself in source code control system.

Let me get this straight:

After I execute the script, the only other time I need it is if I want to modify it or reuse it on another server. Otherwise, it is for informational purposes. For the info to be effective, the DDL code would have to be broken down into smaller files for granularity and a huge effort to duplicate the tree-like organization. No automation at all. And all this while the same already exists, but the code is munged.

What is it with MS and code munging by force? Didn't they get enough complaints about mutilating html/aspnet markup in VS1.x? That was a sheer nightmare!

What good does it do to add over 40 unnecessary bracket chars in just one line of code?

</rant>

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
>

Help retreving Value form sql reader

hello,

I have tried myReader.GetSqlString, GetSqlValue, GetSqlInt16, etc...etc...

But I keep getting an error (System.InvalidCastException was caught
Message="Conversion from type 'SqlInt32' to type 'String' is not valid."
Source="Microsoft.VisualBasic"
StackTrace:
at Microsoft.VisualBasic.CompilerServices.Conversions.ToString(Object Value)
at ImportDelimitedFile.SumCurrentAmount() in C:\Documents and Settings\emg3703\My Documents\Visual Studio 2005\EscuelasComunidad\ImportDelimitedFile.aspx.vb:line 556)

Here is my code:

Public Function SumCurrentAmount()As String

Dim sqlconnAs New SqlConnection(ConfigurationManager.ConnectionStrings("GDBRemitanceConnectionString1").ConnectionString)
Dim sqlcmdAs New SqlCommand("SELECT SUM(CONVERT (Int, Field_6)) AS TotalAmount, Record_Type FROM tblTempWorkingStorage_NACHA GROUP BY Record_Type HAVING (Record_Type ='6')", sqlconn)

Try

sqlcmd.Connection.Open()
Dim myReaderAs SqlDataReader
myReader = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
If myReader.Read()Then

SumCurrentAmount =CType(myReader.GetSqlValue(0),String)
Return SumCurrentAmount

Else

End If
myReader.Close()

Catch

End Try
sqlcmd.Connection.Close()

End Function

I need to know which "GetSql...(type) should I used to extract field numbre one from myReader.

Thanks a lot,

Try to change this line:

SumCurrentAmount =CType(myReader.GetSqlValue(0),String)

to:

SumCurrentAmount = (myReader.GetDecimal(0)).ToString()

For more information about mapping SqlDbTypes to CLR data types, you can refer to:

http://msdn2.microsoft.com/en-us/library/system.data.sqldbtype.aspx