Inflow / outflow report per day

  • Hello,

    I have two simple tables:

    • Invoices

      • InvoiceID
      • Date
      • Value

    • and PaymentsReceived

      • PaymentID
      • Date
      • Value

    I want to generate a report which will display day by day the cashflow (total invoices - total payments received).

  • So is there no joining factor between the two tables?  The only real thing is the Date column, so the SUM of Invoices.Value - SUM of PaymentsReceived.Value for each day?

    SELECT
    ISNULL(I.Date,P.Date) AS Date --Added for safety incase the join type is changed
    ,ISNULL(Inv.DayTotal,0) - ISNULL(Pay.DayTotal,0) AS CashFlow --ISNULL 0 added for math, if no invoices or payments for that day then X - 0 or 0 - Y math
    FROM
    (
    SELECT
    CONVERT(DATE,Date) AS Date --assuming this is a datetime field so need to strip the time to sum for the whole date
    ,SUM(Value) AS DayTotal
    FROM Invoices
    GROUP BY CONVERT(DATE,Date)
    ) as Inv
    INNER JOIN --May want to change this to LEFT / RIGHT / FULL join depending on if one side has more data than other
    (
    SELECT
    CONVERT(DATE,Date) AS Date --assuming this is a datetime field so need to strip the time to sum for the whole date
    ,SUM(Value) AS DayTotal
    FROM PaymentsReceived
    GROUP BY CONVERT(DATE,Date)
    ) as Pay
    ON Inv.Date = Pay.Date
  • You should at least post what the datatype are for the columns.  You'll also find that most people want to see what you've tried in the form of code.  We're not "free consultants". 😉

    With the very sparse information you've provided, I'll say you need a CTE to aggregate the data for each by day and then do a join between the CTEs using the Date columns to do the join and then do your subtraction in the outer select.e

    If you want days with no activity to appear with "0"s, then you'll also need to outer join to a Calendar table or build a 3rd CTE that encompasses all the dates in the data from Min to Max.

    Seriously, though... you need to show that you've tried.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • milo1981 wrote:

    I want to generate a report which will display day by day the cashflow (total invoices - total payments received).

    Wouldn't "total invoices - total payments received" be the receivable balance / shortfall and not the cashflow?  Wouldn't "cashflow" be the sum of payments made?!

    It sounds to me like you want a running total.  Do you want a running total of what I'm calling shortfall then?

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Thank you, all, for your answers. Sorry, I posted in a hurry without much details.

    So far, I have the following query:

    WITH D AS (

    SELECT CONVERT(VARCHAR, '2020.11.01', 102) AS d_y_m,

    CAST('2020-11-01' AS DATETIME) AS dt

    UNION ALL

    SELECT CONVERT(VARCHAR, DATEADD(dd, 1, z.dt), 102) AS d_y_m,

    DATEADD(dd, 1, z.dt)

    FROM D z

    WHERE DATEADD(dd, 1, z.dt) <= '2020-11-30')

    SELECT x.d_y_m, ISNULL(SUM(y.Total), 0) AS [Invoiced], ISNULL(SUM(FEI.Total), 0) AS [Paid] FROM D x

    LEFT JOIN Invoices y ON CONVERT(VARCHAR, y.InvoiceDate, 102) = x.d_y_m

    LEFT JOIN Payments AS FEI ON CONVERT(VARCHAR, FEI.PaymentDate, 102) = x.d_y_m

    GROUP BY x.d_y_m

    ORDER BY x.d_y_m OPTION (MAXRECURSION 0)

    How do I add another column (Cashflow) to the query which sums up the (Invoiced-Paid) result from the previous day to the one for today

    Example:

    d_y_m | Invoiced | Paid | Cashflow

    2020.11.01 | 24 | 5 | 19

    2020.11.02 | 45 | 2 | 62

    2020.11.03 | 10 | 20 | 52

    2020.11.04 | 5 | 0 | 57

    2020.11.05 | 0 | 10 | 47

  • like this? Really I should learn how to convert normal dates to silly US date format (or vice versa), but anyway... this should work... (create running totals for Invoiced and Paid, then subtract.

    Maybe I should explain better. This is the running total pattern:

    <aggregate function>(<numeric column>) OVER (ORDER BY <date column>
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW)

    So you just plug in your field names where applicable. Do this once for "inflows", and another for "outflows". Then subtract... Running_Total_Inflows - Running_Total_Outflows.

    use tempdb
    go

    CREATE TABLE TempData (TextDate CHAR(10),Invoiced INT, Paid INT, Cashflow INT);
    GO
    INSERT INTO TempData VALUES
    ('2020-11-01' , 24 , 5 , 19)
    ,('2020-11-02' , 45 , 2 , 62)
    ,('2020-11-03' , 10 , 20 , 52)
    ,('2020-11-03' , 10 , 20 , 52)
    ,('2020-11-04' , 5 , 0 , 57);

    SELECT FixedDate
    ,Invoiced
    ,rsInvoiced = SUM(Invoiced) OVER (ORDER BY FixedDate
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW)
    ,Paid
    ,rsPaid = SUM(Paid) OVER (ORDER BY FixedDate
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW)
    ,rsCashflow = SUM(Invoiced) OVER (ORDER BY FixedDate
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW) - SUM(Paid) OVER (ORDER BY FixedDate
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW)
    FROM
    (SELECT FixedDate = CAST(TextDate AS DATE)
    , Invoiced
    , Paid
    , Cashflow
    FROM Tempdata) d

    • This reply was modified 1 month, 3 weeks ago by  pietlinden.
  • I don't have the column 'Cashflow' in may tables, is what I want to calculate. As ScottPletcher said, I need a running total.

  • I've managed to add the following line to the script:

    number = ROW_NUMBER() OVER (ORDER BY x.d_y_m)

    So, now I have something to order it by.

    But I can't get it to do a running sum.

  • Read up on windowing functions. This:

    ,rsPaid = SUM(Paid) OVER (ORDER BY FixedDateROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    does a running sum.

     

  • milo1981 wrote:

    I don't have the column 'Cashflow' in may tables, is what I want to calculate. As ScottPletcher said, I need a running total.

    So tell me where in pietlinden's script where he read from a "Cashflow" column.  "Must look eye" . 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • By the way, I'm using SQL Server 2005.

    I've modified my script as suggested by pietlinden:

    WITH D AS (

    SELECT CONVERT(VARCHAR, '2020.11.01', 102) AS d_y_m,

    CAST('2020-11-01' AS DATETIME) AS dt

    UNION ALL

    SELECT CONVERT(VARCHAR, DATEADD(dd, 1, z.dt), 102) AS d_y_m,

    DATEADD(dd, 1, z.dt)

    FROM D z

    WHERE DATEADD(dd, 1, z.dt) <= '2020-11-30')

    SELECT x.d_y_m, ISNULL(SUM(y.Total), 0) AS [Invoiced], ISNULL(SUM(FEI.Total), 0) AS [Paid] FROM D x,

    rsPaid = ISNULL(SUM(y.Total), 0)-ISNULL(SUM(FEI.Total), 0) OVER (ORDER BY x.d_y_m ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    LEFT JOIN Invoices y ON CONVERT(VARCHAR, y.InvoiceDate, 102) = x.d_y_m

    LEFT JOIN Payments AS FEI ON CONVERT(VARCHAR, FEI.PaymentDate, 102) = x.d_y_m

    GROUP BY x.d_y_m

    ORDER BY x.d_y_m OPTION (MAXRECURSION 0)

    I get an error:

    Incorrect syntax near the keyword 'OVER'.
  • This problem is an interesting problem and so I built two tables each containing more than 102 Million rows spread across 5 years (60 months) with each month containing more than 1.7 million rows each and the tables are each of 12GB in size (I added a "fluff" column of 100 bytes to simulate other columns being present).  I have the run time to solve this problem (and several others) down to 354ms.

    If we look at the query you posted it takes forever on just a million rows in each table (after fixing the code for column names).  Your code also has different table names and columns names from what you posted in the original post.

    milo1981 wrote:

    Thank you, all, for your answers. Sorry, I posted in a hurry without much details.

    So far, I have the following query:

    WITH D AS ( SELECT CONVERT(VARCHAR, '2020.11.01', 102) AS d_y_m, CAST('2020-11-01' AS DATETIME) AS dt UNION ALL SELECT CONVERT(VARCHAR, DATEADD(dd, 1, z.dt), 102) AS d_y_m, DATEADD(dd, 1, z.dt) FROM D z WHERE DATEADD(dd, 1, z.dt) <= '2020-11-30') SELECT x.d_y_m, ISNULL(SUM(y.Total), 0) AS [Invoiced], ISNULL(SUM(FEI.Total), 0) AS [Paid] FROM D x LEFT JOIN Invoices y ON CONVERT(VARCHAR, y.InvoiceDate, 102) = x.d_y_m LEFT JOIN Payments AS FEI ON CONVERT(VARCHAR, FEI.PaymentDate, 102) = x.d_y_m GROUP BY x.d_y_m ORDER BY x.d_y_m OPTION (MAXRECURSION 0)

    The real problem is that your code isn't going to do the job with our without your running total simply because it takes too long.  The big problem is that you're trying to combine the "presentation layer" with the "data layer" and, as a result, you have a super non-SARGable query with a shedload of unnecessary joins.

    That also means that the folks that have been trying to help have been working on the wrong problem and, without meaning to be mean, it's all your fault because your original post isn't only lacking the details people need, but it doesn't even match your query for column or table names.

    I asked for things like the data types of the columns as a very minimum and you've still not provided even that.

    Now... if you'll provide the CORRECT table names and the CORRECT column names and the ACTUAL datatypes for those columns, I'll show you some PFM that'll make your day, not only for this reporting query, but for many more to come.  Take your time and do it right and so will I.  😀

    Also, please read the article at the first link in my signature line below so we don't have to go through this crazy stuff in the future and you'll have the correct answer in coded form much more quickly. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • milo1981 wrote:

    By the way, I'm using SQL Server 2005.

    For the love of Pete... why on Earth would you post your problem in a 2014 forum then?  Any other caveats you'd like to share with us???

    Heh... you're really starting to piss me off.

    We can still do this if you post the information I just requested in my previous post and in about the same amount of time.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you, Jeff for taking the time to respond to my question and I'm sorry for my lack of how-to-post knowledge.

    I usually don't post stuff and ask for advice.

    I like to use google and search for anwers on forums and try to solve it myself, but this problem seems to be too hard for my level of SQL knowledge.

    My two tables scripts:

    /****** Object: Table [dbo].[Invoices] Script Date: 05.12.2020 22:50:00 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[Invoices](
    [InvoiceID] [int] IDENTITY(1,1) NOT NULL,
    [InvoiceDate] [smalldatetime] NULL,
    [Total] [decimal](18, 2) NULL,
    CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED
    (
    [InvoiceID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    /****** Object: Table [dbo].[Payments] Script Date: 05.12.2020 22:50:27 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[Payments](
    [PaymentID] [int] IDENTITY(1,1) NOT NULL,
    [PaymentDate] [smalldatetime] NULL,
    [Total] [decimal](18, 2) NULL,
    CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
    (
    [PaymentID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    My sample data:

     


    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-01 00:00:00', 13.4)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-02 00:00:00', 35.7)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-04 00:00:00', 43.9)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-09 00:00:00', 89.1)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-10 00:00:00', 51.8)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-14 00:00:00', 13.3)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-16 00:00:00', 90.2)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-19 00:00:00', 23.8)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-20 00:00:00', 83.1)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-22 00:00:00', 55.4)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-23 00:00:00', 33.8)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-26 00:00:00', 89.3)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-27 00:00:00', 11.2)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-28 00:00:00', 63.8)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-29 00:00:00', 80.4)
    INSERT INTO Invoices (InvoiceDate, Total) VALUES ('2020-11-30 00:00:00', 23.8)

    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-02 00:00:00', 29.5)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-03 00:00:00', 24.6)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-05 00:00:00', 43.8)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-08 00:00:00', 89.2)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-11 00:00:00', 41.7)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-13 00:00:00', 23.4)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-15 00:00:00', 80.3)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-18 00:00:00', 33.7)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-21 00:00:00', 73.5)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-23 00:00:00', 65.3)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-24 00:00:00', 23.7)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-25 00:00:00', 99.2)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-28 00:00:00', 31.1)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-28 00:00:00', 53.9)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-29 00:00:00', 70.2)
    INSERT INTO Payments (PaymentDate, Total) VALUES ('2020-11-30 00:00:00', 83.5)?

    Hope this helps you figure out the problem better and help me find an answer.

     

  • That, good sir, is an excellent post.  Thank you for taking the time to post it.  I'll be back. 😀

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 25 total)

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