validating 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 through blackout dates

Author

Topic

GrepZen
Starting Member

13 Posts

Posted - 2005-11-14 : 19:14:41

Q :: Request help writing a SQL.Query that validates a given date against 'black out' dates which consist of a date(PublicationDate) and anoffset(MDBP). Such that PublicationDate of Nov 26, Offset of 2 means that no dates between nov24 and 28 are permitted (PubDate ±MDBP)



CREATE TABLE [WinningBids] (
	[TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
	[FinalPrice] [int] NULL ,
	[TransactionDate] [datetime] NULL ,
	[PublicationDate] [datetime] NULL ,
	[AdNumber] [varchar] (20) NULL ,
	[CampaignID] [int] NULL )
CREATE TABLE [DatesRequested] (
	[ReqID] [Int] IDENTITY(1,1) NOT NULL,
	[ReqDate] [DATETIME] NOT NULL )

:: SomeData
 WinningBids
01,180,11/10/2005,11/16/2005,2,A0034,28,7
02,200,11/20/2005,11/26/2005,2,00022,45,8
03,210,11/20/2005,11/22/2005,2,AA004,48,8


:: graphicaly ; three campaigns have 'hit on 16,22,26
I want to if date(n) is valid against this data.

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





Expected Results ::
of the following example target dates, i'd like the query return something like the following.
 
 Nov 17 = 0 rec returned
 Nov 19 = return ReqID if exist
 Nov 20,24,28 = 0 rec returned
 Nov 29 = return ReqID if exist

:: NOTES
MDBP won't always be two.

any insight or helpful reading pointers would be graciously appreciated.

V/R -- Micke

Micke

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-14 : 21:46:27

Will need more info

Q : what is the relationship between WinningBids and DatesRequested ?

Q : your WinningBids sample data shows 8 columns whereas your WinningBids table only have 6 columns

[KH]

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-11-14 : 23:07:13

Have you looked at the dateadd function Micke? This should give you what you need, when used in combination with BETWEEN in the where clause and an aggregate in the select.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

GrepZen
Starting Member

13 Posts

Posted - 2005-11-15 : 06:34:27

Derrick,
Yes, I've tried some with BETWEEN. But I'm not quite clever enough to 'layer' them ---which is how I'm envisioning it (I could be all wrong)

Khtan, yes, sry, i droped some fields in the table that were not germane to the problem. -- but not from the data table - sry.

WinningBids is a log really. Dates and offsets defined above.
Datesrequested is a desired date that we're trying to find availabilty for.

if for example, datesrequested contained record #44 for the 29, it would be returned, whereas rec#46 for the 25th would not.

hope this helps.

Micke

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources