Skip to main content

Notifications

Microsoft Dynamics NAV (Archived)

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

(0) ShareShare
ReportReport
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.

*This post is locked for comments

  • 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,868 Super User 2024 Season 1 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,868 Super User 2024 Season 1 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,868 Super User 2024 Season 1 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

News and Announcements

Announcing Category Subscriptions!

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,359 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans