Edit Question - KnowledgeHub

Title Body

I need some help producing a MS SQL 2012 query that will match the desired stair-step output. The rows summarize data by one date range (account submission date month), and the columns summarize it by another date range (payment date month) Table 1: Accounts tracks accounts placed for collections. 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) Table 2: Trans tracks payments made 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) Here's what the desired output should look like *T

Tags (comma-separated) Cancel