select sql query problem

[rajkrishna]

I hope you can help me.

I am trying to generate a report using 3 tables - sttakehm, sttakedm and stquem.

I wrote below query but it is giving all values of unit_cost, I only need the unit cost which matches stquem.sequence _number=sttaked m.queue_referen ce and adjustment quantit.

The key is sequence number is in one line with Kind = "C" and adjustment quanity is on second line with Kind = "B".

I need this pretty urgent, I hope you can help me

SELECT sttakedm.card_n umber, sttakedm.produc t_code, sttakedm.adjust ment_quantit, sttakedm.expect ed_quantity, sttakehm.wareho use, stquem.unit_cos t, sttakehm.descri ption1
FROM scheme.sttakedm sttakedm

INNER JOIN scheme.sttakehm on sttakehm.card_n umber = sttakedm.card_n umber
LEFT OUTER JOIN scheme.stquem on sttakehm.wareho use=stquem.ware house

WHERE sttakehm.card_n umber = sttakedm.card_n umber AND sttakedm.card_n umber='C03323' AND (stquem.sequenc e_number=sttake dm.queue_refere nce OR (sttakedm.kind = 'B' OR sttakedm.kind = 'C')) AND sttakedm.produc t_code = stquem.prod_cod e AND sttakehm.wareho use=stquem.ware house

[Rabbit]

Sample data and results would help. I have trouble understanding what you're asking.

[rajkrishna]

Sample data and results would help. I have trouble understanding what you're asking.

Hi The result is coming like this:
Stock Variance Template

card_number description1 warehouse product_code adjustment_quan tit expected_quanti ty unit_cost
C03323 STOCK TAKE 01 DEC 2011 FG 0015 0 0 16.9774
C03323 STOCK TAKE 01 DEC 2011 FG 0015 0 144 16.8842
C03323 STOCK TAKE 01 DEC 2011 FG 0015 0 144 16.8968
C03323 STOCK TAKE 01 DEC 2011 FG 0015 0 144 16.9774

Sample data from sttakedm table is:

card_number kind sequence_number queue_reference product_code bin_location status batch_code lot_number adjustment_quan tit expected_quanti ty
C03323 C 000005 P3kKh! 0030 0 0
C03323 B 000005 0030 A100 -35 107
C03323 S 000005 0030 A100 0 0
And sample data from stquem table is:

warehouse product sequence_number prod_code serial_number batch_number date_received bin_number lot_number expiry_date passed_inspecti on inspector_code inspection_date source_code conformity_ref quantity quantity_free unit_cost
FG 0030 P.*qc! 0030 P15506 29/09/2011 0:00 A100 5193 Y 016593 0 0 38.9062
0 32.0154
FG 0030 MA##G! 0030 P00444 11/02/2005 0:00 A100 000493/001 Y 000493 0 0 32.0154
FG 0030 MAhy`! 0030 P00491 18/02/2005 0:00 A100 000551/001 Y 000551 0 0 32.0154
FG 0030 NgOaL! 0030 P09738 19/06/2008 0:00 A100 010345/001 Y 010345 0 0 32.0154
FG 0030 Nii>A! 0030 P09896 10/07/2008 0:00 A100 010508/001 Y 010508 0 0 32.0154

table sttakehm is only used to create card number and for which warehouse. There is no data from that table. It is used only to link card number, warehouse and description.

I hope this helps.

[rajkrishna]

[QUOTE=Rabbit;36 90606]Sample data and results would help. I have trouble understanding what you're asking.[/

[rajkrishna]

Sample data and results would help. I have trouble understanding what you're asking.

HI

I have put excel data in zip file.Hope this helps

[Rabbit]

I see the sample data but I don't see the results.

[rajkrishna]

I see the sample data but I don't see the results.

Hi

I have attached Book3 zip file. In that file tabs stock variance templates are results and tabs-stquem and sttakedm are sample data from tables.

[Rabbit]

Can you paste the results in a post? I prefer not to download files from unknown sources.

[rajkrishna]

Hi

Here is the Result:
card_number description1 warehouse product_code adjustment_quan tit expected_quanti ty unit_cost
C03323 STOCK TAKE 01 DEC 2011 FG 0015 0 0 16.9774
C03323 STOCK TAKE 01 DEC 2011 FG 0015 0 144 16.8842
C03323 STOCK TAKE 01 DEC 2011 FG 0015 0 144 16.8968
C03323 STOCK TAKE 01 DEC 2011 FG 0015 0 144 16.9774

[rajkrishna]

In the above result for
Card_number is : Card03323
description1: STOCK TAKE 01 DEC 2011
warehouse: FG
product_code: 0015
adjustment_quan tit: 0
expected_quanti ty: 144
unit_cost: different batch cost - 16.9774, 16.8842, 16.8968 & 16.9774

Hope this explain. I need a query which give me one unit_cost and one expected_quanti ty & one adjustment_quan tit for one product_code

[Rabbit]

And how do you know which adjustment quantity and product code to take? If there's multiple and you only want one, you need an algorithm to choose one.