Insert with calculation - 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

General SQL Server Forums

New to SQL Server Programming

Insert with calculation

Author

Topic

ricc
Starting Member

16 Posts

Posted - 2013-07-05 : 07:05:30

Hi,

We are introducing a new version of our site. For day 1 we want to redirect 25% of visitors to the new site and keep 75% of visitors on the old site. Day 2 will be 50/50 then day 3 75/50 to the new site.

We record all visitor sessions in a SQL 2008 database, with the following structure:

SessionGuid|DateAdded|SiteID

At the point the session is created in the table we want to assign a siteID (1=old, 2=new) based on the number of sessions for the day that will then be used to redirect the user to the relevant site.

Does anyone have any pointers as to the best way to assign the siteID proportionally (ie 25% of visits given siteID=2) to the number of current days visits at the point of insertion?
Many thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-05 : 08:09:29

Use a random number generator and insert 3 out of 4 into one, and 1 out of four into the other. Something like this:

CASE WHEN CAST( RAND()*100 AS int)%4 < 3 
	THEN -- insert into the 3/4 portion
	ELSE -- insert into the 1/4 portion.
END

ricc
Starting Member

16 Posts

Posted - 2013-07-05 : 11:11:47

Thanks but I want to try and find a way that does not use RAND.

I've got the following query:

; WITH dayStats AS 
(
SELECT COUNT(*) AS Visitors, SiteID
FROM dbo.PublicSession 
WHERE (DAY(DateAdded) = DAY(GETDATE())) AND (MONTH(DateAdded) = MONTH(GETDATE()) )AND(YEAR(DateAdded) = YEAR(GETDATE()))                          
GROUP BY SiteID),totals
AS 
(
SELECT SUM(Visitors)AS tot FROM dayStats
)

SELECT * FROM dayStats

and this produces the following result:
Visitors SiteID
----------- -----------
3 1
1 2

How would I add the percentage of visits for each site to the above query? Once I have this could I not use a CASE WHEN for the calculated percentage?

ricc
Starting Member

16 Posts

Posted - 2013-07-05 : 11:27:38

Answering more of my own question: what about this approach? any ideas/feedback would be great:


DECLARE @SiteID INT
SET @SiteID = 1
DECLARE @Per  DECIMAL
;WITH dayStats AS 
(
SELECT COUNT(*) AS Visitors, SiteID,
COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS percentage
FROM dbo.PublicSession 
WHERE (DAY(DateAdded) = DAY(GETDATE())) AND (MONTH(DateAdded) = MONTH(GETDATE()) )AND(YEAR(DateAdded) = YEAR(GETDATE()))                          
GROUP BY SiteID),totals
AS 
(
SELECT SUM(Visitors)AS tot FROM dayStats
)
SELECT  @Per = percentage FROM dayStats WHERE SiteID = 2

IF(@per <25)
BEGIN
	SET @SiteID = 2 
END

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-05 : 11:38:36

It is very resource intensive. If you are doing a lot of transactions, it's going to be a dog. You can make that date filter a tiny bit more efficient by doing it as follows:

WHERE DateAdded >= DATEADD(dd, DATEDIFF(dd,0,GETDATE()),0)
	AND DateAdded < DATEADD(dd, DATEDIFF(dd,0,GETDATE()),1)

Also, I didn't follow how the two tables - dayStats and PublicSession relate to each other, so I can't comment on whether the query is logically correct.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources