sum subtract in sql server

Apurva Singh

8y1k

I have a a select commamd

select sd.section_id,Bm.Art,Color,sec.fld_barcode,sec.fld_sectionname,pro_barcode,sd.size,sum(sd.pro_Inqty)-Sum(sd.pro_outqty)as StockInhand from stock_trans_master sm

join stock_trans_details sd

on sm.st_id=sd.st_id

join BarcodeMaster Bm

on Bm.Barcode_m=sd.pro_barcode

join tbl_sectionmaster sec

on sd.section_id=sec.fld_id

where voucherdate <= '11-07-2018 00:00:00'

and (pro_barcode like ''+'%' )

AND (Color like 'S.GREEN' )

AND

(Bm.size like '4' )

AND

(Art like 'AUTUMN' )

and

group by sd.section_id,Bm.Art,sd.size,sec.fld_barcode,sec.fld_sectionname,pro_barcode,Color

This statement return now all data which stockinhand in equal to 0 value

now i want (sd.pro_Inqty)-Sum(sd.pro_outqty)as StockInhand is always return greater than 0 value.

Please suggest me.

thanx

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
I have copied and pasted your query below and done a bit of formatting and have added a having clause which should give expected output.

There is one unfinished 'and' just above the group by which I have commented out.
select sd.section_id
  ,Bm.Art,Color
  ,sec.fld_barcode
  ,sec.fld_sectionname
  ,pro_barcode,sd.size
  ,sum(sd.pro_Inqty)-Sum(sd.pro_outqty)as StockInhand
from stock_trans_master sm
join stock_trans_details sd on sm.st_id = sd.st_id
join BarcodeMaster Bm on Bm.Barcode_m = sd.pro_barcode
join tbl_sectionmaster sec on sd.section_id = sec.fld_id
where voucherdate <= '11-07-2018 00:00:00'
  and (pro_barcode like ''+'%' )
  and (Color like 'S.GREEN' )
  and (Bm.size like '4' )
  and (Art like 'AUTUMN' )
--  and
group by sd.section_id
  ,Bm.Art,sd.size
  ,sec.fld_barcode
  ,sec.fld_sectionname
  ,pro_barcode,Color
having sum(sd.pro_Inqty)-Sum(sd.pro_outqty) > 0
Hope it helps!
Apurva Singh8y ago
Thanx Sir..!!
Nitin Sontakke8y ago
Furthermore, always use dates in yyyy-MM-dd format in your sql queries. Now I have no idea if your date is 11-Jul or07-Nov.

Same happens with SQL Server too. If your server is in India with Indian regional settings, it will be seen as 11-Jul, however, if your database is moved to server in US (or US regional settings) it will be 07-Nov, and vice-versa.

Servers get changed very easily wherever cheapest hosting is available. With the introduction of cloud that is even more probable.

So stick to internationally neutral date format so your query never fails when regional settings are changed.
Packiaraj Santhiyagu8y ago
It would be grate if share script of tables and data.