• Replies 3 replies
  • Subscribers 338 subscribers
  • Views 12 views
  • Users 0 members are here
Related

Date Table

Stephen Lang
$organization

I was wondering if there already existed a table in the impresario db with every date of the year, instead of me creating one?  I'm building a sales tracker in SSRS and would love to join it to every date so as to avoid missing dates in my graph.  I would love to know if this exists somewhere.

Thanks,

Stephen

  • $organization

    Stephen,

    I don't believe that there is much holding dates, besides TR_BATCH_PERIOD, but you could do something like the below with a common table expression:

     

    DECLARE @start_dt DATE = '2014-01-01';

    WITH dates (day_dt)
         AS (SELECT 0
             UNION ALL
             SELECT day_dt + 1
             FROM   dates
             WHERE  day_dt < 365)
    SELECT Dateadd(dd, day_dt, @start_dt)
    FROM   dates
    WHERE  Dateadd(dd, day_dt, @start_dt) < Dateadd(yy, 1, @start_dt)
    OPTION (maxrecursion 0); 

  • $organization

    Paul's suggestion is great, however I created a static LT_CALENDAR table here at the Guthrie which I use frequently for all sorts of reporting purposes. I cribbed many ideas from this blog post:

    http://www.brianshowalter.com/calendar_tables

  • $organization in reply to Chris Jensen

    Thank you both for replying, I think I will go ahead and create a local table as I can see needing to use frequently.  This blog post is great.