1

I have a table which contains date ranges. For example:

ID    StartDate   EndDate
---------------------------
2     1/1/2017    1/4/2017
3     1/2/2017    1/5/2017
4     2/1/2017    2/2/2017

I am trying to create the table below via SSIS (either with "Execute SQL Task" or any other method)

ID    Date
--------------
2     1/1/2017
2     1/2/2017
2     1/3/2017
2     1/4/2017
3     1/2/2017
3     1/3/2017
3     1/4/2017
3     1/5/2017
4     2/1/2017
4     2/1/2017

And then once I have that I will join the remaining columns to each row

Question: how do I create a SQL task/command that will create the above mentioned table, based on whatever data is in the first table?

Note: the StartDate and EndDate are populated by an external source. Therefore, the first table is not static.

Also, I have a date dimension table called DimDate, but I have no idea how that helps me

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SUMguy
  • 1,505
  • 4
  • 31
  • 61
  • 1
    So, what's the question? – squillman Mar 15 '17 at 19:31
  • 1
    Reading between the lines, your best approach IMHO is to create a persistent calendar table, and use this to generate the list of dates. Here is a good article: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ – smj Mar 15 '17 at 19:32
  • 1
    Possible duplicate of [SQL query to convert date ranges to per day records](http://stackoverflow.com/questions/5363003/sql-query-to-convert-date-ranges-to-per-day-records) – Tab Alleman Mar 15 '17 at 20:08
  • Possibly...but I have almost no idea what that post is saying... lol ... I am 12 – SUMguy Mar 15 '17 at 20:20

1 Answers1

0

Easy with a date dimension table! I'll let you pick the one you would like to use as there are many out there in the wild. An example: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

enter image description here

decompiled
  • 1,853
  • 3
  • 14
  • 19
  • I should have mentioned this, but I don't know what the actual dates are/will be... therefore I can't just enter '2/1/2017' because that may or may not be a thing – SUMguy Mar 15 '17 at 20:23
  • That's the beauty of the date dimension table as they store datetime series values and various other date related formats as you need. For example you can have integer values for day of year, week, month. Have a look at the link and hopefully the examples will help. – decompiled Mar 15 '17 at 20:52
  • 1
    @john5, All the code before the select statement is just there to reconstruct you're first table. You should focus on the select statement. – KindaTechy Mar 15 '17 at 23:15