GRN-QTY -query problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

GRN-QTY -query problem

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2009-12-01 : 12:33:27

Hi,

i want to show the actualy quantity and hand after production issue from table , here i wrote a query for that but its not showing grn qty which iam not issuing for production.

SELECT HEATNO,flditemdesc,(isnull(a.fldgrnqty -(select sum(qty) from tblgrnprdissuedetail I where I.heatno=b.heatno),0)) as receivedqty from tblblgrndetail a inner join tblblgrnMaster b on a.fldblgrno = b.fldblgrno
left join tblstockitem S on s.flditemcode = a.flditemcode

Desikankannan

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-01 : 12:57:21

Not sure I understand your question completely...

Are you not getting results when a product is not in production? By that do you mean the product does not exist in tblblgrnMaster b? If so you will nto see any results because of the INNER JOIN.

Is there any particular reason to use a LEFT JOIN on fields that are not included at all in your queries? (although I dont think it hurts anything...)

Lastly in the subquery does it help at all to change "I.Heathno = b.Heatno" to "I.heatno = a.Heatno".

Sorry I'm not positive what the initial problem or question is... :(

SELECT HEATNO,flditemdesc,(isnull(a.fldgrnqty -(select sum(qty) from tblgrnprdissuedetail I where I.heatno=b.heatno),0)) as receivedqty from tblblgrndetail a inner join tblblgrnMaster b on a.fldblgrno = b.fldblgrno
left join tblstockitem S on s.flditemcode = a.flditemcode

desikankannan
Posting Yak Master

152 Posts

Posted - 2009-12-01 : 22:52:53

yes iam not clearly mentioned what i want, see for example from xx grn 2000kg
iam using to production from that xx grn 200kg

then i should show the balance from that xx grn =1800kg
if iam not issuing any qty for production for that xx grn its should
show in the list has grn qty on hand is 2000 kg

Desikankannan

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-02 : 09:18:31

Hows this?

SELECT
HEATNO,flditemdesc,a.fldgrnqty - isnull(pr.usedqty,0) as recievedqty from tblblgrndetail a
inner join tblblgrnMaster b on a.fldblgrno = b.fldblgrno
LEFT OUTER JOIN (select heatno, sum(qty) as usedqty from tblgrnprdissuedetail) pr on a.heatno=pr.heatno
left join tblstockitem S on s.flditemcode = a.flditemcode

EDIT: In your original it may just be that you have the ISNULL() function incasing too much, since if the QTY is null you have the function going to 0. You may be able to change it to:

ISNULL(a.fldgrnqty - (Select....), a.fldgrnqty)

So that you return the original qty instead of 0.

desikankannan
Posting Yak Master

152 Posts

Posted - 2009-12-03 : 02:17:05

working well thanks for ur guidence

Desikankannan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources