June 3, 2008 at 2:02 pm
I'm creating queries that can forecast scheduled financial entries into the future. Each scheduled entry has one row in the table Schedule that describes how it is scheduled to occur. I've created a Calendar table to join with Schedule. I'm writing the SQL to forecast each interval type: daily, weekly, monthly, etc.
Here's some relevant partial SQL:
CREATE TABLE [dbo].[Schedule](
[entryId] [bigint] NOT NULL PRIMARY KEY,
[intervalId] [tinyint] NOT NULL,
[nextDate] [smalldatetime] NOT NULL,
[endDate] [smalldatetime] NULL)
CREATE TABLE [dbo].[Calendar](
[yearNumber] [smallint] NOT NULL,
[monthNumber] [tinyint] NOT NULL,
[monthDay] [tinyint] NOT NULL,
[weekdayNumber] [tinyint] NOT NULL,
[transactionDate] [smalldatetime] NOT NULL,
[isValidDate] [bit] NOT NULL DEFAULT ((1)),
[weekdayBefore] [smalldatetime] NOT NULL,
[weekdayAfter] [smalldatetime] NOT NULL,
PRIMARY KEY ([yearNumber], [monthNumber], [monthDay])
And, this is an example join for a weekly interval:
SELECT C.transactionDate, S.entryId
FROM Schedule S, Calendar C
WHERE S.intervalId = 3
AND C.transactionDate >= S.nextDate AND (S.endDate IS NULL OR C.transactionDate <= S.endDate)
AND C.weekdayNumber = DATEPART(dw, S.nextDate)
AND C.isValidDate = 1
I'm trying to write a similar join for a bi-weekly interval (every two weeks). However, I'm having trouble with this one. My first instinct was to use week number % 2 but that isn't accurate because week numbers reset to 1 on Jan 1. If the number of weeks in a year was always even and each weekday occurred exactly once per week, that wouldn't be a problem. But, that's not the case with week numbers.
Maybe I could do something with Row_Number? Or am I missing a date calculation method of doing this?
June 3, 2008 at 2:21 pm
Why not add a column for this to your Calendar table?
If you want to find the number of weeks from some base date (1 Jan 1900, for example), use datediff(day)/7. The number of fortnights (two-week periods) would be /14.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2008 at 4:25 pm
Excellent! This works:
SELECT C.transactionDate, S.entryId
FROM Schedule S, Calendar C
WHERE S.intervalId = 4
AND C.transactionDate >= S.nextDate AND (S.endDate IS NULL OR C.transactionDate <= S.endDate)
AND C.weekdayNumber = DATEPART(dw, S.nextDate)
AND C.isValidDate = 1
AND DATEDIFF(day, '1900-01-01', C.transactionDate) % 14 = DATEDIFF(day, '1900-01-01', S.nextDate) % 14
In truth, the where clause condition based on weekdayNumber is extraneous in this case, but since Calendar.weekdayNumber is indexed, I've left it in.
I could also add the DATEDIFF % 14 calculation as a column to Calendar, since it's a static table used only for forecasting.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply