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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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_
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_
(
@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
from
dbo.PersTempOrderAlloc p1
inner join Pers_order_header ph
on p1.lm_order = ph.lm_order
where dbo.sumletters(ph.lm_order
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
ASKER
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.
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.