Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics NAV forum
Suggested answer

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

Posted on by 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.

  • manish.yadav Profile Picture
    manish.yadav 1,835 on at
    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 Profile Picture
    TeddyH 12,861 Moderator on at
    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 Profile Picture
    manish.yadav 1,835 on at
    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 Profile Picture
    TeddyH 12,861 Moderator on at
    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 Profile Picture
    manish.yadav 1,835 on at
    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".

    ABC.png

    Please help on these critical issue.

    Your help will be greatly appreciated.

  • Suggested answer
    TeddyH Profile Picture
    TeddyH 12,861 Moderator on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,558 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,645 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans