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=sttakedm.queue_reference 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_number, sttakedm.product_code, sttakedm.adjustment_quantit, sttakedm.expected_quantity, sttakehm.warehouse, stquem.unit_cost, sttakehm.description1
FROM scheme.sttakedm sttakedm

INNER JOIN scheme.sttakehm on sttakehm.card_number = sttakedm.card_number
LEFT OUTER JOIN scheme.stquem on sttakehm.warehouse=stquem.warehouse

WHERE sttakehm.card_number = sttakedm.card_number AND sttakedm.card_number='C03323' AND (stquem.sequence_number=sttakedm.queue_reference OR (sttakedm.kind = 'B' OR sttakedm.kind = 'C')) AND sttakedm.product_code = stquem.prod_code AND sttakehm.warehouse=stquem.warehouse

[Rabbit]

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

[rajkrishna]

@Rabbit
Hi The result is coming like this:
Stock Variance Template

card_number description1 warehouse product_code adjustment_quantit expected_quantity 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_quantit expected_quantity
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_inspection 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;3690606]Sample data and results would help. I have trouble understanding what you're asking.[/

[rajkrishna]

@Rabbit
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]

@Rabbit
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_quantit expected_quantity 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_quantit: 0
expected_quantity: 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_quantity & one adjustment_quantit 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.