Cast SUM

Hello!
Im trying edit this sql to show the refill amount of an product. Hope anyone can help or give some guidance.

For example

  1. There are an order for 7 iphones
  2. The pick location in the warehouse only has 5 iphones on location
  3. So I want one more filed in the sql that shows "refill 2"
(order amount) (stock balance) (new field wanted)
partno partdsc1 Kollinummer BeställtAntal balpl refill
123 Iphone 176601714 7 5 2

I guess that I should add something like this, but I cant figure out how to get it to work
CAST(SUM("_Beställt" - balpl) AS FLOAT) AS "_Påfyllnad"


;With TMP AS
(Select G.partno,SUM(amooncr) '_Antal på Buffert', L.partdsc1, balpl, pmha
FROM ASTRO_VIEW_CNT_G08T1 AS G
JOIN L62T1 AS L
ON G.partno=L.partno
Where wmha LIKE 'FBROD'
Group by G.partno, balpl, pmha, L.partdsc1),

TMP2 AS
(Select O.partno, CONVERT (INT,ROUND(SUM(reqquant),0)) AS '_BeställtAntal', TMP.partdsc1, balpl, pmha
FROM ASTRO_VIEW_CNT_O08R08 AS O
INNER JOIN TMP
ON O.partno = TMP.partno
Where CAST(oppdate AS DATE) LIKE LEFT(CAST(GETDATE()AS DATE), 10) AND (linestat =2 OR linestat =22) AND routeno NOT LIKE '9%'
Group by O.partno, balpl, pmha, TMP.partdsc1),

TMP3 AS
(Select TMP2.partno, TMP2.partdsc1, MAX(G.ecarrno) AS '_Kollinummer', TMP2._BeställtAntal, balpl, pmha
FROM ASTRO_VIEW_CNT_G08T1 AS G
JOIN TMP2
ON G.partno = TMP2.partno
JOIN
(select ecarrno, mha, rack, horcoor, vercoor, amooncr
FROM ASTRO_VIEW_CNT_G08T1) AS SUB
ON G.ecarrno=SUB.ecarrno
Where G.mha LIKE 'FBROD%' AND carrstat !=8
Group by TMP2.partno, TMP2.partdsc1, TMP2._BeställtAntal, balpl, pmha)

Select TMP3.*, mha, rack, horcoor, vercoor, amooncr
FROM TMP3
INNER JOIN ASTRO_VIEW_CNT_G08T1 AS G
ON TMP3._Kollinummer = G.ecarrno
Order by pmha

without any sample data or ddl, I would guess at adding this to the final query

TMP2._BeställtAntal - balpl as Refill

thanks it worked!

Two more questions

  1. How do I get rid of the decimals? I added this CONVERT (INT,ROUND(SUM(reqquant),0)) in the sql, but decimals still showing

  2. Any simple way to only show refill values that doesn't contain 0 or -values? For example I dont need to see products that doesnt need to be refilled

_BeställtAntal = shows ordered amount
balpl = shows balance at pick location

image

I'm assuming your query looks something like this:

Select TMP3.*, mha, rack, horcoor, vercoor, amooncr, TMP2._BeställtAntal - balpl as Refill
FROM TMP3
INNER JOIN ASTRO_VIEW_CNT_G08T1 AS G
ON TMP3._Kollinummer = G.ecarrno
Order by pmha

then change it to
Select TMP3.*, mha, rack, horcoor, vercoor, amooncr, Cast(TMP2._BeställtAntal - balpl as int) as Refill
FROM TMP3
INNER JOIN ASTRO_VIEW_CNT_G08T1 AS G
ON TMP3._Kollinummer = G.ecarrno
and TMP2._BeställtAntal - balpl > 0
Order by pmha

the decimal is still there

image

;With TMP AS
(Select G.partno,SUM(amooncr) '_Antal på Buffert', L.partdsc1, balpl, pmha
FROM ASTRO_VIEW_CNT_G08T1 AS G
JOIN L62T1 AS L
ON G.partno=L.partno
Where wmha LIKE 'BFZ3'
Group by G.partno, balpl, pmha, L.partdsc1),

TMP2 AS
(Select O.partno, CONVERT (INT,ROUND(SUM(reqquant),0)) AS '_BeställtAntal', TMP.partdsc1, balpl, pmha
FROM ASTRO_VIEW_CNT_O08R08 AS O
INNER JOIN TMP
ON O.partno = TMP.partno
Where CAST(oppdate AS DATE) LIKE LEFT(CAST(GETDATE()AS DATE), 10) AND (linestat =2 OR linestat =22) AND routeno NOT LIKE '9%'
Group by O.partno, balpl, pmha, TMP.partdsc1),

TMP3 AS
(Select TMP2.partno, TMP2.partdsc1, MAX(G.ecarrno) AS '_Kollinummer', TMP2._BeställtAntal, balpl, pmha, CAST(TMP2._BeställtAntal - balpl AS INT) AS _Påfyllnad
FROM ASTRO_VIEW_CNT_G08T1 AS G
JOIN TMP2
ON G.partno = TMP2.partno
JOIN
(select ecarrno, mha, rack, horcoor, vercoor, amooncr
FROM ASTRO_VIEW_CNT_G08T1) AS SUB
ON G.ecarrno=SUB.ecarrno
Where G.mha LIKE 'BFZ%' AND carrstat !=8
and TMP2._BeställtAntal - balpl > 0
Group by TMP2.partno, TMP2.partdsc1, TMP2._BeställtAntal, balpl, pmha)

Select TMP3.*, mha, rack, horcoor, vercoor, amooncr
FROM TMP3
INNER JOIN ASTRO_VIEW_CNT_G08T1 AS G
ON TMP3._Kollinummer = G.ecarrno
Order by pmha

Your code formatted

;WITH tmp AS (
			 SELECT G.partno,
					Sum(amooncr) '_Antal på Buffert',
					L.partdsc1,
					balpl,
					pmha
			 FROM   astro_view_cnt_g08t1 AS G
					JOIN l62t1 AS L
					  ON G.partno = L.partno
			 WHERE  wmha LIKE 'BFZ3'
			 GROUP  BY G.partno,
					   balpl,
					   pmha,
					   L.partdsc1
			 ),
     tmp2 AS (
			 SELECT O.partno,
					CONVERT (INT, Round(Sum(reqquant), 0)) AS '_BeställtAntal',
					tmp.partdsc1,
					balpl,
					pmha
			 FROM   astro_view_cnt_o08r08 AS O
					INNER JOIN tmp
							ON O.partno = tmp.partno
			 WHERE  Cast(oppdate AS DATE) LIKE LEFT(Cast(Getdate()AS DATE), 10)
					AND ( linestat = 2
						   OR linestat = 22 )
					AND routeno NOT LIKE '9%'
			 GROUP  BY O.partno,
					   balpl,
					   pmha,
					   tmp.partdsc1
			  ),
     tmp3  AS (
				 SELECT tmp2.partno,
						tmp2.partdsc1,
						Max(G.ecarrno)                            AS '_Kollinummer',
						tmp2._best�lltantal,
						balpl,
						pmha,
						Cast(tmp2._best�lltantal - balpl AS INT) AS _Påfyllnad
				 FROM   astro_view_cnt_g08t1 AS G
						JOIN tmp2
						  ON G.partno = tmp2.partno
						JOIN (SELECT ecarrno,
									 mha,
									 rack,
									 horcoor,
									 vercoor,
									 amooncr
							  FROM   astro_view_cnt_g08t1) AS SUB
						  ON G.ecarrno = SUB.ecarrno
				 WHERE  G.mha LIKE 'BFZ%'
						AND carrstat != 8
						AND tmp2._best�lltantal - balpl > 0
				 GROUP  BY tmp2.partno,
						   tmp2.partdsc1,
						   tmp2._best�lltantal,
						   balpl,
						   pmha
			    )
SELECT 
	  tmp3.*
    , mha
    , rack
    , horcoor
    , vercoor
    , amooncr
FROM   
    tmp3
       INNER JOIN 
    astro_view_cnt_g08t1 AS G
       ON 
		  tmp3._kollinummer = G.ecarrno
ORDER  BY 
    pmha

I solved it, thanks!