help changing 3 queries into 1

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 changing 3 queries into 1

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-22 : 05:09:37

Hi,

I have the following 3 queries that bring back desired results in 3 seperate result sets. I am looking to bring back all this data in just one result set. "dateAccessed,memberSince,dateSent" do not need to each be brought, a simple "date" column that represents all 3 is fine.

Any assistance on putting this one together is greatly appreciated. I don't think DDL or DML is helpful in this situation, but if you want it please let me know, and I'll happily put it together.

Basically what I want brought back is the following columns


date/ COUNT(A.mediaID) AS Accessed FROM tblMedia M /
 COUNT(UD.userID) AS Signups FROM tblUserDetails UD / 
COUNT(E.emailID) AS emails FROM tblemaillist_sendtofriend E

Thanks once again!!,
Mike123


CREATE PROCEDURE [dbo].[select_stats2]

(
 @numDays int
)
		 
AS SET NOCOUNT ON


SELECT CONVERT(varchar(10),A.dateAccessed,112) as dateAccessed, 
COUNT(A.mediaID) AS Accessed FROM tblMedia M 
INNER JOIN tblAccessLog A ON M.mediaID = A.mediaID
WHERE DateDiff(dd, A.dateAccessed, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),A.dateAccessed,112)
ORDER BY dateAccessed desc, accessed DESC



SELECT CONVERT(varchar(10),UD.memberSince,112) as memberSince, 
COUNT(UD.userID) AS Signups FROM tblUserDetails UD
WHERE DateDiff(dd, UD.memberSince, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),UD.memberSince,112)
ORDER BY memberSince DESC

 
SELECT CONVERT(varchar(10),E.dateSent,112) as dateSent, 
COUNT(E.emailID) AS emails FROM tblemaillist_sendtofriend E 
WHERE DateDiff(dd, E.dateSent, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),E.dateSent,112)
ORDER BY dateSent desc, emails DESC

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-05-22 : 05:31:38

Put a Union..
SELECT CONVERT(varchar(10),A.dateAccessed,112) as dateAccessed,
COUNT(A.mediaID) AS Accessed FROM tblMedia M
INNER JOIN tblAccessLog A ON M.mediaID = A.mediaID
WHERE DateDiff(dd, A.dateAccessed, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),A.dateAccessed,112)

Union

SELECT CONVERT(varchar(10),UD.memberSince,112) as memberSince,
COUNT(UD.userID) AS Signups FROM tblUserDetails UD
WHERE DateDiff(dd, UD.memberSince, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),UD.memberSince,112)
Union

SELECT CONVERT(varchar(10),E.dateSent,112) as dateSent,
COUNT(E.emailID) AS emails FROM tblemaillist_sendtofriend E
WHERE DateDiff(dd, E.dateSent, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),E.dateSent,112)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-22 : 06:02:06

If you dont worry about duplicates, use union all

Madhivanan

Failing to plan is Planning to fail

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-22 : 18:43:32

for readability reasons , rather than using a union I was hoping to have the data being brought back from all 3 queries visible on one row...

Basically there will be a "date" record for the past 10 days, and each query will have a corresponding column value for each date. Is it possible to do it like this ?

Thanks!
mike123

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-22 : 19:51:13

Here is a method to achomplish what you are trying to do. It maynot be the best way, but it will get you the results. Depending on how big your tables are and if you have any restrictions we might be able to get the performance up a bit, but untill then:

SELECT
	ID,
	MAX(dateAccessed) AS dateAccessed,
	MAX(memberSince) AS memberSince,
	MAX(dateSent) AS dateSent
FROM
	(
		SELECT 1 AS ID, GETDATE() AS dateAccessed, NULL AS memberSince, NULL AS dateSent
		UNION
		SELECT 1, NULL, GETDATE() - 30, NULL
		UNION
		SELECT 1, NULL, NULL, GETDATE() - 5
	) t
GROUP BY
	ID

Obviously, this is just an example of the concept. But, you should be able to just UNION your 3 queries using NULL or something and group by your key and take the MAX of the value to get the actual value. Hopefully, it makes sense.

-Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources