内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用
我需要一些帮助产生一个MS SQL 2012查询,将匹配所需的阶梯步骤输出。行按一个日期范围(帐户提交日期月)汇总数据,列按另一个日期范围(付款日期月)对数据进行汇总。
表1:帐目跟踪为收款而放置的帐户。
CREATE TABLE [dbo].[Accounts]( [AccountID] [nchar](10) NOT NULL, [SubmissionDate] [date] NOT NULL, [Amount] [money] NOT NULL, CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED (AccountID ASC)) INSERT INTO [dbo].[Accounts] VALUES ('1000', '2012-01-01', 1999.00) INSERT INTO [dbo].[Accounts] VALUES ('1001', '2012-01-02', 100.00) INSERT INTO [dbo].[Accounts] VALUES ('1002', '2012-02-05', 350.00) INSERT INTO [dbo].[Accounts] VALUES ('1003', '2012-03-01', 625.00) INSERT INTO [dbo].[Accounts] VALUES ('1004', '2012-03-10', 50.00) INSERT INTO [dbo].[Accounts] VALUES ('1005', '2012-03-10', 10.00)
表2:
CREATE TABLE [dbo].[Trans]( [TranID] [int] IDENTITY(1,1) NOT NULL, [AccountID] [nchar](10) NOT NULL, [TranDate] [date] NOT NULL, [TranAmount] [money] NOT NULL, CONSTRAINT [PK_Trans] PRIMARY KEY CLUSTERED (TranID ASC)) INSERT INTO [dbo].[Trans] VALUES (1000, '2012-01-15', 300.00) INSERT INTO [dbo].[Trans] VALUES (1000, '2012-02-15', 300.00) INSERT INTO [dbo].[Trans] VALUES (1000, '2012-03-15', 300.00) INSERT INTO [dbo].[Trans] VALUES (1002, '2012-02-20', 325.00) INSERT INTO [dbo].[Trans] VALUES (1002, '2012-04-20', 25.00) INSERT INTO [dbo].[Trans] VALUES (1003, '2012-03-24', 625.00) INSERT INTO [dbo].[Trans] VALUES (1004, '2012-03-28', 31.00) INSERT INTO [dbo].[Trans] VALUES (1004, '2012-04-12', 5.00) INSERT INTO [dbo].[Trans] VALUES (1005, '2012-04-08', 7.00) INSERT INTO [dbo].[Trans] VALUES (1005, '2012-04-28', 3.00)
以下是所需输出:
*Total Payments in Each Month* SubmissionYearMonth TotalAmount | 2012-01 2012-02 2012-03 2012-04 -------------------------------------------------------------------- 2012-01 2099.00 | 300.00 300.00 300.00 0.00 2012-02 350.00 | 325.00 0.00 25.00 2012-03 685.00 | 656.00 15.00
前两列和帐户。按月分组的金额。
最后4列是Tran的总和。按月计算,用于当前行的给定月份中放置的帐户。
我一直在处理的查询感觉很接近。我只是没有正确的滞后。下面是我目前正在处理的查询:
Select SubmissionYearMonth, TotalAmount, pt.[0] AS MonthOld0, pt.[1] AS MonthOld1, pt.[2] AS MonthOld2, pt.[3] AS MonthOld3, pt.[4] AS MonthOld4, pt.[5] AS MonthOld5, pt.[6] AS MonthOld6, pt.[7] AS MonthOld7, pt.[8] AS MonthOld8, pt.[9] AS MonthOld9, pt.[10] AS MonthOld10, pt.[11] AS MonthOld11, pt.[12] AS MonthOld12, pt.[13] AS MonthOld13 From ( SELECT Convert(Char(4),Year(SubmissionDate)) + '-' + Right('00' + Convert(VarChar(2), DatePart(Month, SubmissionDate)),2) AS SubmissionYearMonth, SUM(Amount) AS TotalAmount FROM Accounts GROUP BY Convert(Char(4),Year(SubmissionDate)) + '-' + Right('00' + Convert(VarChar(2), DatePart(Month, SubmissionDate)),2) ) AS AccountSummary OUTER APPLY ( SELECT * FROM ( SELECT CASE WHEN DATEDIFF(Month, SubmissionDate, TranDate) < 13 THEN DATEDIFF(Month, SubmissionDate, TranDate) ELSE 13 END AS PaymentMonthAge, TranAmount FROM Trans INNER JOIN Accounts ON Trans.AccountID = Accounts.AccountID Where Convert(Char(4),Year(TranDate)) + '-' + Right('00' + Convert(VarChar(2), DatePart(Month, TranDate)),2) = AccountSummary.SubmissionYearMonth ) as TransTemp PIVOT (SUM(TranAmount) FOR PaymentMonthAge IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13])) as TransPivot ) as pt
它正在产生以下输出:
SubmissionYearMonth TotalAmount MonthOld0 MonthOld1 MonthOld2 MonthOld3 ... 2012-01 2099.00 300.00 NULL NULL NULL ... 2012-02 350.00 325.00 300.00 NULL NULL ... 2012-03 685.00 656.00 NULL 300.00 NULL ...
select a.yyyymm, a.Amount, t201201, t201202, t201203, t201204 from (select LEFT(convert(varchar(255), a.submissiondate, 121), 7) as yyyymm, SUM(a.Amount) as amount from Accounts a group by LEFT(convert(varchar(255), a.submissiondate, 121), 7) ) a left outer join (select LEFT(convert(varchar(255), a.submissiondate, 121), 7) as yyyymm, sum(case when trans_yyyymm = '2012-01' then tranamount end) as t201201, sum(case when trans_yyyymm = '2012-02' then tranamount end) as t201202, sum(case when trans_yyyymm = '2012-03' then tranamount end) as t201203, sum(case when trans_yyyymm = '2012-04' then tranamount end) as t201204 from Accounts a join (select t.*, LEFT(convert(varchar(255), t.trandate, 121), 7) as trans_yyyymm from trans t ) t on a.accountid = t.accountid group by LEFT(convert(varchar(255), a.submissiondate, 121), 7) ) t on a.yyyymm = t.yyyymm order by 1
当两个单元格中有一个0.00的时候,我得到一个空值。
With SubmissionMonths As ( Select DateAdd(d, -Day(A.SubmissionDate) + 1, A.SubmissionDate) As SubmissionMonth , A.Amount From dbo.Accounts As A ) , TranMonths As ( Select DateAdd(d, -Day(Min( T.TranDate )) + 1, Min( T.TranDate )) As TranMonth , 1 As MonthNum From dbo.Accounts As A Join dbo.Trans As T On T.AccountId = A.AccountId Join SubmissionMonths As M On A.SubmissionDate >= M.SubmissionMonth And A.SubmissionDate < DateAdd(m,1,SubmissionMonth) Union All Select DateAdd(m, 1, TranMonth), MonthNum + 1 From TranMonths Where MonthNum < 12 ) , TotalBySubmissionMonth As ( Select M.SubmissionMonth, Sum( M.Amount ) As Total From SubmissionMonths As M Group By M.SubmissionMonth ) Select Format(SMT.SubmissionMonth,'yyyy-MM') As SubmissionMonth, SMT.Total , Sum( Case When TM.MonthNum = 1 Then T.TranAmount End ) As Month1 , Sum( Case When TM.MonthNum = 2 Then T.TranAmount End ) As Month2 , Sum( Case When TM.MonthNum = 3 Then T.TranAmount End ) As Month3 , Sum( Case When TM.MonthNum = 4 Then T.TranAmount End ) As Month4 , Sum( Case When TM.MonthNum = 5 Then T.TranAmount End ) As Month5 , Sum( Case When TM.MonthNum = 6 Then T.TranAmount End ) As Month6 , Sum( Case When TM.MonthNum = 7 Then T.TranAmount End ) As Month7 , Sum( Case When TM.MonthNum = 8 Then T.TranAmount End ) As Month8 , Sum( Case When TM.MonthNum = 9 Then T.TranAmount End ) As Month9 , Sum( Case When TM.MonthNum = 10 Then T.TranAmount End ) As Month10 , Sum( Case When TM.MonthNum = 11 Then T.TranAmount End ) As Month11 , Sum( Case When TM.MonthNum = 12 Then T.TranAmount End ) As Month12 From TotalBySubmissionMonth As SMT Join dbo.Accounts As A On A.SubmissionDate >= SMT.SubmissionMonth And A.SubmissionDate < DateAdd(m,1,SMT.SubmissionMonth) Join dbo.Trans As T On T.AccountId = A.AccountId Join TranMonths As TM On T.TranDate >= TM.TranMonth And T.TranDate < DateAdd(m,1,TM.TranMonth) Group By SMT.SubmissionMonth, SMT.Total