Dont display Zero Qty - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dont display Zero Qty

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-12-16 : 02:39:29

Guys,

Here is my requirements.
I need only to display records with positive qty.
what should i place to my condition in my script
get my requirements. requirements.

Sample data.

TABLE1
ItemID--Qty--Value
--------------------
111111-- 14---32.00
111111-- -14---32.00
--------------------
222222-- 20---40.00
222222-- 15---40.00

Result:

TABLE1
ItemID--Qty--Value
-------------------
222222-- 35---80.00

Select
Itemd,
SUM(qty) as Qty, ??"I would like to display the positive value of (SUM(QTY) "
SUM(value)
From table1
??What condition should be add into this part

Thanks.

JOV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 03:12:32

[code]
Select
Itemd,
SUM(qty) as Qty,
SUM(value) as value
From table1
GROUP BY Itemd
HAVING SUM(qty) >0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-12-16 : 03:37:44

Thanks visakh..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 06:23:36

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-12-19 : 03:49:01

Hi Visakh,

I have 3 tables, but when i check the records i have no records in i.INVENTSUM but i found records from it.INVETTABLE & loc.INVETDIM. My concerned is why I still capture the data even the value of "having sum(i.PHYSICALINVENT) > 0" i think this condition is not satisfy.

I notice also that even the i.PHYSICALINVENT is 0 the sum(i.PHYSICALINVENT) as Qty has display value.

1.i.INVENTSUM ---
2.it.INVENTTABLE ---
3.loc.INVENTDIM---

select
it.ASUMODEL,
left(it.itemid,7) as ItemID,
it.ITEMNAME,
it.ITEMID as SKU,
sum(i.PHYSICALINVENT) as Qty,
sum(i.POSTEDVALUE) as InventoryValue,
ITEMGROUPID,
loc.WMSLOCATIONID,
loc.INVENTLOCATIONID,
i.INVENTDIMID
from INVENTSUM i
inner join INVENTTABLE it
on i.itemid = it.itemid
and it.dataareaid = 'red'
-- and sum(i.PHYSICALINVENT) > 0
inner join INVENTDIM loc
on loc.INVENTDIMID = i.INVENTDIMID
and loc.dataareaid = i.dataareaid
where i.dataareaid in ('blue')
and it.ASUMODEL in ('Cosmos')
and it.ITEMGROUPID not in ('Scrap')
group by
it.ASUMODEL,
it.ITEMNAME,
it.ITEMID,
left(it.itemid,7),
it.ITEMGROUPID,
loc.WMSLOCATIONID,
loc.INVENTLOCATIONID,
i.INVENTDIMID
Having sum(i.PHYSICALINVENT) > 0
order by
it.ASUMODEL,
it.ITEMID

Regards,

JOV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 04:29:19

please post some sample data to explain your problem. Cant make out what exactly is problem you're facing from the decsription above.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-12-19 : 04:45:52

Here is Sample Table & Data

Inventtable
ItemID--DataareaID--Model
---------------------------
LGE2257----red------VX10000
LGE2212----red------VX10000
LGE2260----red------VX900

Inventsum
Itemid--Postedvalue-PhysicalInvent-Invetdimid--Dataareaid
---------------------------------------------------------
LGE2257- 140.00---- 14.00------DIM0000725 ----blue
LGE2257- -139.43---- -14.00------DIM0017562 ----blue
LGE2260- 100.00---- 45.00------DIM0012345 ----blue
this is negative 14 and negative 139.43

Inventdim
----------------------------------------
Inventdimid--WMSlocationid--Dataaeraid--
----------------------------------------
DIM0000725 -- TRC-dock ----blue--------
DIM0017562 -- TRC-dock ----blue--------
DIM0012345 -- Parts ----blue--------

1. when I sum up the Physicalinvent to value should be 0 but somtimes it appears 14.00
2. there is records in inventtable but no records both from inventsum and invetdim but it appears in the query.
3. if the value of sum(PhysicalInvent) is zero this sould not be in the query result.

the result should be the LGE2260..the other itemid is not included.

Thanks.

JOV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 05:13:12

what you're telling doesnt make sense with query posted
you're using inner join with inventsum and invetdim so under no condition it will return records in inventtable but no records both from inventsum and invetdim which contradicts what you're specifying in point 2 above

if the value of sum(PhysicalInvent) is zero this sould not be in the query result - again you've already incuded the condition in your HAVING as below


...
group by 
it.ASUMODEL,
it.ITEMNAME,
it.ITEMID,
left(it.itemid,7),
it.ITEMGROUPID,
loc.WMSLOCATIONID,
loc.INVENTLOCATIONID,
i.INVENTDIMID
Having sum(i.PHYSICALINVENT) > 0 
..

so unless you've multiple locations per invent table entry this should also work fine.
I'm sure you're either missing something or are using some other query of which above forms just a part and might not have given us full picture to understand real problem.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-12-19 : 21:38:01

Hi Visakh,

I need to check thoroughly the records then i will post the problem. Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-19 : 22:16:53

quote:
Originally posted by visakh16

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Is that 50%?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources