Need Direction Please? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Need Direction Please?

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2002-12-10 : 19:57:56

Hi,

I'm a beginner at this and I'm trying to work out a simple banner rotation system where two different banners are selected from multiple choices, displayed and then both ads are updated on the impression count.

I have the following code that does what I want it to, however I was wondering if there is a more efficient way to do it.

Here is my query

DECLARE @ADID1 int,
@ADID2 int

SELECT TOP 1 @ADID1 = AdId
FROM IPG_ADVERTISE
WHERE DisplayWhere = 'Side'
ORDER BY NewID()

SELECT TOP 1 @ADID2 = AdId
FROM IPG_ADVERTISE
WHERE DisplayWhere = 'Side' And AdId <> @ADID1
ORDER By NewID()

UPDATE IPG_ADVERTISE
SET Impressions = Impressions + 1
WHERE AdID = @ADID1 OR AdID = @ADID2

PRINT @ADID1
PRINT @ADID2

Any help would be appreciated.

Thanks,

JB

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-10 : 20:35:31

CREATE TABLE #IPG_ADVERTISE
(
AdId INT,
Impressions INT,
DisplayWhere NVARCHAR(25)
)

INSERT INTO #IPG_ADVERTISE (AdId, Impressions, DisplayWhere)
VALUES (1,0,'top')
INSERT INTO #IPG_ADVERTISE (AdId, Impressions, DisplayWhere)
VALUES (2,1,'side')
INSERT INTO #IPG_ADVERTISE (AdId, Impressions, DisplayWhere)
VALUES (3,2,'side')
INSERT INTO #IPG_ADVERTISE (AdId, Impressions, DisplayWhere)
VALUES (4,0,'bottom')
INSERT INTO #IPG_ADVERTISE (AdId, Impressions, DisplayWhere)
VALUES (5,5,'side')

UPDATE #IPG_ADVERTISE
SET Impressions = Impressions + 1
FROM
(
SELECT TOP 2 AdId, NewID() As Col2
FROM #IPG_ADVERTISE
WHERE DisplayWhere = 'Side'
ORDER BY NEWID()
)B
WHERE
#IPG_ADVERTISE.AdId = B.AdId

DROP TABLE #IPG_ADVERTISE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources