help with query (merging results of 2 queries)

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 query (merging results of 2 queries)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-01-19 : 08:37:12

Hi,

I have 2 queries that are working perfectly, but I want to combine them so that all the results are brought back together. This way I can add one more additional column that will simply be a division of 2 of the columns.

For instance a column I want to add is "UniqueRatio" This column would be (totalMapViews_Unique/TotalMapsCreated_Unique)

Is there an easy way to do this?

Thanks again for any help, very much appreciated!! :)
mike123

The first SPROC is

CREATE PROCEDURE dbo.select_stats_totalMapCreations

(
@numDays int
)

AS SET NOCOUNT ON

SELECT CONVERT(varchar(10),MD.dateCreated,112) as dateCreated,
COUNT(MD.mapID) AS TotalMapsCreated, COUNT(distinct(MD.creatorIP)) AS TotalMapsCreated_UniqueIPS FROM tblMapDetails MD
WHERE DateDiff(dd, MD.dateCreated, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),MD.dateCreated,112)
ORDER BY dateCreated DESC

GO

(Returns Data in the format)

dateCreated/TotalMapsCreated/TotalMapsCreated_Unique
20070118 31 19
20070117 42 29

The second SPROC is

CREATE PROCEDURE dbo.select_stats_totalMapViews

(
@numDays int
)

AS SET NOCOUNT ON

SELECT CONVERT(varchar(10),MVL.viewDate,112) as viewDate,
COUNT(MVL.mapID) AS TotalMapViews, COUNT(DISTINCT(MVL.IP)) AS TotalMapViews_UniqueIPS FROM tblmapviews_log MVL
WHERE DateDiff(dd, MVL.viewDate, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),MVL.viewDate,112)
ORDER BY viewDate DESC

GO

(Returns Data in the format)

dateCreated/TotalMapViews/TotalMapViews_Unique
20070118 1346 720
20070117 1159 561

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-19 : 08:40:13

Why don't you UNION two queries in single SP along with new column?

Or You can store output from both the SPs in temp tables and then use select query to fetch the required data from both tables.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 08:43:29

create table #peso1 (datecreated datetime, views int, unique int)

insert #peso1
exec dbo.select_stats_totalMapCreations 2

create table #peso2 (datecreated datetime, views int, unique int)

insert #peso2
exec dbo.select_stats_totalMapViews 2

select p1.datecreated, 1.0 * p1.unique / p2.unique
from #peso1 as p1
inner join #peso2 as p2 on p2.datecreated = p1.datecreated

make sure you check for zero division...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 08:51:03

[code]CREATE PROCEDURE dbo.uspGetUniqueData
(
@NumDays INT
)
AS

SET NOCOUNT ON

SELECT c.DateCreated AS theDate,
CASE
WHEN v.UniqueIPS = 0 THEN 0
ELSE 1.0 * c.UniqueIPS / v.UniqueIPS
END
FROM (
SELECT DATEADD(day, DATEDIFF(day, 0, DateCreated), 0) AS DateCreated,
COUNT(DISTINCT(CreatorIP)) AS UniqueIPS
FROM tblMapDetails
WHERE DateCreated >= DATEADD(day, -@NumDays, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
GROUP BY DATEADD(day, DATEDIFF(day, 0, DateCreated), 0)
) AS c
INNER JOIN (
SELECT DATEADD(day, DATEDIFF(day, 0, ViewDate), 0) AS ViewDate,
COUNT(DISTINCT(MapIP)) AS UniqueIPS
FROM tblMapViews_Log
WHERE DateCreated >= DATEADD(day, -@NumDays, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
GROUP BY DATEADD(day, DATEDIFF(day, 0, ViewDate), 0)
) AS v ON v.ViewDate = c.DateCreated[/code]

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-01-19 : 09:15:47

thanks very much once again Peter!! :)
worked perfectly

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources