How to Deleting equal number of records with positive and negative values available in table.

(0) Share

Report

Posted on by manish.yadav

1,835

Dear Experts,

I have write the SQL query to show the total no. of quantity available in bin content from SQL server. Here I don't want to show these serial or lot no where have not positive values. I want to show data only serial/lot have only positive values. In my query bot are coming, please help me to rectify it.

My Query is below:

select distinct x.[Item No_],[UAT Private Limited$Item].Description,x.[Variant Code], SUM(x.[Qty_ (Base)]) "Quantity",x.[Serial No_],x.[Lot No_],y.No_,y.[Registering Date] from [UAT Private Limited$Warehouse Entry] x
left outer join [UAT Private Limited$Item] on [UAT Private Limited$Item].No_ = x.[Item No_]
left outer join [UAT Private Limited$Registered Whse_ Activity Hdr_] y on x.[Reference No_] = y.No_
where x.[Bin Code]='101-RG1-N001' and x.[Location Code] = '101' and x.[Zone Code] = '101-RG1'
group by x.[Item No_],x.[Variant Code],[UAT Private Limited$Item].Description,x.[Serial No_],x.[Lot No_],y.No_,y.[Registering Date]
having sum([Qty_ (Base)])<>0

I will be thankful if someone can give resolution on these.

*This post is locked for comments

All responses (6)

Answers (0)

manish.yadav 1,835 on at

Like (0)

Report

RE: How to Deleting equal number of records with positive and negative values available in table.

yes sir, I have matched with warehouse entries
Suggested answer

TeddyH 12,868 Super User 2024 Season 1 on at

Like (0)

Report

RE: How to Deleting equal number of records with positive and negative values available in table.

Did you look at Quantity or Qty. (Base) in Bin Content ? Quantity is based on the Bin Unit of Measure.
manish.yadav 1,835 on at

Like (0)

Report

RE: How to Deleting equal number of records with positive and negative values available in table.

Hi Teddy,

I appreciate your response. Unfortunately, this didn't solve our problem. Report data is not matched with data available in "Bin Content".
Suggested answer

TeddyH 12,868 Super User 2024 Season 1 on at

Like (0)

Report

RE: How to Deleting equal number of records with positive and negative values available in table.

select x.[Item No_],[UAT Private Limited$Item].Description,x.[Variant Code], SUM(x.[Qty_ (Base)]) "Quantity",x.[Serial No_],x.[Lot No_],x.[Registering Date] from [UAT Private Limited$Warehouse Entry] x

left outer join [UAT Private Limited$Item] on [UAT Private Limited$Item].No_ = x.[Item No_]

group by x.[Item No_],x.[Variant Code],[UAT Private Limited$Item].Description,x.[Serial No_],x.[Lot No_],x.[Registering Date]

having sum([Qty_ (Base)])<>0
manish.yadav 1,835 on at

Like (0)

Report

RE: How to Deleting equal number of records with positive and negative values available in table.

Sir,

I have added this field for get the correct "Posting Date".

Please help on these critical issue.

Your help will be greatly appreciated.
Suggested answer

TeddyH 12,868 Super User 2024 Season 1 on at

Like (0)

Report

RE: How to Deleting equal number of records with positive and negative values available in table.

Why looking at Registered Whse Activity Header ?

Just sum the Warehouse Entry quantity.