help with stats query - (JOIN)

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

Transact-SQL (2000)

help with stats query - (JOIN)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-05-20 : 20:43:16

Hi,

Im attempting to write a query that is going to give some details on whats happening in the database and its getting a bit complex for me.

I have 2 tables, I want to pass to my query 2 values (dateStart, dateEnd)

The tables are as follow

tblUserDetails
userID, nameOnline, etc

tblEmails
emailID, sourceEmailAddress, destinationEmailAddress, dateSent, emailSourceID, mediaID

create PROCEDURE dbo.select_Stats

(
@dateStart smalldatetime,
@dateEnd smalldatetime
)

AS SET NOCOUNT ON

SELECT count(userID) as signUpCount FROM tblUserDetails WHERE memberSince > @dateStart AND memberSince < @dateEnd AND active <> '9'

GO

I need to have the count of emails sent as well.

(WHERE tblEmails.dateSent > @dateStart AND tblEmails.dateSent < @dateEnd )
How can I join this info on to the query?

Thanks once again for any assitance

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-21 : 05:00:33

You haven't indicated what columns in the two tables can be used to JOIN.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2005-05-21 : 05:02:34

Mike, you haven't specified if you want the count of emails sent by these users or by all users. That will decide if you really need a join here. Also, do you need the output as two columns (CountOfUsers, EmailsSent), two rows or two recordsets? For columns, you could use a subquery for each column. To return two rows you could UNION the two queries. To return two recordsets you can simply have two sql statements in the proc and use rs.NextRecordset in your ASP code.

OS

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-21 : 05:03:14

Without doing a join, you could return a recordset, with a single row, and two column values:

SELECT (Your first querey here) As UserTotal, (Email total query here) As EmailTotal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources