Cast SUM

1

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

2

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

TMP2._BeställtAntal - balpl as Refill

3

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

4

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

5

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

6

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
7

I solved it, thanks!