时间戳

首页 > 解决方案 > 将第一行十进制值添加到 SQL Server 中的下一行

sql - 将第一行十进制值添加到 SQL Server 中的下一行

问题描述

我有如下表格，其中包含该期间的客户用电量。可用数据如 

OwnerID StartDate   EndDate     Volume
---------------------------------------
 1      2019-01-01  2019-01-15  10.40
 1      2019-01-16  2019-01-31   5.80
 1      2019-02-01  2019-02-10   7.90
 1      2019-02-11  2019-02-28   8.50

为此OwnerID = 1，现有一些体积 0.90。这应该适用于第一行（添加）

  1. 获取整数和小数部分的值
  2. 第一行返回的十进制值应该添加到下一行，反之亦然。

现有量 - 0.90

所以预期的结果集应该是：

OwnerId StartDate   EndDate     CalulatedVolume  AppliedExistingVolume(0.90)   RemainExistingVolume 
----------------------------------------------------------------------------------------------------
    1   2019-01-01  2019-01-15         11                0.60                   0.30
    1   2019-01-16  2019-01-31          6                0.20                   0.10
    1   2019-02-01  2019-02-10          8                0.10                   0.00
    1   2019-02-11  2019-02-28          8                0.00                   0.50

你能建议如何在 SQL 查询中实现这一点吗？

标签： sqlsql-server

解决方案

下面的示例使用累积和和一些 CTE 来计算这 2 列。

样本数据：

create table yourtable
(
  Id int identity(1,1) primary key,
  OwnerID int not null, 
  StartDate date not null, 
  EndDate date not null, 
  Volume decimal(16,2) not null
);

insert into yourtable
 (OwnerID, StartDate, EndDate, Volume) values       
  (1, '2019-01-01', '2019-01-15', 10.40)
, (1, '2019-01-16', '2019-01-31', 5.80)
, (1, '2019-02-01', '2019-02-10', 7.90)
, (1, '2019-02-11', '2019-02-28', 8.50)
;

询问：

;with CTE_EXISTING_VOLUMES AS
(
   select *
   from (values
     (1, 0.9)
   ) q(OwnerId, ExistingVolume)
)
, CTE_DATA AS
(
  select t.*, c.ExistingVolume
  , ceiling(t.Volume) - t.Volume as VolumeRemainder
  , sum(ceiling(t.Volume) - t.Volume) 
      over (partition by t.OwnerID
            order by t.StartDate) as CumSumVolumeRemainder
  from yourtable t
  left join CTE_EXISTING_VOLUMES c
    on t.OwnerID = c.OwnerID
)
, CTE_DATA2 AS
(
  select *
  , ceiling(Volume) as CalculatedVolume
  , IIF(ExistingVolume < CumSumVolumeRemainder, 0.0, (ceiling(Volume) - Volume)) as AppliedExistingVolume
  from CTE_DATA
)
select OwnerId, StartDate, EndDate
, CalculatedVolume
, AppliedExistingVolume
, case
  when AppliedExistingVolume > 0
  then ExistingVolume - CumSumVolumeRemainder
  else VolumeRemainder
  end as RemainExistingVolume
from CTE_DATA2;

GO

所有者 ID | 开始日期 | 结束日期 | 计算量 | 应用现有卷 | RemainExistingVolume
------: | :----------------- | :----------------- | :--------------- | :-------------------- | :--------------------
      1 | 01/01/2019 00:00:00 | 15/01/2019 00:00:00 | 11 | 0.60 | 0.30                
      1 | 16/01/2019 00:00:00 | 31/01/2019 00:00:00 | 6 | 0.20 | 0.10                
      1 | 01/02/2019 00:00:00 | 2019 年 10 月 2 日 00:00:00 | 8 | 0.10 | 0.00                
      1 | 2019 年 11 月 2 日 00:00:00 | 28/02/2019 00:00:00 | 9 | 0.00 | 0.50

db<>在这里摆弄

推荐阅读