validating dPubDate through blackout dates

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)

validating dPubDate through blackout dates

Author

Topic

GrepZen
Starting Member

13 Posts

Posted - 2005-11-16 : 19:08:30

Given a date (dPubDate) I'd like a SQL statement that returns AdCampaign.CampaignIDs which are valid for that date.

Valid IF
If AdCampaigns.StartDate <= dPubDate AND AdCampaigns.EndDate>=dPubDate
AND
NOT ( any occurrance of WinningBids.PublicationDate (matching CampaignIDs) that ...
dPubDate >= WinningBids.PublicationDate -AdCampaigns.MDBP(days)
OR
dPubDate <= WinningBids.PublicationDate +AdCampaigns.MDBP(days) )

Some Schema:
CREATE TABLE [dbo].[AdCampaigns] (
[CampaignID] [int] IDENTITY (1, 1) NOT NULL ,
[CampaignName] [varchar] (40) NULL ,
[MDBP] [int] NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
CREATE TABLE [dbo].[WinningBids] (
[TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
[CampaignID] [int] NULL )
[PublicationDate] [datetime] NULL ,

» AdCampaigns
8, SomeCampaignName, 3, 11/15/2005 00:00:00.000, 11/30/2005 00:00:00.000
9, SomeCampaignName, 3, 11/15/2005 00:00:00.000, 11/30/2005 00:00:00.000

» WinningBIds
27, 8, 11/16/2005 00:00:00.000
28, 8, 11/26/2005 00:00:00.000
29, 8, 11/22/2005 00:00:00.000

desired results for example dates:
DATE, Recordset (CampaignIDs)
11/17/2005, 9
11/19/2005, 8,9
11/20/2005, 9
11/18/2005, 8,9
11/24/2005, 9
11/27/2005, 9
11/29/2005, 8,9

any help would be appreciated.

BTW: and if it helps, I'm writing this in classic .ASP on msSQL2000,IIS6 win2k3
...while treading water
......in a school of hungry sharks, baracuda, pirana and leaches
..........with a nasty paper cut on my toe
.............. and a 20 pound weight tied to foot

Cheers! and thanks for your time/consideration

:: ALSO
[] MDBP will always be a positive integer( 1-7 )
this may be a hint.. the only records in WinningBids that need to be checked are those who's PublicationDate fall ±7 days of dPubDate

Micke

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-17 : 03:29:54

not sure if this is what you want

select *
from #WinningBids wb inner join #AdCampaigns ac
on wb.CampaignID = ac.CampaignID
where @dPubDate < dateadd(day, MDBP * -1, PublicationDate)
or @dPubDate > dateadd(day, MDBP, PublicationDate)

[KH]

GrepZen
Starting Member

13 Posts

Posted - 2005-11-17 : 05:03:01

not quite, consider the following:


14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
-- -- 27 -- --                -- -- 29 -- --
                  -- -- 28 -- --

for dPubDates of (14-18, 20-28) only CampaignID(9) should be returned
for the 19th, 29th & 30th, both CampaignIDs(8,9) are 'valid'

But I definately agree with your direction of a subquery of sorts.

I can describe what I 'think' I want to do in SQL, I just can't manage a SQL statement that would make it happen.

#8729; Assume that all CampaignIDs for which dPubDate falls between Start/End dates = VALID
#8729; for any given dPubDate, pull all WinningBid.PublicationDate records that fall between dPubdate (±7days)
#8729; For each of those records, if dPubdate is between WinningBid.PublicationDate (±AdCampaigns.MDBP days) then that CampaignID is INVALID (for the remainder of this query)

What comes out is..
a list of CampaignIDs who
A) have never been placed within WinningBids
2) have placed in WinningBids but those dates are outside of 'MDBP days from the new dPubDate.

Many thanks for your help thus far.

Micke

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-17 : 05:26:19

sorry, misunderstood your requirement.

Hope this will work

select  *
from	AdCampaigns ac
where 	ac.StartDate	<= @dPubDate
and	ac.EndDate	>= @dPubDate
and	not exists (select * from WinningBids wb
			where	wb.CampaignID = ac.CampaignID
			and	dateadd(day, ac.MDBP * -1, PublicationDate) 	< @dPubDate
			and	dateadd(day, ac.MDBP, PublicationDate)		> @dPubDate)

[KH]

GrepZen
Starting Member

13 Posts

Posted - 2005-11-17 : 06:55:42

KH, that looks promising.

I'll RSVP when I've had a chance to test it. (hopefuly later this AM)

I appreciate your time/effort w/ this.

Micke

GrepZen
Starting Member

13 Posts

Posted - 2005-11-17 : 18:11:02

KH,
I'm trying that sql query (for dPubDate=11/26/2005) above, and it's returning CampaignID(9) which 'hit' on the 26th.

Micke

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources