A Typical Stored Procedure…

So, here’s a typical SP that I’ve inherited.   And this one is about medium in size, and one of the “cleaner” ones.  Comments? 

The first thing you will notice is the minimal use of PK/FK relations in favour of “SELECT INTO… #temp” along with “UNION”.  Messy.  And this is one of the better ones.  Do any of you other DBA’s run into this kind of code?  If so, what’s your approach.  Personally, the first thing I am looking for when presented a new SP is 1) The FROM clauses…, and 2) Any occurance of ” SELECT INTO”, and 3) uses of UPDATE and DELETE.  The first thing I want to know about a proc when I open it is “Does this query MODIFY data?  Or just SELECT data to be returned in the resultset?”

Things I am concerned with in this proc are things like,

  • The Use of “SELECT DISTINCT” on the left side of the equation, and
  • Repeated selection of columns with aliases, and
  • “Order By” using a column position number instead of a column name.

SELECT DISTINCT on the left side of the JOIN (or, in this case on the “main table”) indicate a potential problem with the data.  The main table’s rows ought to be able to be unique.  The repeated selection of columns with aliases (and especially, re-aliasing the same column with a different name each itteration) is, to me, an invite to trouble.  And grouping by a number is just plain not conducive to clear coding.  Maybe “1” is what you think it is.  Maybe it isn’t.  But “GROUP BY tha_account_number” cannot be misunderstood.

Disclaimer: This is NOT my code.  I didn’t write this.  This is an example of something I inherited.  Don’t think this is an example of my coding style.

USE [prod_tha_database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[rpt_populate_tha]
    @runAsOfDate DATETIME
AS
    BEGIN

        DECLARE @ed DATETIME,
            @sd DATETIME,
            @lastPeriod INT,
            @ThisPeriod INT,
            @thisYear INT,
            @ThisFY INT,
            @ThisFYMonth INT,
            @LastPeriodYear INT
           

        SET @sd = DATEADD(MONTH, -1,
                          dbo.ufn_getfirstdayofmonth(dbo.ufn_Midnight(@RunAsOfDate)))
        SET @ed = dbo.ufn_getlastdayofmonth(@sd)
        SET @ed = DATEADD(DAY, 1, @ed)
        SET @ed = DATEADD(millisecond, -4, @ed)
        SELECT  @lastPeriod = MONTH(@sd),
                @LastPeriodYear = YEAR(@sd),
                @ThisPeriod = MONTH(@RunAsOfDate)
        SELECT  @thisYear = YEAR(@RunAsOfDate)
        IF @ThisPeriod >= 10
            BEGIN
                SET @ThisFY = @thisYear + 1
                SET @ThisFYMonth = @ThisPeriod - 9
            END
        ELSE
            BEGIN
                SET @ThisFY = @thisYear
                SET @ThisFYMonth = @ThisPeriod + 3
            END
        DECLARE @dt_startdate datetime                                                          
        DECLARE @dt_enddate datetime
        SET @dt_startdate = @sd
        SET @dt_enddate = @ed
        Select distinct
                l.tha_no,
                l.tha_skey,
                l.tha_class_code,
                t2.create_date,
                isnull(( Select sum(round(isnull(principal_amt, 0), 2))
                         from   a_cert_trans t,
                                a_cert c
                         where  c.cert_skey = t.cert_skey
                                and c.cert_type = 'Principal'
                                and c.tha_skey = l.tha_skey
                                and t.cert_trans_skey = t2.cert_trans_skey
                                and t.create_date < dateadd(d, 1, @dt_enddate)
                       ), 0) as 'Principal',
                isnull(( Select sum(round(isnull(int_amt, 0), 2))
                         from   a_cert_trans t,
                                a_cert c
                         where  c.cert_skey = t.cert_skey
                                and c.cert_type = 'Principal'
                                and c.tha_skey = l.tha_skey
                                and t.cert_trans_skey = t2.cert_trans_skey
                                and t.create_date < dateadd(d, 1, @dt_enddate)
                       ), 0) as 'Int_Due',
                isnull(( Select sum(round(isnull(principal_amt, 0), 2))
                         from   a_cert_trans t,
                                a_cert c
                         where  c.cert_skey = t.cert_skey
                                and c.cert_type = 'Tax Escrow'
                                and c.tha_skey = l.tha_skey
                                and t.cert_trans_skey = t2.cert_trans_skey
                                and t.create_date < dateadd(d, 1, @dt_enddate)
                       ), 0) as 'Tax_Escrow',
                isnull(( Select sum(round(isnull(principal_amt, 0), 2))
                         from   a_cert_trans t,
                                a_cert c
                         where  c.cert_skey = t.cert_skey
                                and c.cert_type = 'Serv Fee'
                                and c.tha_skey = l.tha_skey
                                and t.cert_trans_skey = t2.cert_trans_skey
                                and t.create_date < dateadd(d, 1, @dt_enddate)
                       ), 0) as 'Serv_Fee',
                isnull(( Select sum(round(isnull(principal_amt, 0), 2))
                         from   a_cert_trans t,
                                a_cert c
                         where  c.cert_skey = t.cert_skey
                                and c.cert_type = 'Late Fee'
                                and c.tha_skey = l.tha_skey
                                and t.cert_trans_skey = t2.cert_trans_skey
                                and t.create_date < dateadd(d, 1, @dt_enddate)
                       ), 0) as 'Late_Fee',
                isnull(( Select sum(round(isnull(principal_amt, 0), 2))
                         from   a_cert_trans t,
                                a_cert c
                         where  c.cert_skey = t.cert_skey
                                and c.cert_type = 'Other Adv'
                                and c.tha_skey = l.tha_skey
                                and t.cert_trans_skey = t2.cert_trans_skey
                                and t.create_date < dateadd(d, 1, @dt_enddate)
                       ), 0) as 'Other_Adv',
                isnull(( Select sum(round(isnull(principal_amt, 0), 2))
                         from   a_cert_trans t,
                                a_cert c
                         where  c.cert_skey = t.cert_skey
                                and c.cert_type = 'Tax Adv'
                                and c.tha_skey = l.tha_skey
                                and t.cert_trans_skey = t2.cert_trans_skey
                                and t.create_date < dateadd(d, 1, @dt_enddate)
                       ), 0) as 'Tax_Adv',
                isnull(( Select sum(round(isnull(principal_amt, 0), 2))
                         from   a_cert_trans t,
                                a_cert c
                         where  c.cert_skey = t.cert_skey
                                and c.cert_type = 'INSF'
                                and c.tha_skey = l.tha_skey
                                and t.cert_trans_skey = t2.cert_trans_skey
                                and t.create_date < dateadd(d, 1, @dt_enddate)
                       ), 0) as 'INSF',
                isnull(( Select sum(round(isnull(principal_amt, 0), 2))
                         from   a_cert_trans t,
                                a_cert c
                         where  c.cert_skey = t.cert_skey
                                and c.cert_type = 'Suspense'
                                and c.tha_skey = l.tha_skey
                                and t.cert_trans_skey = t2.cert_trans_skey
                                and t.create_date < dateadd(d, 1, @dt_enddate)
                       ), 0) as 'Suspense',
                g.trans_code,
                g.action_code,
                g.trans_desc,
                l.mort_insur_cert_date as Endorsement_Date,
                CASE WHEN month(l.mort_insur_cert_date) > '9'
                     THEN Year(l.mort_insur_cert_date) + 1
                     ELSE Year(l.mort_insur_cert_date)
                END BUDGET_YEAR,
                CASE WHEN left(l.gl_matrix, 1) = 'M'
                          or left(l.gl_matrix, 1) = 'F' THEN 'M'
                     WHEN left(l.gl_matrix, 1) = 'S' THEN 'S'
                     ELSE 'G'
                END 'FUND_CODE',
                l.gl_matrix
        INTO    #TEMP1
        from    a_tha l,
                a_cert c,
                a_cert_trans t2,
                r_trans_seg2 g with ( NoLock )
        where   l.tha_program_skey = 30
                and l.tha_case_no NOT in ( '000-0000000', '7777777777',
                                           '3333333100' )
                and c.tha_skey = l.tha_skey
                and c.cert_skey = t2.cert_skey
                and t2.trans_seg2 = g.trans_seg2
                and t2.status = 'P'
                and t2.create_date >= @dt_startdate
                and t2.create_date < dateadd(d, 1, @dt_enddate)                                                   
                                                               
--SELECT * FROM #temp1 ORDER BY 5 desc

        SELECT  T.TRANS_CODE,
                t.tha_no,
                t.tha_skey,
                CASE WHEN t.tha_class_code IN ( 'AP', 'FE', 'PM' ) THEN 'PM'
                     ELSE t.tha_class_code
                END tha_class_code,
                count(distinct t.tha_no) cnt,
                sum(t.principal) principal
        INTO    #DETAIL
        FROM    #TEMP1 t
        group by T.TRANS_CODE,
                t.tha_class_code,
                t.tha_no,
                t.tha_skey
        having  sum(t.principal) <> 0
        order by 1                                                          

                                                                                                                                                               
        Select  l.tha_no,
                L.tha_SKEY,
                l.tha_class_code,
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Principal'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                ) as 'Principal',
                ( Select    isnull(sum(round(isnull(int_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Principal'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                ) as 'Int Due',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Tax Escrow'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                ) as 'Tax Escrow',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Serv Fee'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                ) as 'Serv Fee',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Late Fee'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                ) as 'Late Fee',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Other Adv'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                ) as 'Other Adv',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Tax Adv'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                ) as 'Tax Adv',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'INSF'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                ) as 'INSF',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Suspense'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                ) as 'Suspense',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Principal'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                ) + ( Select    isnull(sum(round(isnull(int_amt, 0), 2)), 0)
                      from      a_cert_trans t,
                                a_cert c
                      where     c.cert_skey = t.cert_skey
                                and c.cert_type = 'Principal'
                                and c.tha_skey = l.tha_skey
                                and t.create_date < dateadd(d, 1, @dt_enddate)
                    )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Tax Escrow'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Serv Fee'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Late Fee'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Other Adv'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Tax Adv'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'INSF'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Suspense'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                  ) Total,
                l.mort_insur_cert_date as Endorsement_Date,
                CASE WHEN month(l.mort_insur_cert_date) > '9'
                     THEN Year(l.mort_insur_cert_date) + 1
                     ELSE Year(l.mort_insur_cert_date)
                END BUDGET_YEAR,
                CASE WHEN left(l.gl_matrix, 1) = 'M'
                          or left(l.gl_matrix, 1) = 'F' THEN 'M'
                     WHEN left(l.gl_matrix, 1) = 'S' THEN 'S'
                     ELSE 'G'
                END 'FUND_CODE',
                l.gl_matrix
        INTO    #TEMP2BB
        from    a_tha l,
                a_cert c
        where   l.tha_program_skey = 30
                and l.tha_skey = c.tha_skey
                and c.cert_type = 'Principal'
                and l.create_date < dateadd(d, 1, @dt_enddate)
                and l.tha_no not in ( '444444444', '555555555', '777777777',
                                       '888888888', '999999999', '333333310' )
        group by l.tha_skey,
                l.tha_no,
                l.tha_class_code,
                l.gl_matrix,
                l.mort_insur_cert_date
        having  ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Principal'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                ) + ( Select    isnull(sum(round(isnull(int_amt, 0), 2)), 0)
                      from      a_cert_trans t,
                                a_cert c
                      where     c.cert_skey = t.cert_skey
                                and c.cert_type = 'Principal'
                                and c.tha_skey = l.tha_skey
                                and t.create_date < dateadd(d, 1, @dt_enddate)
                    )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Tax Escrow'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Serv Fee'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Late Fee'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Other Adv'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Tax Adv'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'INSF'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Suspense'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, 1, @dt_enddate)
                  ) <> 0
        order by l.tha_no                                                                                                                                                                         
                                                                                                                                                                               
        SELECT  L.tha_SKEY,
                l.tha_no,
                case when l.tha_class_code in ( 'AP', 'FE', 'PM' ) then 'PM'
                     else l.tha_class_code
                end tha_class_code,
                count(distinct L.tha_SKEY) cnt,
                sum(principal) principal
        INTO    #DETAILBB
        FROM    #TEMP2BB t,
                a_tha l
        where   t.tha_SKEY = L.tha_SKEY
        group by l.tha_class_code,
                L.tha_SKEY,
                l.tha_no
        having  sum(t.principal) <> 0                                                                                                                                                                        
                                                                                                                                                                               
------------------------------------------------------------------------------------------------------

                                                                                                                                                               
        Select  l.tha_no,
                L.tha_SKEY,
                l.tha_class_code,
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Principal'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                ) as 'Principal',
                ( Select    isnull(sum(round(isnull(int_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Principal'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                ) as 'Int Due',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Tax Escrow'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                ) as 'Tax Escrow',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Serv Fee'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                ) as 'Serv Fee',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Late Fee'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                ) as 'Late Fee',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Other Adv'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                ) as 'Other Adv',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Tax Adv'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                ) as 'Tax Adv',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'INSF'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                ) as 'INSF',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Suspense'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                ) as 'Suspense',
                ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Principal'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                )
                + ( Select  isnull(sum(round(isnull(int_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Principal'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Tax Escrow'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Serv Fee'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Late Fee'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Other Adv'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Tax Adv'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'INSF'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Suspense'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  ) Total,
                l.mort_insur_cert_date as Endorsement_Date,
                CASE WHEN month(l.mort_insur_cert_date) > '9'
                     THEN Year(l.mort_insur_cert_date) + 1
                     ELSE Year(l.mort_insur_cert_date)
                END BUDGET_YEAR,
                CASE WHEN left(l.gl_matrix, 1) = 'M'
                          or left(l.gl_matrix, 1) = 'F' THEN 'M'
                     WHEN left(l.gl_matrix, 1) = 'S' THEN 'S'
                     ELSE 'G'
                END 'FUND_CODE',
                l.gl_matrix
        INTO    #TEMP2BB0
        from    a_tha l,
                a_cert c
        where   l.tha_program_skey = 30
                and l.tha_skey = c.tha_skey
                and c.cert_type = 'Principal'
                and l.create_date < dateadd(d, -1, @dt_startdate)
                and l.tha_no not in ( '444444444', '555555555', '777777777',
                                       '888888888', '999999999', '333333310' )
        group by l.tha_skey,
                l.tha_no,
                l.tha_class_code,
                l.gl_matrix,
                l.mort_insur_cert_date
        having  ( Select    isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                  from      a_cert_trans t,
                            a_cert c
                  where     c.cert_skey = t.cert_skey
                            and c.cert_type = 'Principal'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                )
                + ( Select  isnull(sum(round(isnull(int_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Principal'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Tax Escrow'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Serv Fee'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Late Fee'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Other Adv'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Tax Adv'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'INSF'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  )
                + ( Select  isnull(sum(round(isnull(principal_amt, 0), 2)), 0)
                    from    a_cert_trans t,
                            a_cert c
                    where   c.cert_skey = t.cert_skey
                            and c.cert_type = 'Suspense'
                            and c.tha_skey = l.tha_skey
                            and t.create_date < dateadd(d, -1, @dt_startdate)
                  ) <> 0
        order by l.tha_no                                                                                                                                                                         
                                                                                                                                                                               
        SELECT  L.tha_SKEY,
                l.tha_no,
                case when l.tha_class_code in ( 'AP', 'FE', 'PM' ) then 'PM'
                     else l.tha_class_code
                end tha_class_code,
                count(distinct L.tha_SKEY) cnt,
                sum(principal) principal
        INTO    #DETAILBB0
        FROM    #TEMP2BB0 t,
                a_tha l
        where   t.tha_SKEY = L.tha_SKEY
        group by l.tha_class_code,
                L.tha_SKEY,
                l.tha_no
        having  sum(t.principal) <> 0                                                                                                                                                                        
        SELECT  sum(CNT) [NUMBER],
                sum(principal) PRIN
        INTO    #TEMP_OLD
        FROM    #DETAILBB0          

------------------------------------------------------------------------------------------------------                                                                                                                                                     

        select  x.*
        into    #delta
        from    ( SELECT    '7- ' + CONVERT(VARCHAR(10), dateadd(d, -1, @dt_startdate), 101) [CAPTION],
                            -SUM(NUMBER) [NUMBER],
                            -SUM(PRIN) [AMOUNT]
                  FROM      #temp_old
                  UNION
                  SELECT    '8- ' + CONVERT(VARCHAR(10), @dt_enddate, 101) [CAPTION],
                            count(*) [NUMBER],
                            SUM(PRINCIPAL) [AMOUNT]
                  FROM      #DETAILBB
                ) x
        SELECT  Z.*
        INTO    #REPORT1
        FROM    ( SELECT    '1- ' + CONVERT(VARCHAR(10), dateadd(d, -1, @dt_startdate), 101) [CAPTION],
                            SUM(NUMBER) [NUMBER],
                            SUM(PRIN) [AMOUNT]
                  FROM      #temp_old
                  UNION
                  SELECT    '2- PAYOFFS              ' [CAPTION],
                            -ISNULL(COUNT(tha_NO), 0) [NUMBER],
                            ISNULL(SUM(PRINCIPAL), 0) [AMOUNT]
                  FROM      #DETAIL t
                  WHERE     t..trans_code = 'SHP'
                  UNION
                  SELECT    '3- CONVERSIONS' [CAPTION],
                            -ISNULL(COUNT(tha_NO), 0) [NUMBER],
                            0 [AMOUNT]
                  FROM      #DETAIL t
                  WHERE     t.tha_class_code = 'PM'
                            AND TRANS_CODE = 'LSPM'
                  UNION
                  SELECT    '4- SALES' [CAPTION],
                            0 [NUMBER],
                            0 [AMOUNT]
                  FROM      #DETAIL t
                  WHERE     t.tha_class_code = 'PM'
                            AND TRANS_CODE = 'LSPM'
                  UNION
                  SELECT    '5- PM-SETUPS' [CAPTION],
                            ISNULL(COUNT(tha_NO), 0) [NUMBER],
                            ISNULL(SUM(PRINCIPAL), 0) [AMOUNT]
                  FROM      #DETAIL t
                  WHERE     t.tha_class_code = 'PM'
                            AND TRANS_CODE = 'LSPM'
                  UNION
                  SELECT    '6- MN-SETUPS' [CAPTION],
                            ISNULL(COUNT(tha_NO), 0) [NUMBER],
                            ISNULL(SUM(PRINCIPAL), 0) [AMOUNT]
                  FROM      #DETAIL t
                  WHERE     t.tha_class_code = 'MN'
                            AND TRANS_CODE = 'LSSH'
                ) Z
        INSERT  INTO #REPORT1
                SELECT  X.*
                FROM    ( SELECT    '8- ' + CONVERT(VARCHAR(10), @dt_enddate, 101) [CAPTION],
                                    count(*) [NUMBER],
                                    SUM(PRINCIPAL) [AMOUNT]
                          FROM      #DETAILBB
                        ) X

  --update the counts and amounts for the month that just closed
        UPDATE  dbo.a_rpt_tha
        SET     AssignMNAmt = ( SELECT  AMOUNT
                                FROM    #REPORT1
                                WHERE   LEFT(caption, 1) = '6'
                              ),
                AssignMNQty = ( SELECT  NUMBER
                                FROM    #REPORT1
                                WHERE   LEFT(caption, 1) = '6'
                              ),
                AssignPMAmt = ( SELECT  amount
                                FROM    #REPORT1
                                WHERE   LEFT(caption, 1) = '5'
                              ),
                AssignPMQty = ( SELECT  number
                                FROM    #REPORT1
                                WHERE   LEFT(caption, 1) = '5'
                              ),
                ConversionAmt = ( SELECT    amount
                                  FROM      #REPORT1
                                  WHERE     LEFT(caption, 1) = '3'
                                ),
                ConversionQty = ( SELECT    number
                                  FROM      #REPORT1
                                  WHERE     LEFT(caption, 1) = '3'
                                ),
                PayoffAmt = ( SELECT    amount
                              FROM      #REPORT1
                              WHERE     LEFT(caption, 1) = '2'
                            ),
                PayoffQty = ( SELECT    number
                              FROM      #REPORT1
                              WHERE     LEFT(caption, 1) = '2'
                            )
        WHERE   PeriodYear = @lastperiodyear
                AND PeriodNo = @lastPeriod

        UPDATE  dbo.a_rpt_tha
        SET     AdjustmentAmt = ( SELECT    amount
                                  FROM      #REPORT1
                                  WHERE     LEFT(caption, 1) = '8'
                                ) - BegBalance - PayoffAmt - ConversionAmt
                - AssignMNAmt - AssignPMAmt,
                AdjustmentQty = ( SELECT    number
                                  FROM      #REPORT1
                                  WHERE     LEFT(caption, 1) = '8'
                                ) - BegCount - PayoffQty - ConversionQty
                - AssignMNQty - AssignPMQty
        WHERE   PeriodYear = @lastperiodyear
                AND PeriodNo = @lastPeriod

  --insert the row for the current month
        INSERT  dbo.a_rpt_tha
                (
                  PeriodYear,
                  PeriodNo,
                  FY,
                  FYMonth,
                  BegCount,
                  BegBalance
         
                )
                SELECT  @thisYear, -- PeriodYear - int
                        @ThisPeriod, -- PeriodNo - int
                        @ThisFY, -- FY - int
                        @ThisFYMonth, -- FYMonth - int
                        NUMBER, -- BegCount - int
                        Amount  -- BegBalance - money
                FROM    #REPORT1
                WHERE   LEFT(caption, 1) = '8'

        SELECT  'tha data populated as of:' + CONVERT(VARCHAR(10), GETDATE(), 101) + ' '  + CONVERT(VARCHAR(10), GETDATE(), 108) AS 'Finis'
        ORDER BY 1
        GOTO BYE

        FINISH:

        Print 'Date VALUE/RANGE too low !!!'

        BYE:

                                                               
        DROP TABLE #TEMP1                                     
        DROP TABLE #DETAIL                                                     
        DROP TABLE #TEMP2BB
        DROP TABLE #DETAILBB                                                                                                                                                                               
        DROP TABLE #TEMP2BB0             
        DROP TABLE #DETAILBB0                                                                                                                                                                             
        DROP TABLE #TEMP_OLD
        drop table #delta
        DROP TABLE #REPORT1
    END
   

Advertisements

About combatdba

I'm a production DBA at a terabyte-class SQL Server Shop
This entry was posted in Procedures. Bookmark the permalink.

5 Responses to A Typical Stored Procedure…

  1. Brent Ozar says:

    HAHAHA, ouch, man, what a way to start the week. That’s intimidating to see in a blog, let alone in SSMS. My condolences, sir.

  2. John Sansom says:

    Whoa, I really do feel for you if that is one of the urm cleaner ones 🙂

    The first thing that comes to my mind when I see code like this is why are they trying to run data analytics on an OLTP database? The data is literally screaming out for a Data Warehouse solution in my opinion. All the slicing and dicing, aggregations, filtering data by year, is begging to be reborn as a SSAS cube.

    I’ve seen this flavour of work before, particularly when looking at financial data, so I have an idea of what your up against. What did I do about it? Well I went ahead and built the Data Warehouse of course 🙂

  3. Pingback: Exectweets » BrentO at 01/10/11 01:29:56

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s