February 12, 2014 at 11:25 pm
Hi
I have a table that looks like this;
CustomerID Amount Date RunninTotal
1 50 01/01/2014 50
1 30 10/01/2014 80
2 100 06/05/2014 100
2 60 12/08/2014 160
3 23 23/09/2014 23
4 34 08/011/2014 34
For each customer I need to be able to return a daily running balance (resultset), up until today's date or the end date of the report. The start date for calculating the running balance will be the date of the first transaction.
The result should look like this
CustomerID Amount Date RunninTotal
1 50 01/01/2014 50
1 50 02/01/2014 50
1 50 03/01/2014 50
1 50 04/01/2014 50
1 50 05/01/2014 50
....
...
1 30 10/01/2014 80
...
...
2 100 06/05/2014 100
2 100 07/05/2014 100
2 100 08/05/2014 100
...
...
2 60 12/08/2014 160
Can someone please assist in achieving this result.
Thanks
February 12, 2014 at 11:59 pm
your table structure is already showing running total column, i think you want to repeat the data until the new value come.
the date column is not a proper date, is it a typo or u have date in VARCHAR?
February 13, 2014 at 12:05 am
yes...you are right
and the date column is a typo...it is actually in proper date format.
Do you have a solution?
February 13, 2014 at 1:27 am
If you need details about customers and their running totals for each day you would need a table or function generating one entry for each day. For instance you could use the folowing function developed by Itzik Ben-Gan (http://tsql.solidq.com):
CREATE FUNCTION dbo.GetDates(@from AS DATETIME, @to AS DATETIME)
RETURNS @Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
DECLARE @rc AS INT
SET @rc = 1
INSERT INTO @Dates VALUES(@from)
WHILE @from + @rc * 2 - 1 <= @to
BEGIN
INSERT INTO @Dates
SELECT dt + @rc FROM @Dates
END
INSERT INTO @Dates
SELECT dt + @rc FROM @Dates
WHERE dt + @rc <= @to
RETURN
END
GO
The next thing , actually the first thing you had to do before you post your question is to prepare sample data in an executable format. Something like this:
DECLARE @Orders AS TABLE (
CustomerID INT NOT NULL,
Amount INT NOT NULL,
OrderDate DATE NOT NULL,
RunningTotal INT NOT NULL
)
INSERT INTO @Orders
VALUES
(1 ,50 ,'20140101', 50),
(1 ,30 ,'20140110', 80),
(2 ,100, '20140605', 100),
(2 ,60, '20140812', 160),
(3 ,23 ,'20140923', 23),
(4, 34 ,'20141108', 34);
This can help us to help you faster and with no errors.
And finaly, here is a solution for your report. As twin.devil wrote, you already have running total precalculated for each entry in the table. You need only to match it with a calendar date.
This query delivers a list of customers and runing totals (one entry for each customer per day having an order until this day) for 2014:
SELECT
appl.CustomerID AS CustomerID,
dt AS CalendarDate,
appl.RunningTotal AS RunningTotal
FROM
dbo.GetDates('20140101', '20141231')
CROSS APPLY
(
SELECT TOP (1) CustomerID, RunningTotal
FROM @Orders
WHERE OrderDate <= dt
ORDER BY OrderDate DESC
) appl
___________________________
Do Not Optimize for Exceptions!
February 13, 2014 at 5:34 pm
Thanks heaps guys....much appreciated
February 13, 2014 at 6:04 pm
You should change your function to an inLine table-valued function to improve performance.
CREATE FUNCTION dbo.GetDates(@from AS DATETIME, @to AS DATETIME)
RETURNS TABLE
AS
RETURN
WITH E1(N) AS(
SELECT N FROM(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(N)
),
E3(N) AS(
SELECT a.N FROM E1 a, E1 b, E1 c
),
cteTally(dt) AS(
SELECT TOP(DATEDIFF( DD, @from, @to) + 1)
DATEADD( dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @FROM) dt
FROM E3
)
SELECT dt
FROM cteTally
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply