0

My question is related to these 2 questions:

  1. https://stackoverflow.com/questions/17529860/how-to-list-all-dates-between-two-dates/27319120
  2. https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function

However in my case, I have a table with an ID,StartDate,EndDate columns and I want to apply the same approach (but instead of 2 days, I want to use the entire table).

And before dropping into functions, I wanted to see if this is possible w/ a select and a date dimension table.

Basically I want to scale this up to the entire table:

SELECT 
    t.Id
    ,dd.[Date]
FROM dbo.DateDimension dd
    ,dbo.T1 t   
WHERE
    t.Id = 957
    and  dd.Date>=t.StartDate and dd.Date <= t.EndDate 

enter image description here

I have Sample Data + Desired Output (The Select Statement) here:
www.sqlfiddle.com/#!18/0e389/2

1

There is probably something I don't understand in your question, but from the looks of it all you have to do is remove the predicate t.id = ? You can shorten the query a bit by using BETWEEN predicate, I also prefer explicit JOINS over "," joins:

SELECT t.Id, dd.[Date]
FROM dbo.DateDimension dd
JOIN dbo.dbse t   
    ON dd.Date BETWEEN t.StartDate and t.EndDate;

In your example in the fiddle, you output 3 id's. If you only want a subset you can use an IN predicate:

SELECT t.Id, dd.[Date]
FROM dbo.DateDimension dd
JOIN dbo.dbse t   
    ON dd.Date BETWEEN t.StartDate and t.EndDate;
WHERE t.id IN (4229, 4268, 4269);    
  • I thought I had tried that and got a different result, but sure enough, it works. That is the result I was after. – Jay Cummins Jun 7 at 20:33
1

I believe you are looking for the following, leveraging the TFV from you second reference:

SELECT 
    t.Id
    Exploded.*

FROM dbse t
  CROSS APPLY ExplodeDates(t.StartDate, t.EndDate) AS Exploded

For each row in t, the TVF will be called and will produce a row for each record in the TVF results.

  • I tried this one but it ran way too long (I have way more records than my example)! Although I understand what it does (it's the lateral join from PostgreSQL) – Jay Cummins Jun 7 at 20:45
  • Does this need to be a single statement? Or can you make this multiple statements? If the latter, I have an alternative that should be more performant. – Graham Jul 26 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.