如何在 SQL 中将月份拆分为一个月的前 10 天、接下来的 10 天和剩余天数

本文关键字：一个 余天 接下来 SQL 拆分 | 更新日期: 2023-09-27 18:35:43

此查询将在SQL中按月份和年份分组。我想要对这个问题进行选择查询，用户将在其中输入FromDate和ToDate：

我正在测试的查询：

select 
    year(CDate) as Payment_year, 
    count(distinct CDate) as count_days,
    month(CDate) as month_name,
    isnull(sum(Amount),0.00) as Total_Amount 
from 
    tblCowMilk
where 
    CDate between '2016-01-01' and '2016-03-31' 
group by 
    year(CDate),
    month(CDate),
    (datepart(day,CDate))/11 
order by 
    month_name

在此处输入图像描述

我想要这样的结果

                    Payment Register for the Period: 01-05-2015 to 23-02-2016         
 -----------------------------------------------------------------------------------------
 MONTH      days      I PERIOD    days   II PERIOD   day   III PERIOD      TOTAL  
 -----------------------------------------------------------------------------------------
May           10         10.00     10      160.00       11       0.00         170.00
June                     30.00               0.00                0.00          30.00
July                     20.00               0.00               10.00          30.00
August                    0.00               0.00                0.00           0.00
September                10.00               0.00                0.00          10.00
October                   0.00               0.00                0.00           0.00
November                  0.00               0.00                0.00           0.00
December                  0.00               0.00                0.00           0.00
January                   0.00               0.00                0.00           0.00
February                  0.00               0.00                0.00           0.00
-----------------------------------------------------------------------------------------
                         70.00             160.00               10.00         240.00
-----------------------------------------------------------------------------------------

如何在 SQL 中将月份拆分为一个月的前 10 天、接下来的 10 天和剩余天数

你计算 10 天组的逻辑是错误的，目前是

1-10
11-21
22-end of month

这应该返回正确的结果：

select 
    year(CDate) as Payment_year, 
    count(* as count_days,
    month(CDate) as month_name,
    sum(case when period = 1 then Total_Amount else 0 end) as Period_I,
    sum(case when period = 2 then Total_Amount else 0 end) as Period_II,
    sum(case when period = 3 then Total_Amount else 0 end) as Period_III,
    isnull(sum(Total_Amount),0.00) as Total_Amount
from
 (
   select 
      CDate,
      case -- 10 days within a period
         when datepart(day,CDate) <= 10 then 1
         when datepart(day,CDate) <= 20 then 3
         else 3
      end as period,
     sum(Amount) as Total_Amount
   from 
      tblCowMilk
   where 
      CDate between '2016-01-01' and '2016-03-31' 
   group by 
      CDate,
      case
         when datepart(day,CDate) <= 10 then 1
         when datepart(day,CDate) <= 20 then 3
         else 3
      end
 ) as dt
group by year(CDate), month(CDate)
order by year(CDate), month(CDate)

要获得总计，您需要标准SQL的扩展分组，我不知道SQL Server 2012是否完全支持此功能：

group by
   grouping sets ((year(CDate),month(CDate)),())
order by
   grouping(year(CDate)), year(CDate),
   grouping(month(CDate)), month(CDate)

或者在您的应用程序中做总计。

试试这个

select 
    year(a.CDate) as Payment_year, 
    month(a.CDate) as month_name,
    COUNT(b.Amount) as 'I Period',
    COUNT(c.Amount) as 'II Period',
    COUNT(d.Amount) as 'II Period',
    isnull(sum(a.Amount),0.00) as Total_Amount 
from 
    tblCowMilk a 
left join tblCowMilk d on d.CDate = a.CDate and DAY(d.CDate) < 11
left join tblCowMilk b on b.CDate = a.CDate and DAY(b.CDate) >10  and DAY(b.CDate) <21
left join tblCowMilk c on c.CDate = a.CDate  and DAY(c.CDate) >20
where 
    a.CDate between '2016-01-01' and '2016-03-31' 
group by 
    year(a.CDate),
    month(a.CDate),
order by 
    month_name
相关文章：