Solved

# SQL Running sum

Posted on 2011-10-12
411 Views
Hi,

I'm trying to create a stored procedure that will return records based on a running total.

I am passing a total order value to the stored procedure.

I want to return all orders sorted by due date up to the runnnint total order value being less than my parameter.

ie parameter value = 500.00

order       due date         order value
123         12/10/2011             50.00
124         09/10/2011             150.00
125         09/10/2011             200.00
126         10/10/2011             300.00

So if I sort the output by due date and looking for a total order value of < 500
my stored procedure should ony return orders 124 and 125 for a value of 350

I've looked aroung and the general consensus is that running totals are quite difficult in SQl.

0
Question by:EWHTLC

LVL 28

Accepted Solution

Depending on how often this query will be executed, you can create a UDF that sums all the values for dates <= row.Date. It would look something like this:

``````    CREATE FUNCTION dbo.SumPreviousValues (@when DATETIME)
RETURNS Int
AS
BEGIN
Declare @sum int
SELECT @sum = SUM(OrderValue) FROM OrderTable WHERE DueDate <= @when
RETURN @sum
End

--Stored procedure code
SELECT * FROM
(
SELECT Order
,DueDate
,OrderValue
,dbo.SumPreviousValues(DueDate) As RunningTotal
FROM OrderTable
) allRecords
WHERE allRecords.RunningTotal < @parameterValue
``````

If you need to run this more often, let me know and I will work out a more efficient query.
0

LVL 18

Assisted Solution

try this join logic, joining records to itself and all lower order records, then grouping by first incantation of the table

``````DECLARE @LIMIT float ;
SET @LIMIT = 500;

SELECT SUBQ.* FROM (
select O1.DueDate, O1.[Order],Sum(O2.ordervalue) AS RunningTotal from orders O1 JOIN orders O2 ON O1.duedate >O2.duedate OR O1.DueDate=O2.Duedate and O1.[order] >= O2.[Order]
GROUP BY O1.DueDate,O1.[Order]
) SUBQ
WHERE SUBQ.RunningTotal <= @LIMIT

``````
0

LVL 18

Expert Comment

BTW, it isn't clear what order the running total is made when multiple orders appear on the same day, I decided that in that case you sorted by date then order id as a 'tie breaker', which I think works ok
0

Author Comment

Hi There,
Thanks for this I feel it's almost there.

I didn't make my self clear enough in my question.
I do not want to selct based on the due but need to sort by due date and select based on a target order value being hit.
(Even the order value field name wasn't really correct)

I actually got somewhere by doing my selection and sort into a temporary (permanemt) table
I sent use the function to get my running sum

THe process works if I run the sp from within SQL.

However, when runn from my Access prject I get a message stating that
"THe stored procedure exected successfully but did not return any records."

Ca you see where I'm going wrong.
Here's my sp and my function

SP
USE [Test_EB_Production]
GO
/****** Object:  StoredProcedure [dbo].[sp_Pers_Allocation_Orders]    Script Date: 10/12/2011 18:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Migrated from Access query
-- Author:            <Eric Harris>
-- Description:      <Process Pers Employees
-- =============================================

ALTER PROCEDURE [dbo].[sp_Pers_Allocation_Orders]
(
@patt as nvarchar(50),
@lett as int
)
AS
Begin

truncate table dbo.PersTempOrderAlloc
insert into dbo.PersTempOrderAlloc
(
LM_Order,
Total_Letters
)
select
LM_Order,
Total_Letters
from
where pattern_match like (case when @patt = 'PERS - Mixed' then '%' else @patt end)
order by due_date

select
ph.LM_Order,
ph.Due_date,
ph.Pattern_match,
ph.Total_Letters,
dbo.sumletters(ph.lm_order) as running_total

from
dbo.PersTempOrderAlloc p1
on p1.lm_order = ph.lm_order
where dbo.sumletters(ph.lm_order) <= @lett

END

Here's my function
USE [Test_EB_Production]
GO
/****** Object:  UserDefinedFunction [dbo].[SumLetters]    Script Date: 10/12/2011 18:31:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SumLetters]
(
@ord nvarchar(20)
)
RETURNS Int
AS
BEGIN
declare @sum int
SELECT @sum = SUM(total_Letters)
FROM dbo.PersTempOrderAlloc
WHERE lm_order <= @ord

RETURN @sum
End
0

Author Comment

I answered my own question in the end.
Basically it was a schoolboy error.
My sp couldn't be a multiple pass sp.
I ended up creating a final table and used that as the source in my application.
It's not pretty but it works

THanks for your help Ill split the points as I used bits from both solutions in the end.
0

## Featured Post

This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.