时间戳

首页 > 解决方案 > SQL Server中按期间的日期差异分组

sql - SQL Server中按期间的日期差异分组

问题描述

这是数据库表结构

卡号 帐号 活动日期 最后充电日期 到期日
XXX1702 154505100 01-05-2013 02-06-2020 28-02-2021
XXX2148 41805 01-05-2013 02-06-2020 28-02-2021
XXX2694 4844404 01-05-2013 02-06-2020 28-02-2021
XXX6634 44804844888 01-05-2013 02-06-2020 28-02-2021
XXX7160 80445540 01-05-2013 02-06-2020 28-02-2021

这是我的 SQL 查询

 SELECT c.accno,
       5.00
       AS Charge,
       Concat('CARD XX', RIGHT(c.cardno, 4), ' AMC ', Upper(
       Format(c.lastchargedate, 'MMM')),
       Format(c.lastchargedate, 'yy'), ' ', 'to ',
       Upper(Format(Getdate(), 'MMM')), Format(CONVERT(DATE, Getdate()), 'yy'))
       AS
       Period,
       c.lastchargedate,
       c.activedate
FROM   cc c
WHERE  c.lastchargedate IS NOT NULL
       AND Datediff(day, c.lastchargedate, CONVERT(DATE, Getdate())) >= 365

结果如下。

PrimaryAccount  Charge  Period                          LastChargeDate  ActiveDate
104011192124    180.00  CARD XX9912 AMC MAR20 to MAR21  2020-03-03  2018-02-21
104011206784    180.00  CARD XX7122 AMC MAR20 to MAR21  2020-03-03  2019-02-04
104011266381    180.00  CARD XX8064 AMC MAR20 to MAR21  2020-03-03  2017-02-09
104011150218    180.00  CARD XX8775 AMC MAR20 to MAR21  2020-03-03  2017-02-17
104011262467    180.00  CARD XX0510 AMC APR18 to MAR21  2018-04-09  2018-03-21

最后记录显示 4 月 18 日至 3 月 21 日，费用仅为 5

总天数：1076 天因此，费用应为 2 倍 1076/365=2.94 施法后（2.946 AS INT）

如果天数超过 365 天，即如果期间是 4 月 18 日至 3 月 21 日，然后是 4 月 18 日至 3 月 20 日和 4 月 20 日至 3 月 21 日等，如何收取两次费用

IE

104011262467    180.00  CARD XX0510 AMC APR18 to MAR21  2018-04-09  2018-03-21

应该像下面的结果

104011262467    180.00  CARD XX0510 AMC APR18 to MAR19  2018-04-09  2018-03-21
104011262467    180.00  CARD XX0510 AMC APR19 to MAR20  2018-04-09  2018-03-21
104011262467    180.00  CARD XX0510 AMC APR20 to MAR21  2018-04-09  2018-03-21

标签： sqlsql-server

解决方案

您可以将费用乘以年数，这适用于所有情况

SELECT c.accno,
       5.00 * FLOOR(Datediff(day, c.lastchargedate, CONVERT(DATE, Getdate()))/365)
       AS Charge,
       Concat('CARD XX', RIGHT(c.cardno, 4), ' AMC ', Upper(
       Format(c.lastchargedate, 'MMM')),
       Format(c.lastchargedate, 'yy'), ' ', 'to ',
       Upper(Format(Getdate(), 'MMM')), Format(CONVERT(DATE, Getdate()), 'yy'))
       AS
       Period,
       c.lastchargedate,
       c.activedate
FROM   cc c
WHERE  c.lastchargedate IS NOT NULL

推荐阅读