help adding one extra column on to SP

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 2005 Forums

Transact-SQL (2005)

help adding one extra column on to SP

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-01-27 : 10:22:07

Hi,

I have the following SPROC below, which is working perfectly, however I would like to add just 1 more column. The column would be called "totalMapViews_toCurrentTime"

This column would SELECT the same count as "totalMapViews" however it would add a WHERE clause that would limit by the current time.

For instance if it was run on Jan,25,2007 at 330am it would select the count of total records for todays date, and all previous dates the total would be until 330am only.

Can anyone help me figure out the best way to do this? Having problems with writing the query.

Thank you very much once again for any help!!
mike123

CREATE PROCEDURE dbo.select_stats_TotalMapViews_Creations_Ratio
(
@NumDays INT
)
AS

SET NOCOUNT ON

SELECT c.DateCreated AS DateCreated,TotalMapsCreated,TotalMapsCreated_UniqueIPS,TotalMapViews,TotalMapViews_UniqueIPS,
CASE
WHEN v.UniqueIPS = 0 THEN 0
ELSE 1.0 * v.UniqueIPS / c.UniqueIPS
END as MapView_Create_Ratio
FROM (
SELECT DATEADD(day, DATEDIFF(day, 0, dateCreated), 0) AS DateCreated,
COUNT(DISTINCT(CreatorIP)) AS UniqueIPS,

COUNT(MD.mapID) AS TotalMapsCreated, COUNT(distinct(MD.creatorIP)) AS TotalMapsCreated_UniqueIPS

FROM tblMapDetails MD
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(IP)) AS UniqueIPS,

COUNT(MVL.mapID) AS TotalMapViews, COUNT(DISTINCT(MVL.IP)) AS TotalMapViews_UniqueIPS

FROM tblMapViews_Log MVL
WHERE ViewDate >= 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

ORDER BY DateCreated desc

GO

CREATE TABLE [dbo].[tblMapViews_Log] (
[mapID] [int] NOT NULL ,
[IP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[viewDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblMapDetails] (
[mapID] [int] IDENTITY (1, 1) NOT NULL ,
[dateCreated] [datetime] NOT NULL ,
[lastUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-28 : 01:55:33

/*I would actually write the query creating a couple tmp tables to keep it neet rather then the way you have it, but to simply modify your query you could do what I illustrated below. Your query was a little difficult to decipher w/o knowing exactly what your db structure is, but you should be able to get the basic idea. I hope this helps*/

SELECT c.DateCreated AS DateCreated,TotalMapsCreated,TotalMapsCreated_UniqueIPS,TotalMapViews,TotalMapViews_UniqueIPS,
CASE
WHEN v.UniqueIPS = 0 THEN 0
ELSE 1.0 * v.UniqueIPS / c.UniqueIPS
END as MapView_Create_Ratio,
--***
Select COUNT(MVL.mapID) AS TotalMapViews
FROM tblMapViews_Log MVL
WHERE ViewDate <= getdate()
as totalMapViews_toCurrentTime
--****
FROM (
SELECT DATEADD(day, DATEDIFF(day, 0, dateCreated), 0) AS DateCreated,
COUNT(DISTINCT(CreatorIP)) AS UniqueIPS,

COUNT(MD.mapID) AS TotalMapsCreated, COUNT(distinct(MD.creatorIP)) AS TotalMapsCreated_UniqueIPS

FROM tblMapDetails MD
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(IP)) AS UniqueIPS,

COUNT(MVL.mapID) AS TotalMapViews, COUNT(DISTINCT(MVL.IP)) AS TotalMapViews_UniqueIPS

FROM tblMapViews_Log MVL
WHERE ViewDate >= 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

ORDER BY DateCreated desc

GO

CREATE TABLE [dbo].[tblMapViews_Log] (
[mapID] [int] NOT NULL ,
[IP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[viewDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblMapDetails] (
[mapID] [int] IDENTITY (1, 1) NOT NULL ,
[dateCreated] [datetime] NOT NULL ,
[lastUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources