Efficient way of writing this stored procedure (Takes too much time to run)

  • Dear all,
    We have a query which results all the records which are unpaid as of transaction effective date provided by user.

    SELECT dbo.LRLedger.LedgerName, dbo.Account.AccountName, dbo.LRTransaction.AccountNumber, sum(dbo.LRTransaction.Amount) AS Amount FROM dbo.LRLedger,dbo.LRTransaction LEFT OUTER JOIN dbo.Account ON dbo.LRTransaction.AccountNumber = dbo.Account.AccountNumber WHERE dbo.LRLedger.LedgerId = dbo.LRTransaction.LedgerId AND dbo.LRTransaction.TransactionEffDate <= '01/31/2018' AND dbo.LRTransaction.IsClosed = 0 GROUP BY dbo.LRLedger.LedgerName, dbo.Account.AccountName, dbo.LRTransaction.AccountNumber 

    This query takes about less than one second to run . We will use this result set to populate an excel sheet and present to user in the online application.

    Now I am trying to modify this query in such a way that it breaks down the unpaid amount based on number of aging days like below.

    1. Records which are between 0 and 30 days which are unpaid since the transaction effective date.
    2.  Records which are between 30 and 60 days which are unpaid since the transaction effective date
    3. Between 60 and 90 , 90 and 120 , 120 and 150 and finally 150 +.

    Basically its just a breakdown of the existing report.

    To achieve this I have written a stored procedure which will give me the breakdown as expected.
    But when i run this stored procedure, it takes more than 30 seconds to complete.
    Not sure what is the mistake because of which this query is running  for this much time.
    Please guide me on improving the performance of this stored procedure.

    My sp is below :

    CREATE PROCEDURE [dbo].[Pmcsdcorrection] @TransactionEffDate DATETIME2(3)
    AS
    BEGIN
      SET nocount ON
      SET ansi_warnings OFF

      DECLARE @thirty     DATETIME2(3),
         @Sixty     DATETIME2(3),
         @Ninety     DATETIME2(3),
         @onetwenty    DATETIME2(3),
         @onefifty    DATETIME2(3),
         @ActNumber    CHAR(10),
         @LedgerNme    CHAR(30),
         @NinetyBucket   MONEY,
         @ThirtyBucket   MONEY,
         @SixtyBucket   MONEY,
         @OneTwentyBucke  MONEY,
         @OneFiftyBucket  MONEY,
         @OneFiftyPlusBucket MONEY,
         @LedgerId    NUMERIC (18, 0)

      CREATE TABLE #tmpledger
       (
        [ledgername]   [CHAR](20) NULL,
        [ledgerid]    [NUMERIC](18, 0) NULL,
        [accountname]   [CHAR](30) NULL,
        [accountnumber]  [CHAR](10) NOT NULL,
        [thirtybucket]   [MONEY] NULL,
        [sixtybucket]   [MONEY] NULL,
        [ninetybucket]   [MONEY] NULL,
        [onetwentybucket]  [MONEY] NULL,
        [onefiftybucket]  [MONEY] NULL,
        [onefiftyplusbucket] [MONEY] NULL
       )

      SELECT dbo.lrledger.ledgername,
         dbo.lrtransaction.ledgerid,
         dbo.account.accountname,
         dbo.lrtransaction.accountnumber
      FROM dbo.lrledger,
         dbo.lrtransaction
         LEFT OUTER JOIN dbo.account
             ON dbo.lrtransaction.accountnumber =
              dbo.account.accountnumber
      WHERE dbo.lrledger.ledgerid = dbo.lrtransaction.ledgerid
         AND dbo.lrtransaction.transactioneffdate <= '04/30/2017'
         AND dbo.lrtransaction.isclosed = 0
      GROUP BY dbo.lrledger.ledgername,
          dbo.account.accountname,
          dbo.lrtransaction.accountnumber,
          dbo.lrtransaction.ledgerid;

      INSERT INTO #tmpledger
          (ledgername,
           ledgerid,
           accountname,
           accountnumber)
      SELECT dbo.lrledger.ledgername,
         dbo.lrtransaction.ledgerid,
         dbo.account.accountname,
         dbo.lrtransaction.accountnumber
      FROM dbo.lrledger,
         dbo.lrtransaction
         LEFT OUTER JOIN dbo.account
             ON dbo.lrtransaction.accountnumber =
              dbo.account.accountnumber
      WHERE dbo.lrledger.ledgerid = dbo.lrtransaction.ledgerid
         AND dbo.lrtransaction.transactioneffdate <= '04/30/2017'
         AND dbo.lrtransaction.isclosed = 0
      GROUP BY dbo.lrledger.ledgername,
          dbo.account.accountname,
          dbo.lrtransaction.ledgerid,
          dbo.lrtransaction.accountnumber

      DECLARE get_aging_buckets CURSOR FOR
       SELECT ledgername,
         ledgerid,
         accountnumber
       FROM #tmpledger

      OPEN get_aging_buckets

      FETCH next FROM get_aging_buckets INTO @LedgerNme, @LedgerId, @ActNumber

      WHILE @@FETCH_STATUS = 0
       BEGIN
        SELECT @thirty = Dateadd(day, -30, @TransactionEffDate)

        SELECT @Sixty = Dateadd(day, -30, @TransactionEffDate)

        SELECT @Ninety = Dateadd(day, -60, @TransactionEffDate)

        SELECT @onetwenty = Dateadd(day, -90, @TransactionEffDate)

        SELECT @onefifty = Dateadd(day, -150, @TransactionEffDate)

        SELECT @ThirtyBucket = (SELECT Sum(dbo.lrtransaction.amount) AS
                   Amount
                FROM dbo.lrledger,
                   dbo.lrtransaction
                WHERE dbo.lrtransaction.accountnumber =
                   @ActNumber
                   AND dbo.lrledger.ledgerid = @LedgerId
                   AND transactioneffdate BETWEEN
                    @thirty AND @TransactionEffDate
                   AND dbo.lrtransaction.isclosed = 0);

        SELECT @SixtyBucket = (SELECT Sum(dbo.lrtransaction.amount) AS
                  Amount
                FROM dbo.lrledger,
                  dbo.lrtransaction
                WHERE dbo.lrtransaction.accountnumber =
                  @ActNumber
                  AND dbo.lrledger.ledgerid = @LedgerId
                  AND transactioneffdate BETWEEN
                    @Sixty AND @thirty
                  AND dbo.lrtransaction.isclosed = 0);

        SELECT @NinetyBucket = (SELECT Sum(dbo.lrtransaction.amount) AS
                   Amount
                FROM dbo.lrledger,
                   dbo.lrtransaction
                WHERE dbo.lrtransaction.accountnumber =
                   @ActNumber
                   AND dbo.lrledger.ledgerid = @LedgerId
                   AND transactioneffdate BETWEEN
                    @Ninety AND @Sixty
                   AND dbo.lrtransaction.isclosed = 0);

        SELECT @OneTwentyBucke = (SELECT Sum(dbo.lrtransaction.amount) AS
                   Amount
                 FROM dbo.lrledger,
                   dbo.lrtransaction
                 WHERE dbo.lrtransaction.accountnumber =
                   @ActNumber
                   AND dbo.lrledger.ledgerid =
                     @LedgerId
                   AND transactioneffdate BETWEEN
                     @onetwenty AND @Ninety
                   AND dbo.lrtransaction.isclosed = 0)
        ;

        SELECT @OneFiftyBucket = (SELECT Sum(dbo.lrtransaction.amount) AS
                   Amount
                 FROM dbo.lrledger,
                   dbo.lrtransaction
                 WHERE dbo.lrtransaction.accountnumber =
                   @ActNumber
                   AND dbo.lrledger.ledgerid =
                     @LedgerId
                   AND transactioneffdate BETWEEN
                     @onefifty AND @onetwenty
                   AND dbo.lrtransaction.isclosed = 0)
        ;

        SELECT @OneFiftyPlusBucket = (SELECT Sum(dbo.lrtransaction.amount)
                     AS
                     Amount
                  FROM dbo.lrledger,
                     dbo.lrtransaction
                  WHERE dbo.lrtransaction.accountnumber
                     =
                     @ActNumber
                     AND dbo.lrledger.ledgerid =
                      @LedgerId
                     AND transactioneffdate >
                      @onefifty
                     AND dbo.lrtransaction.isclosed
                      =
                      0);

        UPDATE #tmpledger
        SET  thirtybucket = @ThirtyBucket,
           sixtybucket = @SixtyBucket,
           ninetybucket = @NinetyBucket,
           onetwentybucket = @OneTwentyBucke,
           onefiftybucket = @OneFiftyBucket,
           onefiftyplusbucket = @OneFiftyPlusBucket
        WHERE accountnumber = @ActNumber
           AND ledgerid = @LedgerId

        FETCH next FROM get_aging_buckets INTO @LedgerNme, @LedgerId,
        @ActNumber
       END

      CLOSE get_aging_buckets

      DEALLOCATE get_aging_buckets

      SELECT *
      FROM #tmpledger

      DROP TABLE #tmpledger

      SET nocount OFF
      SET ansi_warnings ON
    END

    go

  • Table definitions please & index definitions.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Your problem is the cursor.
    It is difficult to write a set based approach without DDL and comsumable test data.
    Try something like the following:

    --declare @TransactionEffDate DATETIME2(3) = SYSDATETIME();
    SET @TransactionEffDate = DATEADD(day, DATEDIFF(day, 0, @TransactionEffDate), 1);

    WITH Buckets(Bucket, MinDate, MaxDate)
    AS
    (
        SELECT Bucket
            ,COALESCE(DATEADD(DAY, V.MinVal, @TransactionEffDate), '19000101')
            ,COALESCE(DATEADD(DAY, V.MaxVal, @TransactionEffDate), @TransactionEffDate)
        FROM
        (
            VALUES ('30', -30, NULL),('60', -60, -30),('90', -90, -60),('120', -120, -90)
            ,('150', -150, -120), ('Rest', NULL, -150)
        ) V (Bucket, MinVal, MaxVal)
    )
    --select * from Buckets
    SELECT L.ledgername, T.ledgerid, A.accountname, T.accountnumber
        ,ThirtyBucket = SUM(CASE WHEN B.Bucket = '30' AND T.transactioneffdate >= B.MinDate AND T.transactioneffdate < B.MaxDate THEN L.amount ELSE 0 END)
        ,SixtyBucket = SUM(CASE WHEN B.Bucket = '60' AND T.transactioneffdate >= B.MinDate AND T.transactioneffdate < B.MaxDate THEN L.amount ELSE 0 END)
        ,NinetyBucket = SUM(CASE WHEN B.Bucket = '90' AND T.transactioneffdate >= B.MinDate AND T.transactioneffdate < B.MaxDate THEN L.amount ELSE 0 END)
        ,OneTwentyBucket = SUM(CASE WHEN B.Bucket = '120' AND T.transactioneffdate >= B.MinDate AND T.transactioneffdate < B.MaxDate THEN L.amount ELSE 0 END)
        ,OneFiftyBucket = SUM(CASE WHEN B.Bucket = '150' AND T.transactioneffdate >= B.MinDate AND T.transactioneffdate < B.MaxDate THEN L.amount ELSE 0 END)
        ,OneFiftyPlusBucket = SUM(CASE WHEN B.Bucket = 'Rest' AND T.transactioneffdate >= B.MinDate AND T.transactioneffdate < B.MaxDate THEN L.amount ELSE 0 END)
    FROM dbo.lrledger L
        CROSS JOIN Buckets B
      JOIN dbo.lrtransaction T
            ON L.ledgerid = T.ledgerid
                AND T.transactioneffdate < @TransactionEffDate
                AND T.isclosed = 0
      LEFT OUTER JOIN dbo.account A
       ON L.accountnumber = A.accountnumber
    GROUP BY L.ledgername, T.ledgerid, A.accountname, T.accountnumber;

  • I took a slightly different approach, but for the same reasons.   Cursors are well known for being potential performance problems, and what it appears you did was to approach the problem as a procedural problem instead of a set-based one.   SQL Server and relational databases do best by working with sets.   The key here was simply to recognize how to group your data in such a way that the SUMs you wanted were done according to various 30 days ranges from the date passed in to your sproc.   Look at each query element and see how it contributes to the process.   Taking this apart may do more to help your understanding.


    CREATE PROCEDURE dbo.Pmcsdcorrection (
        @TransactionEffDate DATETIME2(3)
    )
    AS
    BEGIN
    SET NOCOUNT ON;
    SET ANSI_WARNINGS OFF;

    WITH AGE_RANGES AS (

        SELECT '0 - 30 days' AS RANGE_NAME, 0 AS RANGE_LOW, 31 AS RANGE_HIGH, 1 AS SORT_VALUE
        UNION ALL
        SELECT '31 - 60 days', 31, 61, 2
        UNION ALL
        SELECT '61 - 90 days', 61, 91, 3
        UNION ALL
        SELECT '91 - 120 days', 91, 121, 4
        UNION ALL
        SELECT '121 - 150 days', 121, 151, 5
        UNION ALL
        SELECT '150+ days', 151, 2147483647, 6
    ),
        AGING AS (

            SELECT LRL.ledgername, LRT.ledgerid, A.accountname, LRT.accountnumber, LRT.amount,
                DATEDIFF(day, LRT.transactioneffdate, @TransactionEffDate) AS ACCOUNT_AGE
            FROM dbo.lrledger AS LRL,
              dbo.lrtransaction AS LRT
                  LEFT OUTER JOIN dbo.account AS A
                      ON LRT.accountnumber = A.accountnumber
            WHERE LRL.ledgerid = LRT.ledgerid
                AND LRT.transactioneffdate <= '04/30/2017'
                AND LRT.isclosed = 0
    ),
        RANGE_ASSIGNMENT AS (

           SELECT R.RANGE_NAME, A.ledgername, A.ledgerid, A.accountname, A.accountnumber, SUM(ISNULL(A.amount,0)) AS amount, R.SORT_VALUE
            FROM AGE_RANGES AS R
                LEFT OUTER JOIN AGING AS A
                    ON A.ACCOUNT_AGE >= R.RANGE_LOW
                    AND A.ACCOUNT_AGE < R.RANGE_HIGH
           GROUP BY R.RANGE_NAME, A.ledgername, A.ledgerid, A.accountname, A.accountnumber
    )
    SELECT [0 - 30 days], [31 - 60 days], [61 - 90 days], [91 - 120 days], [121 - 150 days], [150+ days]
    FROM (
        SELECT SORT_VALUE, [0 - 30 days], [31 - 60 days], [61 - 90 days], [91 - 120 days], [121 - 150 days], [150+ days]
        FROM RANGE_ASSIGNMENT
            PIVOT (SUM(amount) FOR RANGE_NAME IN ([0 - 30 days], [31 - 60 days], [61 - 90 days], [91 - 120 days], [121 - 150 days], [150+ days]) AS PVT
        ) AS X;
    END
    GO

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Just a basic approach using cross tabs to avoid massaging the data for pivot.
    I also changed the old-syntax join to keep the code consistent and used table alias to prevent 3-part column names which are deprecated.

    CREATE PROCEDURE [dbo].[Pmcsdcorrection] @TransactionEffDate DATETIME2(3)
    AS
    SET nocount ON ;
    SET ansi_warnings OFF ;

    DECLARE @thirty  DATETIME2(3),
      @Sixty  DATETIME2(3),
      @Ninety  DATETIME2(3),
      @onetwenty  DATETIME2(3),
      @onefifty  DATETIME2(3);

    SELECT l.ledgername,
      t.ledgerid,
      a.accountname,
      t.accountnumber
    FROM dbo.lrledger l
    JOIN dbo.lrtransaction t ON l.ledgerid = t.ledgerid
    LEFT OUTER JOIN dbo.account a ON t.accountnumber = a.accountnumber
    WHERE t.transactioneffdate <= '04/30/2017'
      AND t.isclosed = 0
    GROUP BY l.ledgername,
      a.accountname,
      t.accountnumber,
      t.ledgerid;
      
    SELECT @thirty = Dateadd(day, -30, @TransactionEffDate)
       ,@Sixty = Dateadd(day, -30, @TransactionEffDate)
       ,@Ninety = Dateadd(day, -60, @TransactionEffDate)
       ,@onetwenty = Dateadd(day, -90, @TransactionEffDate)
       ,@onefifty = Dateadd(day, -150, @TransactionEffDate);

    SELECT l.ledgername,
      t.ledgerid,
      a.accountname,
      t.accountnumber,
      thirtybucket   = SUM( CASE WHEN transactioneffdate BETWEEN @thirty AND @TransactionEffDate THEN t.amount ELSE 0 END),
      sixtybucket   = SUM( CASE WHEN transactioneffdate BETWEEN @Sixty AND @thirty THEN t.amount ELSE 0 END),
      ninetybucket   = SUM( CASE WHEN transactioneffdate BETWEEN @Ninety AND @Sixty THEN t.amount ELSE 0 END),
      onetwentybucket  = SUM( CASE WHEN transactioneffdate BETWEEN @onetwenty AND @Ninety THEN t.amount ELSE 0 END),
      onefiftybucket  = SUM( CASE WHEN transactioneffdate BETWEEN @onefifty AND @onetwenty THEN t.amount ELSE 0 END),
      onefiftyplusbucket = SUM( CASE WHEN transactioneffdate < @onefifty THEN t.amount ELSE 0 END)
    FROM dbo.lrledger l
    JOIN dbo.lrtransaction t ON l.ledgerid = t.ledgerid
    LEFT JOIN dbo.account a ON t.accountnumber = a.accountnumber
    WHERE t.transactioneffdate <= '04/30/2017'
      AND t.isclosed = 0
    GROUP BY l.ledgername,
      a.accountname,
      t.accountnumber,
      t.ledgerid;

    SET nocount OFF ;
    SET ansi_warnings ON ;

    go

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Hi 

    GilaMonster - Thursday, February 16, 2017 12:37 AM

    Table definitions please & index definitions.

    Hi Gila,

    Thanks very much for checking this .. I have attached  DDL for reference. Please check it.

  • muralikrishna2489 - Thursday, February 16, 2017 12:19 PM

    Hi 

    GilaMonster - Thursday, February 16, 2017 12:37 AM

    Table definitions please & index definitions.

    Hi Gila,

    Thanks for checking this .. I have attached  DDL for reference. Please check it.

    You're missing one table (accounts) and sample data that can be used for testing purposes.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Hi Luis , Sgmunson, Ken Mckelvey

    Thanks very much for explaining the current problem and providing solution for it.
    I am going through the new stored procedures and trying to understand it. When i run these SPs with test data, it completes within two seconds !!!!!.
    As i am not familiar with the concepts, it is taking some time for me to understand it. I will post here shortly if i have any doubts . Thanks again 🙂

  • muralikrishna2489 - Thursday, February 16, 2017 12:29 PM

    Hi Luis , Sgmunson, Ken Mckelvey

    Thanks very much for explaining the current problem and providing solution for it.
    I am going through the new stored procedures and trying to understand it. When i run these SPs with test data, it completes within two seconds !!!!!.
    As i am not familiar with the concepts, it is taking some time for me to understand it. I will post here shortly if i have any doubts . Thanks again 🙂

    Take a look at this article which explains the method that I used.
    http://www.sqlservercentral.com/articles/T-SQL/63681/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Hi Luis,

    I am having one small doubt in the SP, Sorry i didn't realize this earlier.  I would also like to show one last column where it shows the sum of all six aging buckets.
    I have tried to modify your SP like below but it is throwing error like i cannot use the variable which is not declared.


    SELECT l.LedgerName, 
    t.LedgerId, 
    a.AccountName, 
    t.AccountNumber,
    thirtybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @thirty AND @TransactionEffDate THEN t.Amount ELSE 0 END),
    sixtybucket  = SUM( CASE WHEN TransactionEffDate BETWEEN @Sixty AND @thirty THEN t.Amount ELSE 0 END),
    ninetybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @Ninety AND @Sixty THEN t.Amount ELSE 0 END),
    onetwentybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @onetwenty AND @Ninety THEN t.Amount ELSE 0 END),
    onefiftybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @onefifty AND @onetwenty THEN t.Amount ELSE 0 END),
     onefiftyplusbucket= SUM( CASE WHEN TransactionEffDate < @onefifty THEN t.Amount ELSE 0 END),
    SumofAmount = SUM (thirtybucket+sixtybucket+ninetybucket+onetwentybucket+onefiftybucket+onefiftyplusbucket)
    [/code]

    Could you please tell me on how to use the last column as sum of all six aging buckets.

  • Hi Luis,

    I have got it 🙂 ...
    I have used below query to get the sum of aging bucket.
    Please tell me if it is wrong Luis... Thanks in Advance...

    SELECT l.LedgerName,
    t.LedgerId,
    a.AccountName,
    t.AccountNumber,
    thirtybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @thirty AND @TransactionEffDate THEN t.Amount ELSE 0 END),
    sixtybucket  = SUM( CASE WHEN TransactionEffDate BETWEEN @Sixty AND @thirty THEN t.Amount ELSE 0 END),
    ninetybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @Ninety AND @Sixty THEN t.Amount ELSE 0 END),
    onetwentybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @onetwenty AND @Ninety THEN t.Amount ELSE 0 END),
    onefiftybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @onefifty AND @onetwenty THEN t.Amount ELSE 0 END),
     onefiftyplusbucket= SUM( CASE WHEN TransactionEffDate < @onefifty THEN t.Amount ELSE 0 END),
    SumofAmount = SUM (t.Amount)
    FROM dbo.LRLedger l
    JOIN dbo.LRTransaction t ON l.LedgerId = t.LedgerId
    LEFT JOIN dbo.Account a ON t.AccountNumber = a.AccountNumber
    WHERE t.TransactionEffDate <= '04/30/2017'
    AND t.IsClosed = 0
    GROUP BY l.LedgerName,
    a.AccountName,
    t.AccountNumber,
    t.LedgerId;

  • muralikrishna2489 - Thursday, February 16, 2017 1:25 PM

    Hi Luis,

    I have got it 🙂 ...
    I have used below query to get the sum of aging bucket.
    Please tell me if it is wrong Luis... Thanks in Advance...

    SELECT l.LedgerName,
    t.LedgerId,
    a.AccountName,
    t.AccountNumber,
    thirtybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @thirty AND @TransactionEffDate THEN t.Amount ELSE 0 END),
    sixtybucket  = SUM( CASE WHEN TransactionEffDate BETWEEN @Sixty AND @thirty THEN t.Amount ELSE 0 END),
    ninetybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @Ninety AND @Sixty THEN t.Amount ELSE 0 END),
    onetwentybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @onetwenty AND @Ninety THEN t.Amount ELSE 0 END),
    onefiftybucket = SUM( CASE WHEN TransactionEffDate BETWEEN @onefifty AND @onetwenty THEN t.Amount ELSE 0 END),
     onefiftyplusbucket= SUM( CASE WHEN TransactionEffDate < @onefifty THEN t.Amount ELSE 0 END),
    SumofAmount = SUM (t.Amount)
    FROM dbo.LRLedger l
    JOIN dbo.LRTransaction t ON l.LedgerId = t.LedgerId
    LEFT JOIN dbo.Account a ON t.AccountNumber = a.AccountNumber
    WHERE t.TransactionEffDate <= '04/30/2017'
    AND t.IsClosed = 0
    GROUP BY l.LedgerName,
    a.AccountName,
    t.AccountNumber,
    t.LedgerId;

    That's exactly the way to code it. As you can see, it's also the simplest way.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares - Thursday, February 16, 2017 1:28 PM

    muralikrishna2489 - Thursday, February 16, 2017 1:25 PM

    That's exactly the way to code it. As you can see, it's also the simplest way.

    Thanks very much Luis !!!

  • Hello muralikrishna2489,

    Did you notice that in your orginal query as well as in some of the answers, three intervals are set incorrect? @Sixty is set to 30 days, @Ninety is set to 60 days and @onetwenty is set to 90 days. This might be on purpose, but I guess they are just typos, because @thirty and @onefifty are set exactly according to their names. Hope this helps, good luck with this procedure.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply