SQL Query Startdate to Enddate

Question

I have a table (eg. TableA) like this

| Name | Startdate | Enddate |
|----------------------------------------------|
| a | 2014-02-26 | 2014-02-28 |
| b | 2014-03-05 | 2014-03-06 |

If I want to below results. Please help how to do?

| Name | Date |
|---------------------------|
| a | 2014-02-26 |
| a | 2014-02-27 |
| a | 2014-02-28 |
| b | 2014-03-05 |
| b | 2014-03-06 |

Thank you.

Make clear your question.... I think u are talking about get result from a start date to end date from column StartDate and EndDate. Use SP to make increment in startdate and check for <=Enddate value — AHMAD SUMRAIZ, Mar 07 '14 at 10:35
The question doesn't really state what kind of query you are looking for. From what I can see, you want all the dates between start date and end date for each name. Is that correct? — Rob Aston, Mar 07 '14 at 10:49
...And do you have a Calendar Table? If yes, this is trivial. — Clockwork-Muse, Mar 07 '14 at 14:00
possible duplicate of [Get all dates in date range in SQL Server](http://stackoverflow.com/questions/9140308/get-all-dates-in-date-range-in-sql-server) — LittleBobbyTables - Au Revoir, Mar 07 '14 at 23:04

score 1 · Accepted Answer · answered Mar 07 '14 at 11:16

1

In SQL Server it can be achieved by using CTE as below

;WITH cte(name, Startdate,Enddate)
AS
(  
    SELECT name, Startdate,Enddate FROM your_table
    UNION ALL
    SELECT name, DATEADD(dd, 1,Startdate),Enddate FROM CTE 
    WHERE Startdate < Enddate
)
SELECT * FROM cte ORDER BY name

Here is the code at SQL Fiddle

answered Mar 07 '14 at 11:16

Dipendu Paul

2,685
1
23
20

1

Using recursive CTEs is not a good way to generate a series in SQL Server. Although on this scale it is unlikely to cause much of a problem, this would still be better using set based logic. See - [Generate a set or sequence without loops – part 2](http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2) – GarethD Mar 07 '14 at 11:22
@GarethD would we consider CTE as a loop? A loop would certainly hamper performance, but as far as my experience goes, CTEs fare very well in performance – Dipendu Paul Mar 07 '14 at 11:25
I would consider a recursive CTE a while loop, since it will continue to execute the recursive member while it returns results, although this is semantics. Read the article I posted, or at least scroll down to the performance comparison where it shows a recursive CTE to be over 15 times slower than the next slowest method. – GarethD Mar 07 '14 at 11:32
It is in my first comment. – GarethD Mar 07 '14 at 11:37

score 1 · Answer 2 · answered Mar 07 '14 at 11:25

In Oracle it's a little tricky:

SELECT distinct b.name, trim(regexp_substr(b.dates, '[^,]+', 1, LEVEL)) day
  FROM 
  (
    select a.name name,(select WM_CONCAT(a.startdate + rownum -1)  from all_objects  where rownum <=    a.enddate - a.startdate + 1) dates
    from TableA a
  ) b
CONNECT BY LEVEL <= length(b.dates) - length(REPLACE(b.dates, ',', ''))+1
order by 1,2;

The inner query generates days for each name as a comma-separated String. The outer simply splits the String in several rows.

....really? While I don't doubt that this works, it's not likely to be a performant option. — Clockwork-Muse, Mar 07 '14 at 13:59

score -1 · Answer 3 · edited Mar 07 '14 at 10:47

-1

Here is a easy way to do this

select name, startdate from test_table
UNION ALL 
select name, enddate from test_table
order by 2 desc

edited Mar 07 '14 at 10:47

GarethD

68,045
10
83
123

answered Mar 07 '14 at 10:45

Aniruddh Sawant

1
2

1

This just gets the start date and the end date, not the dates in between. e.g. for `a` in the sample data this would not include `2014-02-27` – GarethD Mar 07 '14 at 10:48

SQL Query Startdate to Enddate

3 Answers3