[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Running sum

Posted on 2011-10-12
5
Medium Priority
?
427 Views
Last Modified: 2012-05-12
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.
Which is why I'm asking





0
Comment
Question by:EWHTLC
  • 2
  • 2
5 Comments
 
LVL 28

Accepted Solution

by:
strickdd earned 1200 total points
ID: 36956590
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

Open in new window


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

by:deighton
deighton earned 800 total points
ID: 36956630
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
	

Open in new window

0
 
LVL 18

Expert Comment

by:deighton
ID: 36956723
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

by:EWHTLC
ID: 36957429
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
pers_order_header
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
  inner join Pers_order_header ph
    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

by:EWHTLC
ID: 36958693
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question