How to neglect lowest value in my o/p?

Question

How to neglect lowest value in my o/p?

raghuldrag

Hall of Fame

Points: 3160
More actions
January 21, 2014 at 12:51 am

#281270

I ve the table like
Billed:
------
bill references product qty
25 - 6000350 25
25 6000350 5000320 35
25 6000350 5000312 05
expecting o/p:
----------------
Bill references product sales free
25 6000350 5000320 25 10
25 6000350 5000312 0 05
so i wrote the code
select
a.bill,
a.references,
a.product,
a.qty as sales
b.qty - a.qty as free
from
Billed a
inner join
Billed b
on
a.bill = b.bill
where
a.references=b.product
group by
a.bill,
a.references,
a.product,
a.qty,
b.qty
its givin o/p:
------------
Bill references product sales free
25 6000350 5000320 25 10
25 6000350 5000312 25 -20
i wanna remove the 5000312 product showing "-" value from my product
how to make a code.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

As a frequent visitor to ssc you already know that you are more likely to get a tested and accurate solution if you provide sample data in a ready-to-consume format. Not only does it help us to help you, it also gives you the opportunity to test your own code. I've turned your source "table" into a script for you using a simple CTE as a demonstration:

-- sample data

;WITH Billed (bill, [references], product, qty) AS (

SELECT 25, 6000350, NULL, 25 UNION ALL

SELECT 25, 6000350, 5000320, 35 UNION ALL

SELECT 25, 6000350, 5000312, 05)

SELECT * FROM Billed

Is it what you are expecting?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden