Date Calculations Puzzle 2

[gbaughma]

Here's a silly question... but for some reason I'm having a problem wrapping my brain around it.

We have products. Those products can be featured. Products are featured for a date range.
For example, halloween products are featured from October 15 to November 7th.
Every year, regardless of the year.

My database has a boolean called "Featured", and I run a routine nightly that goes through the products, looks at the featured date range, and sets the bit for whether or not that product is featured.

I have this working, but not the way I would like. Here is the issue.
I can't just use a date range, because it's recurring... in other words, I'm ignoring the year on the range... so the date range may be 10/15/2010 to 11/7/2010. I only care about the month and date.

Where I run into a *real* problem is when the featured dates cross the new year boundary... in other words, a new years product may run from 12/15 through 1/15

The dates are in two fields, "FeatureStartDate" and "FeatureEndDate", which are datetime fields in my SQL table.

I need to figure out the math so that it looks at the start and end dates, and figures out if "today" is within those dates. I can't just do >= StartDate OR <= EndDate, because that doesn't work on the new years' scenario shown above.

It gets especially tricky since I'm working with just the month and day. If I replace the year on the start and end dates with "this" year, then my routine doesn't work if we're coming up on the new year.

So, to summarize (I know, I ramble... sorry... I do it so that people completely understand what I'm asking...)

I have a product with a start and an end date.
That start and end date may bridge a year change.
I need to evaluate that start and end date, and determine if TODAY's date is within that start and end date.
It needs to be not dependent on a year; just the month and day.
If the condition is true, set a bit for that product marking it as "Featured"

Any thoughts on this tricky little bit of math?

TIA!

--Greg


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[johnherman]

You could calculate the days between the start and end date, then calculate the days between todays date and either of the other two dates. If the days between from the today's date calculation is less than or equal to the days between the start and end dates, then you are within the window.

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[gmmastros]

I have a thought...

Instead of duration, I think it would be simpler to keep track of start date and duration.

For example, your Halloween start date would be 10/15, and the duration would be 23 days.

Here's a little mock up of my idea.

Declare @Temp Table(Reason VarChar(100), StartDate VarChar(20), Duration Int)

Insert into @Temp Values('Halloween', '10/15', 23)
INsert Into @Temp Values('New Years', '12/15', 31)

Declare @Year Int

-- Check the dates for year 2013
Set @Year = 2013

Select  *,
        Convert(DateTime, StartDate + '/' + Convert(VarChar(10), @Year), 101) As StartDate,
        DateAdd(Day, Duration, Convert(DateTime, StartDate + '/' + Convert(VarChar(10), @Year), 101)) As EndDate
From    @Temp


-- Check the dates for year 2014
Set @Year = 2014

Select  *,
        Convert(DateTime, StartDate + '/' + Convert(VarChar(10), @Year), 101) As StartDate,
        DateAdd(Day, Duration, Convert(DateTime, StartDate + '/' + Convert(VarChar(10), @Year), 101)) As EndDate
From    @Temp

Note that the start date in the table would be just month and day. I then convert the year from int to string and do the concatenation. I then convert to DateTime. Please note that the convert to datetime has a style parameter of 101. This is done to prevent potential problems with international date formats. I then take the date and add the duration to it.

Of course, this would all hinge on your ability to change the table structure and all of the surrounding code.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

Hmmm...
I think I see where you're going with that. However, duration it seems could be calculated. Let me think about this (out loud of course)

If my dates are 12/15/xxxx through 1/15/xxxx

Say my date today is 12/1/xxxx

Substitute this year... 12/15/2013 - 1/15/2013
Today's date, 12/1/2013 would be less than the start date. Not featured. Simple enough.
Now, if the date were 12/17/2013....
12/17/2013 > 12/15/2013 ... OK so far... but 12/17/13 !< 1/15/2013... doesn't work.
If we tested that the end date is less than today's date, and set that to the next year...
12/17/2013 > 12/15/2013 ... ok ... 12/17/2013 < 1/15/2014 ... OK.

If the date were 1/1/2014
Putting in this year...
1/1/2014 !> 12/15/2014 ... not ok 1/1/2014 < 1/15/2014 ... OK ... doesn't work.

What about just comparing month/days individually?
12/1 >= 12/15 (12 >= 12, 1 > 15) ... false... ok...
12/17 ... (12 >= 12) .. true ... (17 > 15) ... true...

Wait.. rambling stops... I think I just got it.
IF the START DATE < END DATE, then the END DATE gets CURRENT YEAR + 1
So 12/15 becomes 12/15/2013, and 1/15 becomes 1/15/2014 (Because the end date is less than the start date)... now my test works.

Now to put my pseudo-code into working SQL....

(Sorry for the ramble... but that's the way my brain works.. .I have to work the problem...)




Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

If you want to keep your start and end dates, it's still possible. I think it's a lot more complicated, but here goes...

Declare @Temp Table(Reason VarChar(100), StartDate DateTime, EndDate DateTime)

Insert into @Temp Values('Halloween', '20101015', '20101107')
Insert Into @Temp Values('New Years', '20101215', '20100115')
Insert Into @Temp Values('George'' Holiday', '20101001', '20100115')

Select  *,
        DateDiff(Year, StartDate, GetDate()),
        DateAdd(Year, DateDiff(Year, StartDate, GetDate()), StartDate), 
        DateAdd(Year, DateDiff(Year, StartDate, GetDate()),
                Case When StartDate < EndDate 
                     Then EndDate
                     Else DateAdd(Year, 1, EndDate)
                     End) As EndDate
From    @Temp
Where	GetDate() >= DateAdd(Year, DateDiff(Year, StartDate, GetDate()), StartDate)
        And GetDate() < DateAdd(Day, 1, DateAdd(Year, DateDiff(Year, StartDate, GetDate()),
              Case When StartDate < EndDate 
                   Then EndDate
                   Else DateAdd(Year, 1, EndDate)
                   End))

I would strongly encourage you to play around with this code. In particular, dummy up a holiday that starts today to make sure it is returned, and also dummy up a holiday that ends today to make sure that it is also returned.

I should mention that this code assumes that the years for your start date and end date are the same. If they are not the same, this code will not work properly. To make the code handle this situation would be significantly more complicated.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

Well, here is what I ended up with.... seems to work. It gives me a list of the SKUs in the date range....

SELECT SKU, StartDate, EndDate 
FROM AES_FeaturedItems 
WHERE (Replace(StartDate,year(StartDate),Year(GetDate())) <= GetDate()
 AND 
(CASE 
WHEN StartDate < EndDate THEN REPLACE(EndDate,YEAR(EndDate),YEAR(GetDate())) 
WHEN StartDate > EndDate THEN REPLACE(EndDate,YEAR(EndDate),YEAR(GetDate())+1)
END) >= GetDate())

Seems to work... I've tried it with some test dates...


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[TamarGranor]

If all the feature dates are recurring, then why not update the start and end when you're turning the bit off? That is, at the moment when you discover that the bit is set and no longer should be, add a year to both the start and end date.

Tamar

 

[DaveInIowa]

How about adding some computed columns and then querying directly off of those?

ALTER TABLE FeaturedProducts
ADD FeaturedAfter AS
CASE WHEN GETDATE() <= DATEADD(YEAR, DATEDIFF(YEAR, FeatureEndDate, GETDATE()), FeatureEndDate)
   THEN DATEADD(D, DATEDIFF(D, FeatureEndDate, FeatureStartDate), DATEADD(YEAR, DATEDIFF(YEAR, FeatureEndDate, GETDATE()), FeatureEndDate))
   ELSE DATEADD(D, DATEDIFF(D, FeatureEndDate, FeatureStartDate),  DATEADD(YEAR, DATEDIFF(YEAR, FeatureEndDate, GETDATE()) + 1, FeatureEndDate))
END

ALTER TABLE FeaturedProducts
ADD FeaturedUntil AS
CASE WHEN GETDATE() <= DATEADD(YEAR, DATEDIFF(YEAR, FeatureEndDate, GETDATE()), FeatureEndDate)
   THEN DATEADD(YEAR, DATEDIFF(YEAR, FeatureEndDate, GETDATE()), FeatureEndDate)
   ELSE DATEADD(YEAR, DATEDIFF(YEAR, FeatureEndDate, GETDATE()) + 1, FeatureEndDate)
END

ALTER TABLE FeaturedProducts
ADD IsCurrentlyFeatured As
CASE WHEN GETDATE() <= DATEADD(YEAR, DATEDIFF(YEAR, FeatureEndDate, GETDATE()), FeatureEndDate)
   THEN CASE WHEN GETDATE() >=  DATEADD(D, DATEDIFF(D, FeatureEndDate, FeatureStartDate), DATEADD(YEAR, DATEDIFF(YEAR, FeatureEndDate, GETDATE()), FeatureEndDate)) THEN 1 ELSE 0 END
   ELSE CASE WHEN GETDATE() >= DATEADD(D, DATEDIFF(D, FeatureEndDate, FeatureStartDate),  DATEADD(YEAR, DATEDIFF(YEAR, FeatureEndDate, GETDATE()) + 1, FeatureEndDate)) THEN 1 ELSE 0 END
END

 

[Auguy]

No sure about this, but I would think a Calendar table populated many years into the future night be be helpful. Joining this with your Start/Stop Month/Day or a Duration table shouldn't be too hard using the current date's year to assist in the join. Maybe one of the experts here could give you some ideas on how to make this work.

Auguy
Sylvania/Toledo Ohio