Link to home
Start Free TrialLog in
Avatar of Eric Harris
Eric Harris

asked on

SQL Running sum

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





ASKER CERTIFIED SOLUTION
Avatar of strickdd
strickdd
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of Eric Harris
Eric Harris

ASKER

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
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.