Hello,
I have two simple tables:
I want to generate a report which will display day by day the cashflow (total invoices - total payments received).
December 3, 2020 at 4:07 pm
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
December 3, 2020 at 5:28 pm
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
Change is inevitable... Change for the better is not.
December 3, 2020 at 6:22 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 3, 2020 at 7:31 pm
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
December 3, 2020 at 9:19 pm
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
December 4, 2020 at 7:26 am
I don't have the column 'Cashflow' in may tables, is what I want to calculate. As ScottPletcher said, I need a running total.
December 4, 2020 at 8:22 am
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.
December 4, 2020 at 4:22 pm
Read up on windowing functions. This:
,rsPaid = SUM(Paid) OVER (ORDER BY FixedDateROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
does a running sum.
December 4, 2020 at 4:56 pm
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
Change is inevitable... Change for the better is not.
December 5, 2020 at 9:57 am
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'.
December 5, 2020 at 3:30 pm
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.
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
Change is inevitable... Change for the better is not.
December 5, 2020 at 3:41 pm
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
Change is inevitable... Change for the better is not.
December 5, 2020 at 8:53 pm
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.
December 6, 2020 at 1:02 am
That, good sir, is an excellent post. Thank you for taking the time to post it. I'll be back. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply