SQLTeam.com | Weblogs | Forums

Update stock following Batch


#1

I design a new application.
I use sQL server. So i have a prolem. I only want to show records that have "i.Quality - isnull(e.Quality,0) as Quality">0.
And picture here:
"http://www.mediafire.com/view/l5fyiaa8flolgda/Batch_tong_2.JPG"
This is my code from another help me.
select i.[No]
,i.Code
,i.Name
,i.Quality - isnull(e.Quality,0) as Quality
,i.Batch
from tbImport i
left join (select Code
,Name
,sum(Quality) as Quality
,Batch
from tbExport
group by Code
,Name
,Batch
) e
on(i.Code = e.Code
and i.Name = e.Name
and i.Batch = e.Batch
)Where i.quality > e.Quality.
it is not working
Please help me.


#2

You don't say why it is not working .... perhaps you need to do this?

Where i.quality > e.Quality
      OR e.Quality IS NULL

#3

Thanks for helping. I want this code
"i.Quality - isnull(e.Quality,0) as Quality" that as Quality >0.
It will be show.

See at the picture. In table tbStock. the first Record that material name= "A001" have quality =0. it not display in select.


#4

Well you can use that in the WHERE clause

Where i.Quality - isnull(e.Quality,0) > 0

but the use of the Function will make the query non-SARGable and thus inefficient. I think my alternative suggestion has the same outcome (provided that when e.Quality is NULL that i.Quality is never negative)

Afraid pictures not useful in this forum. Folk here like to be able to cut & paste from data etc. What we like is example data posted as a CREATE TempTable and some INSERT statements, and then we can test our answers rather than guessing if they might be right / contain errors etc.


#5

Thanks so much. I thinks that i work well. I hope you have a good day.