将日期范围转换为单日

一尘不染

将日期范围转换为单日

sql

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

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月份的月份），按帐户按天对交易量进行汇总，如下所示。需要针对给定的报告日期修剪批量请求的日期开始日期和结束日期

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

现在，我正在使用表变量和循环来实现它，我知道这不是编码的好方法。

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

我正在寻找不涉及循环或游标的方法。我发现了一个类似的问题。那里的答案并没有帮助我。

阅读 175

2021-03-17

共1个答案

一尘不染

我喜欢使用Dates表，例如

CREATE TABLE #Dates(
    DateId INT,
    CalendarDate DATETIME)

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

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表，我使用类似以下的方法：

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

修改以满足您的日期范围需求。

2021-03-17