将日期范围转换为单日 [英] Convert Date Range to Individual Days

问题描述

称为 VolumeRequest 的表按日期范围存储帐户的批量请求。

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

我需要每天显示请求，其中在给定日期范围内，按帐户按天对交易量求和，例如7月像下面一样。需要针对给定的报告日期修剪批量请求的日期开始日期和结束日期

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

现在我正在使用table我知道，通过变量和循环来实现它并不是一种好的编码方法。

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.

推荐答案

我喜欢使用日期表，例如

I like to use a Dates table such as

CREATE TABLE #Dates(
    DateId INT,
    CalendarDate DATETIME)

根据您需要的范围填充日期。我使用此表连接到诸如VolumeRequest之类的表，以检索您请求的输出。

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

填写#Dates表，我使用类似这样的东西：

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.

这篇关于将日期范围转换为单日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！