Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Stairstep Query

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

                                 *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

The first two columns sum Account.Amount grouping by month.

The last 4 columns sum the Tran.TranAmount, by month, for Accounts placed in the given month of the current row.

The query I've been working with feel close. I just don't have the lag correct. Here's the query I'm working with thus far:

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

It's producing the following output:

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      ...

As for the column date headers. I'm not sure what the best option is here. I could add an additional set of columns and create a calculated value that I could use in the resulting report.

SQL Fiddle: http://www.sqlfiddle.com/#!6/272e5/1/0

like image 223
Nathan Avatar asked Jun 26 '12 21:06

Nathan


People also ask

How do I get a list of SQL Agent jobs?

To view job activity In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. Expand SQL Server Agent. Right-click Job Activity Monitor and click View Job Activity. In the Job Activity Monitor, you can view details about each job that is defined for this server.

How can I get job history in SQL Server?

Using SQL Server Management Studio Expand SQL Server Agent, and then expand Jobs. Right-click a job, and then click View History. In the Log File Viewer, view the job history. To update the job history, click Refresh.

How do you write a transpose query in SQL?

The XML solution to transposing rows into columns is basically an optimal version of the PIVOT in that it addresses the dynamic column limitation. The XML version of the script addresses this limitation by using a combination of XML Path, dynamic T-SQL and some built-in functions (i.e. STUFF, QUOTENAME).

How do I find the scheduled jobs in SQL Server query?

EVENTS table, so you can run "SELECT * FROM INFORMATION_SCHEMA. EVENTS" to see the list of scheduled jobs and information about them.


2 Answers

Since you are using SQL Server 2012, we can use the Format function to make the date pretty. There is no need to group by the strings. Instead, I find it useful to use the proper data type for as long as I can and only use Format or Convert on display (or not at all and let the middle tier handle the display).

In this solution, I arbitrarily assumed the earliest TransDate and extract from it, the first day of that month. However, one could easily replace that expression with a static value of the start date desired and this solution would take that and the next 12 months.

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

SQL Fiddle version

like image 105
Thomas Avatar answered Sep 22 '22 03:09

Thomas


The following query pretty much returns what you want. You need to do the to operations separately. I just join the results together:

 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

I am getting a NULL where you have a 0.00 in two cells.

like image 31
Gordon Linoff Avatar answered Sep 22 '22 03:09

Gordon Linoff