时间戳

首页 > 解决方案 > 根据日历季度重新计算日期和金额

sql - 根据日历季度重新计算日期和金额

问题描述

我有这样的表格和数据

数量 开始日期 结束日期
30 2021 年 1 月 2 日 2021 年 4 月 31 日
30 2021 年 1 月 5 日 2021 年 7 月 31 日
30 2021 年 1 月 8 日 2021 年 10 月 31 日
30 2021 年 1 月 11 日 2022 年 1 月 31 日

我想重新计算金额以获得以下输出

数量 开始日期 结束日期
20 2021 年 1 月 1 日 2021 年 3 月 31 日
30 2021 年 1 月 4 日 2021 年 6 月 31 日
30 2021 年 1 月 7 日 2021 年 9 月 31 日
30 2021 年 1 月 10 日 2022 年 12 月 31 日
10 2022 年 1 月 1 日 2022 年 3 月 31 日

查询应重新计算日历季度的金额。前任。在第一行，开始日期是 2 月 1 日，结束日期是 4 月 31 日。将此金额转换为第一个日历季度（1 月 1 日至 3 月 31 日），计算为：30/3*2 = 20

实现这一目标的最有效方法是什么？

标签： sqlsql-server

解决方案

使用此处找到的公式：http: //zarez.net/ ?p=2484 ，得到一个季度的第一天：SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)

以及一个季度的最后一天：SELECT DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +1, 0))

CREATE TABLE Q(amount DECIMAL(8,2), "start date" DATE, "end date" DATE);

INSERT INTO Q VALUES 
(30,    '2021/02/01',   '2021/04/30'),
(30,    '2021/05/01',   '2021/07/31'),
(30,    '2021/08/01',   '2021/10/30'),
(30,    '2021/11/01',   '2022/01/31');



WITH CTE AS (
   SELECT 
      [amount],
      [start date], 
      [end date],
      DATENAME(Quarter, [start date]) StartQuarter
   FROM Q
)
SELECT
   SUM(amount),
   StartDate,
   EndDate
FROM (
SELECT
   amount * (DATEDIFF(m,DATEADD(qq, DATEDIFF(qq, 0, [start date]), 0),[start date]))/3.0 as amount,
   DATEADD(qq, DATEDIFF(qq, 0, [start date]), 0)       as "StartDate",
   DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, [start date]) +1, 0)) as "EndDate"
FROM CTE
UNION ALL
SELECT
   amount * (1-(DATEDIFF(m,DATEADD(qq, DATEDIFF(qq, 0, [start date]), 0),[start date]))/3.0) as amount,
   DATEADD(qq, DATEDIFF(qq, 0, [end date]), 0)       as "StartDate",
   DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, [end date]) +1, 0)) as "EndDate"
FROM CTE
) x 
GROUP BY StartDate, EndDate

推荐阅读