Query help? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query help?

Author

Topic

jaroot
Starting Member

46 Posts

Posted - 2005-06-01 : 17:04:35

Table Structure
tMessage
ID/UserID/Message/Date

Sample Data:
1 - 100 - How are you bob? - 1/1/2005
2 - 200 - Good How are you Joe - 1/2/2005
3 - 100 - Fine, how are the kids - 1/3/2005
4 - 200 - Fine, how are yours? - 1/4/2005
...ect

I need to do the following calculations with this..

1. I need to find the date/user for all messages that are 3 days
old for each individual user. So in this case I would need a recordset
containing records (3 & 4)

2. I need to find the AVG time in between responses between users for any given user. In this case.. lets say I need to find the avg time it takes user 100 to respond to all of their messages
(this is a 1-1 way communication, so user 100 and 200 are the only users here). So here it would be simple with only 4 messages, and he responded the next day on each message, so he has a 1 day avg.

Any help here would be GREATLY appreciated!

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-06-01 : 19:05:39

1. How do you determine the start of a conversation in order to determine which messages are 3 days old?

2. If this system really only has the two users, then something like:
SELECT AVERAGE(t2.Date - t1.Date)
FROM tMessage t1
LEFT OUTER JOIN tMessage t2 on t2.ID = (t1.ID - 1)
GROUP BY UserID

If, in reality, there are more users, you will have to do some additional work to identify who is talking to whom.
---------------------------
EmeraldCityDomains.com

jaroot
Starting Member

46 Posts

Posted - 2005-06-02 : 09:21:55

quote:
Originally posted by AjarnMark

1. How do you determine the start of a conversation in order to determine which messages are 3 days old?

There's a "ProductID" in the table as well, as all messages pertain to a particular product that the two users are conversing about.

So to identify 3 day old "threads" (if you will)... I would just need to find the MAX(date) for each given product, and determine if that particular date is 3 days old. I don't need to really find ALL messages that are 3 days old, just the last message of any given "thread" that is 3 days old.

jaroot
Starting Member

46 Posts

Posted - 2005-06-02 : 09:30:37

quote:

2. If this system really only has the two users, then something like:
SELECT AVERAGE(t2.Date - t1.Date)
FROM tMessage t1
LEFT OUTER JOIN tMessage t2 on t2.ID = (t1.ID - 1)
GROUP BY UserID

SQL Server throws the following msg when trying to use AVG..
"The average aggregate operation cannot take a smalldatetime data type as an argument."

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-02 : 13:13:15

see if this works
select avg (datediff(d,t2.date,t1.date)) as YourAverage from

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
No Rows Returned

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources