Post reply

Forcing the existence of date records for a graph of counts over time

  • October 14, 2014 at 2:01 pm

    #397504

    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

    #1752378

    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

    #1752601

    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