sql - Convert Date Range to Individual Days

Question

A table called VolumeRequest stores the volume requests by accounts for a date range.

AccountId   StartDate                       EndDate                   DailyVolume
670         2013-07-01 00:00:00.000         2013-07-31 00:00:00.000   10
670         2013-07-01 00:00:00.000         2013-07-31 00:00:00.000   1050
670         2013-07-10 00:00:00.000         2013-07-10 00:00:00.000   -350
670         2013-07-24 00:00:00.000         2013-07-26 00:00:00.000   -350
673         2013-06-01 00:00:00.000         2013-07-31 00:00:00.000   233

I need to display the requests on daily basis where volume is summed by day by account for a given date range like for month of July the report is like below. The date start and end dates of the volume requests need to be trimmed for the given report dates

AccountId   Date                         Volume
670         2013-07-01 00:00:00.000      1060
670         2013-07-02 00:00:00.000      1060
.
.
670         2013-07-10 00:00:00.000      710
.
.
670         2013-07-24 00:00:00.000      710
670         2013-07-25 00:00:00.000      710
670         2013-07-26 00:00:00.000      710
.
.
670         2013-07-31 00:00:00.000      1060
673         2013-07-01 00:00:00.000      233
.
.
673         2013-07-31 00:00:00.000      233

Right now I am using table Variables and loops to achieve it which I know is not a good way to code.

DECLARE @sDate DATETIME, @eDate DATETIME , @volume DECIMAL (10, 4),  rstartdate DATETIME, @renddate   DATETIME , @loopcount   INT
SET @sdate = '4/1/2013'
SET @edate = '4/30/2013'

DECLARE @VolumeRequest TABLE 
  ( 
     ID        INT IDENTITY (1, 1) PRIMARY KEY, 
     Aid       INT, 
     Startdate DATETIME, 
     Enddate   DATETIME, 
     volume    DECIMAL (14, 4) 
  ) 
DECLARE @DailyRequest TABLE 
  ( 
     ID        INT IDENTITY (1, 1) PRIMARY KEY, 
     Accountid INT, 
     ReadDate  DATETIME, 
     Volume    DECIMAL (14, 4) 
  ) 

    INSERT INTO @VolumeRequest 
      SELECT Accountid, 
             ( CASE 
                 WHEN @sdate > startdate THEN @sdate 
                 ELSE startdate 
               END ), 
             ( CASE 
                 WHEN @edate < enddate THEN @edate 
                 ELSE enddate 
               END ), 
             dailyvolume 
      FROM   VolumeRequest 
      WHERE  Startdate <= @edate 
             AND Enddate >= @sdate 
             AND isnull (deprecated, 0) != 1 

      --loop to breakdown the volume requests into daily requests 
      SET @loopcount = 1

      WHILE @loopcount <= (SELECT MAX(ID) 
                                FROM   @VolumeRequest) 
        BEGIN 
            SELECT @volume = volume, 
                   @rstartdate = Startdate, 
                   @renddate = Enddate 
            FROM   @VolumeRequest 
            WHERE  ID = @loopcount 

            WHILE @rstartdate <= @renddate 
              BEGIN 
                  INSERT INTO @DailyRequest 
                  SELECT @currentaid, 
                         @rstartdate, 
                         @volume 

                  SET @rstartdate = DATEADD(day, 1, @rstartdate) 
              END 

            SET @LoopCount = @LoopCount + 1 
        END

I am looking for ways which don't involve loops or cursors. I found a Similar Question. The answers there didn't help me.

Answer 1

I like to use a Dates table such as

CREATE TABLE #Dates(
    DateId INT,
    CalendarDate DATETIME)

filled with dates for whatever range you need. I use this table to join to tables such as VolumeRequest to retrieve the output you requested.

SELECT
    v.AccountId,
    d.CalendarDate,
    SUM(v.DailyVolume)
FROM
    #Dates d INNER JOIN
    VolumeRequest v ON
        d.CalendarDate >= v.StartDate AND
        d.CalendarDate <= v.EndDate
group by
    d.CalendarDate,
    v.AccountId

to fill the #Dates table, I use something like this:

declare @startdate datetime = '6/1/13', @enddate datetime = '7/31/13'

create table #Dates(CalendarDate datetime)

insert into #Dates(CalendarDate)
select
    dateadd(dd, rid-1, @startdate) as calendardate
from (
    select
        ROW_NUMBER() over(order by o.object_id) as rid
    From
        sys.objects o cross apply
        sys.objects o2
) dates
where
    dateadd(dd, rid-1, @startdate) >= @startdate and dateadd(dd, rid-1, @startdate) <= @enddate

Modify to meet your date range needs.