Select All dates between two dates

Question

I have a table that has only one record as below

Date1            Date2           Amount       CountDays
2014-01-01       2014-01-4       1000           4

field "CountDays" will be calculatd by a trigger. i want to write a query that returns below result:

Date             Amount
2014-01-01       250
2014-01-02       250
2014-01-03       250
2014-01-04       250

it is really neccessary for me please help me.

I don't think this is possible without a custom function as described here: http://stackoverflow.com/a/1378788/2360972 — Chief Wiggum, Nov 10 '14 at 10:34

Tim Schmelter · Accepted Answer · 2014-11-10T11:09:08.707

3

This should work:

WITH Nums AS(
    SELECT DISTINCT Value = number 
    FROM master..[spt_values] 
)
SELECT Date = DATEADD(d, n.Value - 1, t.Date1),
       Amount = t.Amount / t.CountDays * 1.0
FROM Nums n CROSS JOIN TableName t
WHERE n.Value BETWEEN 1 AND t.CountDays

Demo

Note that this apporach works only until 2,164. Another approach is to use a number-table. Read:

http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1

edited Nov 10 '14 at 11:09

answered Nov 10 '14 at 10:44

Tim Schmelter

450,073
74
686
939

Dear Tim , Thank you for yor help . 2164 row is enough for me. you saved me. – Behnam Nov 10 '14 at 11:07

score 0 · Answer 2 · answered Nov 10 '14 at 10:46

The best option would be to use a numbers (tally) table, but in case you don't have one or have the option set one up you can generate a sequence on the fly using a recursive common table expression like this:

-- test data
declare @t table (Date1 date, Date2 date, Amount int, CountDays int);
insert @t values ('2014-01-01','2014-01-04',1000,4);

-- query
;with cte (date1, date2, amount) as (
    select date1, date2, Amount * 1.0 / CountDays as amount
    from @t -- your table
    union all
    select DATEADD(day, 1, date1) date1, date2, amount
    from cte
    where date1 < date2
)

select date1, amount from cte

Result:

date1      amount
---------- -----------
2014-01-01 250
2014-01-02 250
2014-01-03 250
2014-01-04 250

score 0 · Answer 3 · answered Nov 10 '14 at 10:49

Please try the following :

DECLARE @FromDate DATETIME,    
@ToDate DATETIME,
@DateDiff   INT,
@Amount     INT,
@Total      INT

--SET @FromDate = '2014-01-01'
--SET @ToDate = '2014-01-20'
--SET @Amount = 1000

SELECT TOP(1) @FromDate = Date1 FROM TABLENAME
SELECT TOP(1) @ToDate = Date2 FROM TABLENAME
SELECT TOP(1) @Amount = Amount FROM TABLENAME

SET @DateDiff = DATEDIFF(DAY, @FromDate, @ToDate)+1
SET @Total = @Amount / @DateDiff


BEGIN

DECLARE @TOTALCount INT
SET @FromDate = DATEADD(DAY,-1,@FromDate)
Select  @TOTALCount= DATEDIFF(DD,@FromDate,@ToDate);


WITH d AS 
        (
          SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER() 
            OVER (ORDER BY object_id), REPLACE(@FromDate,'-',''))
          FROM sys.all_objects
        )
    SELECT AllDays, @Total AS [Amount] From d

RETURN 
END
GO

I have tested the script with hardcoded values to make sure it runs correct. Please just update the table names with the correct one.

Hope this helps.

score 0 · Answer 4 · answered Nov 10 '14 at 11:10

Creating test data:

create table yourtable (Id int, Date1 datetime, Date2 datetime, Amount int, CountDays decimal);

insert into yourtable values (1, '2014-01-01','2014-01-04', 1000, 4);
insert into yourtable values (2, '2014-01-13','2014-01-19', 1200, 6);

And the sql query (common table expression with recursion):

WITH cte (id, date1, date2, amount) AS (
    SELECT id, date1, date2, (Amount / CountDays) as amount
    FROM yourtable
    UNION ALL
    select id, DATEADD(day, 1, date1) date1, date2, amount
    from cte
    where date1 < date2
)

Finally to receive the data:

select * from cte
order by id

Output:

ID  DATE1                           DATE2                           AMOUNT
1   January, 01 2014 00:00:00+0000  January, 04 2014 00:00:00+0000  250
1   January, 02 2014 00:00:00+0000  January, 04 2014 00:00:00+0000  250
1   January, 03 2014 00:00:00+0000  January, 04 2014 00:00:00+0000  250
1   January, 04 2014 00:00:00+0000  January, 04 2014 00:00:00+0000  250
2   January, 13 2014 00:00:00+0000  January, 19 2014 00:00:00+0000  200
2   January, 14 2014 00:00:00+0000  January, 19 2014 00:00:00+0000  200
2   January, 15 2014 00:00:00+0000  January, 19 2014 00:00:00+0000  200
2   January, 16 2014 00:00:00+0000  January, 19 2014 00:00:00+0000  200
2   January, 17 2014 00:00:00+0000  January, 19 2014 00:00:00+0000  200
2   January, 18 2014 00:00:00+0000  January, 19 2014 00:00:00+0000  200
2   January, 19 2014 00:00:00+0000  January, 19 2014 00:00:00+0000  200

Fiddle Demo

Select All dates between two dates

4 Answers4