时间戳

首页 > 解决方案 > 将值平均分成 12 个连续的月份 (SQL Server)

sql - 将值平均分成 12 个连续的月份 (SQL Server)

问题描述

我试图将价值平均分配到接下来的连续 12 个月。以下是我目前拥有的，接下来是我想要实现的目标。如果有人可以帮忙解决这个问题，将不胜感激。谢谢！

在此处输入图像描述

Type    Year    January February    March   April   May June    July    August  September   October November    December
Saving  2019    $1,740,000   -   -   -   -   -   -   -   -   -   -   - 
REBATE  2018     -   -   -   -   -   -   -   -  $1,640,000   -   -   - 
REBATE  2019     -   -   -  $12,869      -   -   -   -   -   -   -   -

==================================================== ==========

Request_Name    Type    Year    January February    March   April   May June    July    August  September   October November    December
Microsoft   Saving  2019    $145,000    $145,000    $145,000    $145,000    $145,000    $145,000    $145,000    $145,000    $145,000    $145,000    $145,000    $145,000 
Apple   REBATE  2018     -   -   -   -   -   -   -   -  $136,667    $136,667    $136,667    $136,667 
Apple   REBATE  2019    $136,667    $136,667    $136,667    $136,667    $136,667    $136,667    $136,667    $136,667         -   -   - 
Sony    REBATE  2019     -   -   -  $1,072  $1,072  $1,072  $1,072  $1,072  $1,072  $1,072  $1,072  $1,072 
Sony    REBATE  2020    $1,072  $1,072  $1,072   -   -   -   -   -   -   -   -   -

这是我目前拥有的代码 -

/****** Script for SelectTopNRows command from SSMS ******/
SELECT * 
FROM (
      SELECT TASK_ID,
              datename(month, [START_DATE]) 'Month', 
             [INITIATIVE_TYPE],
             DATEpart(year, [START_DATE]) 'Year',
             sum([TOTAL_SAVINGS]) TotalSavings 
       FROM [PRIME].[dbo].[SAV_FORM_DATA] 
       GROUP BY 
                TASK_ID,
                CATEGORY ,
                [INITIATIVE_TYPE],
                [START_DATE] 
       ) tbl 
PIVOT
   (SUM(totalsavings) 
    FOR [Month] IN ( [January] ,[February] ,[March] ,[April] ,
                     [May] ,[June] ,[July] ,[August] ,[September],
                     [October] ,[November] ,[December])) M

标签： sqlsql-server

解决方案

考虑一个递归查询以迭代地将一个月添加到当前日期直到下一年。然后每个月的行将总储蓄除以 12。

以下是未经测试的 SQL 调整，需要您进一步完善。但是，请参阅Rextester 演示，演示该过程的工作原理。

WITH agg AS (
   SELECT TASK_ID,
          [INITIATIVE_TYPE],
          [START_DATE],
          SUM([TOTAL_SAVINGS]) AS TotalSavings 
   FROM [PRIME].[dbo].[SAV_FORM_DATA] 
   GROUP BY 
          TASK_ID,
          [INITIATIVE_TYPE],
          [START_DATE]
),

dates AS (   
   SELECT agg.TASK_ID, 
          agg.[INITIATIVE_TYPE],
          agg.[START_DATE] AS rec_date, 
          agg.TotalSavings / 12 as TotalMonthSavings
   FROM agg
   UNION ALL
   SELECT agg.mytype, 
          agg.[INITIATIVE_TYPE],
          DATEADD(MONTH, 1, rec_date), 
          agg.TotalSavings / 12
   FROM dates
   INNER JOIN agg
     ON agg.TASK_ID = dates.TASK_ID
     AND agg.[INITIATIVE_TYPE] = dates.[INITIATIVE_TYPE] 
     AND dates.rec_date <  DATEADD(MONTH, 11, agg.[START_DATE])
)


SELECT * 
FROM (
  SELECT TASK_ID, 
         [INITIATIVE_TYPE],
         DATEPART(YEAR, rec_date) AS [Year],
         DATENAME(MONTH, rec_date) AS [Month], 
         TotalMonthSavings
  FROM dates
  ) tbl

PIVOT
   (SUM(TotalMonthSavings) 
    FOR [Month] IN ([January], [February], [March], [April],
                    [May], [June], [July], [August], [September],
                    [October], [November], [December])
   ) M
ORDER BY TASK_ID, 
         [INITIATIVE_TYPE], 
         [Year]

推荐阅读