数据透视表中的每月总和 [英] Monthly sum in pivot table

问题描述

伙计们我有问题我已经尝试了很多东西，谷歌找到了somthing但不适合我所以问题是我想做选择，将得到值的总和，并获得它超过

Guys i have question i have tried so many things serached in google found somthing but not for me so the question is that i want to make select that will get sum of values and get it monthly that is more than

@veriable(data)

价值约12个月只有soo我写了一些代码但不适合我，plz帮我出门有人



我尝试过的事情：

value about 12 month only soo i have written some code but not working for me , plz help me out someone

What I have tried:

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @cols2 AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
DECLARE @year DATE

SET @year='2017-04-01 00:00:00.000'
SELECT @cols = STUFF(
                        (   SELECT DISTINCT ',' + CONVERT(NVARCHAR(20),DATENAME(MONTH, Date))
 FROM dbo.Reestr LEFT JOIN dbo.Source AS j ON Sender_Id=j.ID 
WHERE (Date BETWEEN @year and dateadd(month,12,@year ))

 AND (Op_Type_Id=1 OR Op_Type_Id=3 OR Op_Type_Id=5)
GROUP BY SOURCE,DATENAME(MONTH, Date)	 
                            FOR xml path('')
                        )
                        , 1
                        , 1
                        , '')

						SELECT @cols
SELECT @query = 
'DECLARE @year DATE

SET @year=''2017-04-01 00:00:00.000''
SELECT *
FROM (SELECT   j.Source,
       DATENAME(MONTH, Date) [Month],
     ISNULL(SUM(Value), 0) AS value
      FROM dbo.Reestr LEFT JOIN dbo.Source AS j ON Sender_Id=j.ID WHERE 
	  (Date BETWEEN @year and dateadd(month,12,@year ))
	   AND (Op_Type_Id=1 OR Op_Type_Id=3 OR Op_Type_Id=5)
	   GROUP BY SOURCE,Value,
	 DATENAME(MONTH, Date)) mon 
PIVOT 
(
    sum(value)
    FOR [Month] IN  (' + @cols + ')) Piv'



EXEC SP_EXECUTESQL @query

推荐答案

检查：

Check this:

--define fiscal year start and end
DECLARE @fiscalYearStart DATE = CONVERT(DATE, '2017-04-01')
DECLARE @fiscalYearEnd DATE = DATEADD(MM, 12, @fiscalYearStart)
--variable to store column names: [2017-04-01],[2017-05-01],[2017-06-01],...
DECLARE @cols NVARCHAR(2000) = N'';
--recursive query to produce column names
;WITH Dates AS
(
	SELECT @fiscalYearStart AS CommonDate
	UNION ALL
	SELECT DATEADD(MM, 1, CommonDate) AS CommonDate
	FROM Dates
	WHERE DATEADD(MM, 1, CommonDate)<@fiscalYearEnd
)
SELECT @cols = STUFF((SELECT '],[' + CONVERT(NVARCHAR(10),CommonDate) 
	FROM Dates
	FOR XML PATH('')), 1, 2, '') + ']'

--variable to store pivot query
DECLARE @qry NVARCHAR(MAX) = N'SELECT ' + @cols + 
    ' FROM (' + 
    '    SELECT j.[Value], CAST(DATEADD(DAY,-DAY(j.[Date])+1, CAST(j.[Date] AS DATE)) AS DATE) AS CommonMonthDate' + 
    '    FROM FROM dbo.Reestr AS r LEFT JOIN dbo.Source AS j ON r.Sender_Id=j.ID' +
    '    WHERE r.Op_Type_Id IN(1, 3, 5)' +
    ') AS DT' +
    'PIVOT(SUM(Value) FOR CommonMonthDate IN(' + @cols + ')) AS PVT'

EXEC(@qry)

注意：请分别更改表别名（j或r）！



我确实使用过智能技巧： sqlauthority.com:SQL SERVER - 查找本月第一天的脚本 [ ^ ]转换每个日期常见形式：每月第一天能够以月度方式转换数据。

Note: Please, change table aliases (j or r) respectively!

I did use smart trick from: sqlauthority.com: SQL SERVER – Script to Find First Day of Current Month[^] to convert each date into common form: first day in month to bea able to pivot data in monthly manner.

这篇关于数据透视表中的每月总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！