none
SQL Query RRS feed

  • Question

  • I have a table containing customer id, posting date, and running balance.  For example, a customer made a payment on 04/2017, 07/2017 and 10/2017 for $20 each time.  So, the running balance on 04/2017 was $20, on 07/2017 was $40, on 10/2017 was $60.  I would like to find that customer's running balance on 08/2017 which is $40.  Is there a nice solution to deal with this problem?

    I have put together a query for this which works but it is ugly.  I was looking for max date below or equal to  08/2017 and match on the full table with the key and date.

     Thanks in advance
    Thursday, March 22, 2018 4:12 AM

Answers

  • I just realized something.  I could store next postingYM on the rollup.  This will elliminate the need for sort.
    • Marked as answer by rgelfand Sunday, March 25, 2018 3:01 AM
    Friday, March 23, 2018 1:48 PM

All replies

  • Something like below?

    create table test(id int, dt varchar(10), bal int)
    
    insert into test values(1, '04/2017', 20)
    insert into test values(1, '07/2017', 40)
    insert into test values(1, '10/2017', 60)
    
    select top 1 bal from test where cast(substring(dt, 1, 2) as int) <= 8 and id = 1 order by dt desc


    • Edited by SQLNeophyte Thursday, March 22, 2018 4:33 AM
    Thursday, March 22, 2018 4:29 AM
  • you can do like this

    SELECT CustomerName,CummBal
    FROM
    (
    SELECT *,
    SUM(Balance) OVER(PARTITION BY CustomerID ORDER BY Date) AS CummBal,
    ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY Date DESC) AS Seq
    FROM Table
    WHERE Date <= @Date
    AND CustomerName = @CustomerName
    )t
    WHERE Seq = 1
    

    I've assumed column names myself as you've not provided any info on your tables. So make sure you replace table and column names accordingly

    @CustomerName and @Date should be declared as parameters through which you pass the required customer details and date value

    I also assume you're using a SQLServer with version on or above 2012


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 22, 2018 5:11 AM
  • I have a table containing customer id, posting date, and running balance.  For example, a customer made a payment on 04/2017, 07/2017 and 10/2017 for $20 each time.  So, the running balance on 04/2017 was $20, on 07/2017 was $40, on 10/2017 was $60.  I would like to find that customer's running balance on 08/2017 which is $40.  Is there a nice solution to deal with this problem?

    I have put together a query for this which works but it is ugly.  I was looking for max date below or equal to  08/2017 and match on the full table with the key and date.

     Thanks in advance

    Hi rgelfand,

    Please post your sample data and desired output, so that we could provide more particular solution.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 22, 2018 6:28 AM
    Moderator
  • rollup table

    ex.

    insert into rollup(Id, ServiceStartYM, PostingYM, Balance) values(1, 201702, 201710, 33.12)

     

    The query, below, is what I want to accomplish. I tried the above partition example and it was much slower.

      SELECT a.Id
               a.[Period],
               ar.balance
        FROM
    (
        SELECT Id,
               ServiceStartYM [Period],
               MAX(postingYM) PostingYM,
               @AsOfYM AsOfYM
        FROM Rollup
        WHERE postingYM <= @AsOfYM
        GROUP BY Id,
                 ServiceStartYM
    ) a
    JOIN rollup ar ON ar.id = a.id
                              AND a.period = ar.servicestartym
                              AND a.PostingYM = ar.postingYM

    Friday, March 23, 2018 3:04 AM
  • Not sure if you are also only looking for a specific ID, but this would probably work to get all IDs and Balances at your @AsOfYM

    CREATE TABLE [rollup]
    (Id INT, ServiceStartYM INT, PostingYM INT, Balance money)
    GO
    
    INSERT into [rollup](Id, ServiceStartYM, PostingYM, Balance) 
    VALUES
    (1, 201702, 201704, 14.55),
    (1, 201702, 201707, 28.60),
    (1, 201702, 201710, 33.12),
    (2, 201701, 201704, 3.25),
    (2, 201701, 201707, 12.30),
    (2, 201701, 201710, 13.12)
    
    DECLARE @AsOfYM INT = 201708
    
    SELECT ID, @AsOfYM, MAX(Balance)
    FROM [dbo].[rollup]
    WHERE PostingYM <= @AsOfYM 
    GROUP BY ID
    
    DROP TABLE [dbo].[rollup]


    Martin Cairney SQL Server MVP

    Friday, March 23, 2018 5:08 AM
  • I am not looking for specific id rather the whole set.  Also, you have put down max balance, but I am looking for balance with max postingYM.
    Friday, March 23, 2018 11:28 AM
  • I am not looking for specific id rather the whole set.  Also, you have put down max balance, but I am looking for balance with max postingYM.

    So, the running balance on 04/2017 was $20, on 07/2017 was $40, on 10/2017 was $60.  I would like to find that customer's running balance on 08/2017 which is $40

    This explanation is different from what you're doing with the above query

    I dont see anywhere were you're doing a cumulative calculation

    Or is your table already storing them as cumulative figures?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, March 23, 2018 11:40 AM
  • The table is already storing cumulative figures.
    Friday, March 23, 2018 1:09 PM
  • Also, if you see a way how I could help the query by storing additional column(s) on rollup table which would ultimately help the query please let me know.
    Friday, March 23, 2018 1:42 PM
  • I just realized something.  I could store next postingYM on the rollup.  This will elliminate the need for sort.
    • Marked as answer by rgelfand Sunday, March 25, 2018 3:01 AM
    Friday, March 23, 2018 1:48 PM