March 20, 2016 at 12:59 pm
Hello comunity
I have build this TVF because i need to return the SUM of stock quantity and also the medium price cost.
The problem is when i have on same date 2 movements, because the medium price are ok, but not the sum of quantity.
I Order my Query by REF, datalc(date of movment) and cm(code of stock movement), that is the correct order for my query.
IF OBJECT_ID(N'tvf_PRODUCTandPCM') IS NOT NULL
DROP FUNCTION tvf_PRODUCTandPCM;
GO
CREATE FUNCTION [dbo].[tvf_PRODUCTandPCM]
(
@REF VARCHAR(18)
,@EndDate DATE
)
RETURNS TABLE
AS
RETURN
WITH cte(ref ,stock, epcpond)
AS (
SELECT TOP (1)
sll.ref,
StockAData = sum(CASE WHEN sll.cm <50 THEN sll.qtt ELSE -sll.qtt END)
OVER (Order By ref,datalc,cm ROWS UNBOUNDED PRECEDING),
sll.epcpond
FROM
SL AS sll
WHERE Rtrim(sll.REF) = Rtrim(@REF)
AND sll.datalc <= @EndDate
ORDER BY
sll.datalc DESC
)
SELECT ref
,Isnull(stock,0) [Stock]
,epcpond
FROM cte
--calling my TVF
SELECT TOP (1)
SLL.ref,
SLL.design,
MyTVF.epcpond,
MyTVF.stock
FROM sl AS SLL
INNER JOIN
[dbo].[tvf_PRODUCTandPCM]('CT 2776','2015-03-06') AS MyTVF
ON SLL.REF = MyTVF.ref
I send a very simple script to test my TVF function:
CREATE table #MYSL (datalc DATE, ref VARCHAR(18),design VARCHAR(60), epcpond NUMERIC(14,3),cm NUMERIC(2,0), qtt NUMERIC(14,2))
INSERT INTO #MYSL (datalc ,ref, design , epcpond ,cm , qtt )
SELECT '20150224','CT 2776','Ap. Ultra Sons Intelect Mobile',575.601,1, 10
UNION ALL
SELECT '20150224','CT 2776','Ap. Ultra Sons Intelect Mobile',575.601,51, 5
UNION ALL
SELECT '20150224','CT 2776','Ap. Ultra Sons Intelect Mobile',575.601,51, 5
UNION ALL
SELECT '20150225','CT 2776','Ap. Ultra Sons Intelect Mobile',575.601,1, 2
UNION ALL
SELECT '20150230','CT 2776','Ap. Ultra Sons Intelect Mobile',600.60,1, 10
UNION ALL
SELECT '20150230','CT 2776','Ap. Ultra Sons Intelect Mobile',590.00,51, 2
Someone could give me some help.
Many thanks
Luis
March 21, 2016 at 1:08 pm
Your query uses T-SQL language features that are NOT a part of SQL 2008. Perhaps this needs to be posted in the forum appropriate to the version of SQL that you are using. If you are ARE using SQL 2008 (or 2008 R2), then you can't use the SUM function that way. Do you need to have that functionality re-written into your query, or is there some other problem?
Either way, I still don't have enough information on what your data represents to know what the problem is... Please be more detailed in explaining both the data and what the exact result you're looking for is. Also, one thing you had in your post was "Medium price"... might you have meant "median price" ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 21, 2016 at 1:50 pm
IF OBJECT_ID(N'tvf_PRODUCTandPCM') IS NOT NULL
DROP FUNCTION tvf_PRODUCTandPCM;
GO
CREATE FUNCTION [dbo].[tvf_PRODUCTandPCM]
(
@REF varchar(18)
,@EndDate date
)
RETURNS table
AS
RETURN
WITH SL_Reduced
AS ( SELECT * -- TODO: Do scrubbing logic here to get rid of double movements
FROM SL AS sll
WHERE RTRIM(sll.REF) = RTRIM(@REF)
AND sll.datalc <= @EndDate
),
cte ( ref, stock, epcpond )
AS ( SELECT TOP ( 1 )
sll.ref
,StockAData = SUM(CASE WHEN sll.cm < 50 THEN sll.qtt
ELSE -sll.qtt
END) OVER ( ORDER BY ref, datalc, cm ROWS UNBOUNDED PRECEDING )
,sll.epcpond
FROM SL_Reduced AS sll
WHERE RTRIM(sll.REF) = RTRIM(@REF)
AND sll.datalc <= @EndDate
ORDER BY sll.datalc DESC
)
SELECT ref
,ISNULL(stock, 0) [Stock]
,epcpond
FROM cte
--calling my TVF
SELECT TOP ( 1 )
SLL.ref
,SLL.design
,MyTVF.epcpond
,MyTVF.stock
FROM sl AS SLL
INNER JOIN [dbo].[tvf_PRODUCTandPCM]('CT 2776', '2015-03-06') AS MyTVF ON SLL.REF = MyTVF.ref
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply