January 10, 2019 at 2:30 pm
Hello community,
I am writing a query to calculate the nr. of pairs that can put on BOX of 10 pairs.
On my customer order i put all the information to do the calculation
I declare a variable that indicates how many pairs the box can hold, in my example 10.Then for each line I divide the quantity by 10 and I find the whole number, then the rest of the division is summed the quantity of the second and is again divided by 10 and so on.
Curiously from the 3 line and above the calculation is wrong, because the value that should be in the column "acumulatedofPairs" should give 87 and not 89.
This is my query :
DECLARE @Tarifa AS INT
SET @Tarifa = 10
select
ROW_NUMBER() OVER (ORDER BY bi.obrano,bi.dataobra, bi.ref, bi.design, bi.cor, bi.tam) AS SeqNo,
bi.nmdos, bi.obrano, CONVERT(VARCHAR(10),bi.dataobra,104) AS data, bi.ref, bi.design, bi.cor, bi.tam,@Tarifa [MaxPairsinBox] ,
bi.qtt
-
ISNULL((SELECT sum(PACKLST.qtt) FROM bi (nolock) PACKLST WHERE PACKLST.ndos=11 and PACKLST.oobistamp=bi.bistamp),0) [order_qty_Pairs] ,
+
(CASE WHEN ROW_NUMBER() OVER ( ORDER BY bi.obrano, bi.dataobra, bi.ref, bi.design, bi.cor, bi.tam) > 1 THEN
bi.qtt
-
ISNULL((SELECT sum(PACKLST.qtt) FROM bi (nolock) PACKLST WHERE PACKLST.ndos=11 and PACKLST.oobistamp=bi.bistamp),0)
+ SUM(bi.qtt % @Tarifa) OVER (ORDER BY bi.obrano, bi.dataobra, bi.ref, bi.design, bi.cor, bi.tam
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) - ( bi.qtt % @Tarifa)
ELSE qtt END ) [acumulatedofPairs] ,
0.00 [NrofBoxes],
(CASE WHEN ROW_NUMBER() OVER ( ORDER BY bi.obrano, bi.dataobra, bi.ref, bi.design, bi.cor, bi.tam) > 1 THEN
bi.qtt
-
ISNULL((SELECT sum(PACKLST.qtt) FROM bi (nolock) PACKLST WHERE PACKLST.ndos=11 and PACKLST.oobistamp=bi.bistamp),0)
+ SUM(bi.qtt % @Tarifa) OVER (ORDER BY bi.obrano, bi.dataobra, bi.ref, bi.design, bi.cor, bi.tam
ROWS UNBOUNDED PRECEDING) - ( bi.qtt % @Tarifa)
ELSE ( bi.qtt % @Tarifa) END ) % @Tarifa [RestofDivision]
from bo (nolock)
INNER JOIN bi (nolock) on bi.bostamp=bo.bostamp
WHERE bo.ndos=24
AND bo.fechada = 0
AND (bi.qtt-bi.qtt2)>0
and bo.no=707
GROUP BY bi.bistamp, bi.nmdos, bi.obrano, bi.dataobra, bi.ref, bi.design, bi.cor, bi.tam, bi.qtt
Also, i prepare a Temporary table with the values for better understanding and testing:
CREATE TABLE #tmpTable (
SeqNo bigint,
nmdos varchar(24),
obrano decimal(10),
data varchar(10),
ref char(18),
design varchar(60),
cor varchar(25),
tam varchar(25),
MaxPairsinBox int,
order_qty_Pairs decimal(38, 4),
acumulatedofPairs decimal(38, 4),
Resto decimal(14, 4))
INSERT #tmpTable VALUES
(1,'Encomenda de Cliente',164,'19.11.2018','SS18306','SAP. KLEIN','AZUL/BRONZE','36',10,38.0000,38.0000,8.0000),
(2,'Encomenda de Cliente',164,'19.11.2018','SS18306','SAP. KLEIN','AZUL/BRONZE','37',10,63.0000,71.0000,1.0000),
(3,'Encomenda de Cliente',164,'19.11.2018','SS18306','SAP. KLEIN','AZUL/BRONZE','38',10,86.0000,89.0000,7.0000),
(4,'Encomenda de Cliente',164,'19.11.2018','SS18306','SAP. KLEIN','AZUL/BRONZE','39',10,63.0000,69.0000,0.0000),
(5,'Encomenda de Cliente',164,'19.11.2018','SS18306','SAP. KLEIN','AZUL/BRONZE','40',10,38.0000,41.0000,8.0000),
(6,'Encomenda de Cliente',164,'19.11.2018','SS18306','SAP. KLEIN','AZUL/BRONZE','41',10,12.0000,20.0000,0.0000)
SELECT
* FROM #tmpTable
DROP TABLE #tmpTable
Could someone give me some help to solve me problem.
Many thanks,
Luis
January 10, 2019 at 3:21 pm
Your query contains two tables, but you've only provided one sample table. Furthermore, the fields in your sample table do not match the fields in either of the tables in your query, so it's hard to know which table the sample is supposed to represent. Also, your query filters on values that aren't contained in your sample. That makes it very, very difficult to test, and thus to understand.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply