October 14, 2014 at 2:01 pm
I'm pretty sure I know the answer to this, but I figured I would ask in case I'm missing something obvious.
I'm trying to get a count of Employed and Available contractors per time period, and I have a table of Contracts... something like:
CREATE TABLE empContract(
empContractID INT IDENTITY(10000,1) PRIMARY KEY,
StartDate DATE NOT NULL,
EndDate DATE,
ContractorAssigned INT,
FOREIGN KEY ContractorID REFERENCES Contractor(ContractorID)
);
Correct me if I'm wrong, but I don't think this is possible without the existence of some kind of Calendar table. Given the existence of a calendar table, the query seems really simple - just something like:
SELECT cal.CalendarDate, ec.ContractID
FROM Calendar cal LEFT JOIN empContract ec ON cal.CalendarDate BETWEEN ec.StartDate AND ec.EndDate
The left join forces the existence of all dates in a range (@StartDate and @EndDate), so that when I try to create a graph with counts by day, I don't have any gaps in my time series.
Is there any other way of doing this? I don't believe there is... I was just wondering if I were overlooking something.
Thanks!
Pieter
October 14, 2014 at 11:22 pm
Quick thought, this is in essence an unpacking of a period, either a calendar table or a tally table (virtual calendar table) will do the job. For the latter it is simply a number series equal to the date-diff of the given period. Once this is in place, the rest is easy.
😎
October 15, 2014 at 9:28 am
Thanks. That's what I thought, but wanted to make sure I wasn't missing something.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply