April 17, 2023 at 10:15 pm
Hello comunity,
I need to make a query that can retrieve only the last record but with LAST_VALUE(epcpond) as "LastPCM" and also create a RunningTotals for quantities.
My problem is LAST_VALUE(epcpond) as "LastPCM" are correct but the value of Runningtotals are not!
This is a simple script to test:
DROP TABLE #temp
CREATE TABLE #temp (ref VARCHAR(18), cmdesc VARCHAR(25), adoc VARCHAR(10), cm INT, datalc DATE, qtt NUMERIC(18,4), epcpond NUMERIC(18,6))
GO
INSERT INTO #temp ( ref, cmdesc,adoc,cm,datalc,qtt, epcpond)
SELECT 'ORT A4','S01 Stock inicial', '1', 49, '2014-06-30', 3.000, 27.057044
UNION
SELECT 'ORT A4','S03 Transf. Entrada','1549',48, '2014-08-06', 1.000, 27.057044
UNION
SELECT 'ORT A4','C11-Fatura Simplific', '193',72, '2014-08-06', 1.000, 27.057044
UNION
SELECT 'ORT A4','S03 Transf. Saida', '1549',98, '2014-08-06', 1.000, 27.057044
UNION
SELECT 'ORT A4','S03 Transf. Entrada', '1799',48, '2014-08-12', 1.000, 27.057044
UNION
SELECT 'ORT A4','C11-Fatura Simplific', '230',72, '2014-08-12', 1.000, 27.057044
UNION
SELECT 'ORT A4','S03 Transf. Saida', '1799',98, '2014-08-12', 1.000, 27.057044
SELECT main.*
FROM
(SELECT ref ,cmdesc, adoc,cm,datalc, qtt
,sum(case when (cm < 50 OR cm = 60) then ABS(qtt) else -qtt end) over (ORDER BY datalc,cm ASC) AS [RunningTotal]
,Row_Number() OVER (ORDER BY datalc, cm DESC) AS RowNum
, LAST_VALUE(epcpond) OVER(ORDER BY datalc,cm DESC ) [LastPCM]
FROM #temp a
WHERE a.ref = 'ORT A4'
AND a.datalc = '20140812'
)main
WHERE main.RowNum = 1
the value for "LASTPCM" are 27.057044 and is correct , but the runningTotals of quantities are wrong because they return : -1.0000 and the correct value are : 1.0000
Someone could help me with this ?
Many thanks,
Luis
April 18, 2023 at 7:13 am
It would appear that you have different ORDER BY clauses for your RunningTotal vs RowNum and LastPCM fields. This will impact the outcome.
April 18, 2023 at 7:24 am
FYI, in your RunningTotal, you want to frame the query by including a Rows clause
ORDER BY a.datalc, a.cm ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
April 18, 2023 at 9:06 am
Hi DesNorton,
Thanks for your reply, also i changed my query following your Advise :
SELECT main.*
FROM
(SELECT ref ,cmdesc, adoc,cm,datalc, qtt
,sum(case when (cm < 50 OR cm = 60) then ABS(qtt) else -qtt end) over (ORDER BY datalc,cm ASC) AS [RunningTotal]
,Row_Number() OVER (ORDER BY datalc, cm ASC) AS RowNum
, LAST_VALUE(epcpond) OVER( ORDER BY datalc,cm rows between unbounded preceding and current row) [LastPCM]
FROM #temp a
WHERE a.ref = 'ORT A4'
AND a.datalc = '20140812'
)main
WHERE main.RowNum = 1
but the result of RunningTotal is still incorrect : -1.0000 and not 1.0000
Thanks,
Luis
April 18, 2023 at 9:40 am
The value for running total looks correct to me. You're only returning the value of the first row, which means that the value of "Running Total" is actually the SUM of a single row. That row has a value of 98 for cm which is not <50 and not 60, which means that -qtt is returned. As qtt has a value of 1 then -1 is returned. Why is -1 therefore "wrong"?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 18, 2023 at 9:40 am
Seemed to double post
April 18, 2023 at 9:41 am
,ROW_NUMBER() OVER (ORDER BY datalc, cm desc) AS RowNum
Using the above on 20140812 the first cm (RowNum = 1) will be 98 so your rules make the running total -1. You may want:
,ROW_NUMBER() OVER (ORDER BY datalc, cm) AS RowNum
which will make the first cm 48 and the running total 1.
April 18, 2023 at 9:48 am
If I run your code against your sample data, i get RuningTotal = 1.0000
April 18, 2023 at 10:28 am
Hello again,
I changed my query like this but the result of RunnigTotal are wrog:
SELECT main.*
FROM (SELECT ref
,datalc,cmdesc, adoc
,sum(case when (cm < 50 OR sl.cm = 60) then ABS(qtt) else -qtt end) over (ORDER BY datalc,cm ASC) AS [RunningTotal]
,Row_Number() OVER (ORDER BY datalc, cm ) AS RowNum
--, LAST_VALUE(epcpond) OVER(ORDER BY datalc,cm ASC ) [LastPCM]
, LAST_VALUE(epcpond) OVER(
ORDER BY datalc,cm ASC rows between unbounded preceding and current row) [LastPCM]
FROM sl
WHERE sl.ref = 'ORT A4 '
AND sl.datalc = '20220729'
) main
WHERE main.rownum = 1
Values returns :
refdatalc cmdesc adocRunningTotalRowNumLastPCM
ORT A4 2022-07-29 C11-Fatura Simplific1101 -1.000 127.914000 (BAD RunningTotal)
The result is wrong, this is the calculation:
a) SELECT 'ORT A4','S01 Stock inicial', '1', 49, '2014-06-30', 3.000, 27.057044
UNION
b) SELECT 'ORT A4','S03 Transf. Entrada','1549',48, '2014-08-06', 1.000, 27.057044
UNION
c) SELECT 'ORT A4','C11-Fatura Simplific', '193',72, '2014-08-06', 1.000, 27.057044
UNION
d) SELECT 'ORT A4','S03 Transf. Saida', '1549',98, '2014-08-06', 1.000, 27.057044
UNION
e) SELECT 'ORT A4','S03 Transf. Entrada', '1799',48, '2014-08-12', 1.000, 27.057044
UNION
f) SELECT 'ORT A4','C11-Fatura Simplific', '230',72, '2014-08-12', 1.000, 27.057044
UNION
g) SELECT 'ORT A4','S03 Transf. Saida', '1799',98, '2014-08-12', 1.000, 27.057044
Formula:
a =3, b= 1, c=-1, d= -1, e= 1, f= -1, g= -1 THEN
3 + 1 -1 -1 +1 -1 -1 = 1.0000 (Correct Value for RunningTotal)
Best regards,
Luis
April 18, 2023 at 10:33 am
Hello again,
I changed my query like this but the result of RunnigTotal are wrog
It seems like you don't want a running total here, just a windowed SUM. Remove the ORDER BY from your "running total" and then rename the expression to something else, like Total.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 18, 2023 at 11:04 am
Hi Tom,
I rewrite my query like that, but i have the same problem. Maybe i´am missing something !
SELECT main.*
FROM
(SELECT ref ,cmdesc, adoc,cm,datalc, qtt
,sum(case when (cm < 50 OR cm = 60) then ABS(qtt) else -qtt end) over () AS [Total]
,Row_Number() OVER (ORDER BY datalc, cm ASC) AS RowNum
, LAST_VALUE(epcpond) OVER(ORDER BY datalc,cm ASC ) [LastPCM]
FROM #temp a
WHERE a.ref = 'ORT A4'
AND a.datalc = '20140812'
)main
WHERE main.RowNum = 1
Result:
refcmdesc adoccmdatalc qtt TotalRowNumLastPCM
ORT A4S03 Transf. Entrada1799482014-08-121.0000-1.0000127.057044
Best regards,
Luis
April 18, 2023 at 9:34 pm
Hello again
I found the solution fo my problem like that:
SELECT TOP(1)
main.*
from
( SELECT ref ,cmdesc, adoc,cm,datalc,
qtt
,sum(case when (cm < 50 OR sl.cm = 60) then ABS(qtt) else -qtt end) OVER (ORDER BY datalc, cm asc) AS [RunningTotal]
,Row_Number() OVER (ORDER BY datalc, cm ASC ) AS RowNum
, LAST_VALUE(epcpond) OVER(ORDER BY datalc, cm asc) [LastPCM]
FROM sl
WHERE ref = 'ORT A4'
AND sl.datalc <= '20180914' ) main
ORDER BY 8 desc
This work perfectly returning the RunningTotal and LastPCM on every date i choose on where condition.
Many thanks
Luis
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply