-1

Hello experts below is my code: I have different tables where I have the data. One material MYCODE is compound of different raw materials like 01-, 04- and 05-. For each of them I should find out the last date (max date of purchase) which is max(tp_date). below is my code: but the output is showing 2 different dates for the first material 01-. I need just the last one, so the line with the order date 2015-04-22 please help:

select dmprod.pr_codenum pr_co, dmprod.pr_descrip desc, 
max(dttpur.tp_date) Date, dmfcur.fc_name CURR,
(select pr_codenum from 
dmprod where dmprod.pr_id=dmbom.bo_bomfor)BOM, 
max(dmrev.re_name) Rev, 
sum(dmbom.bo_quant)*dtpur.pu_price Ext, 
dmunit.un_name mea, 
dtpur.pu_price price from dmbom, dmprod, 
dmrev, dmunit, dttpur, dmfcur, dtpur  
where dmbom.bo_prid=dmprod.pr_id 
and dttpur.tp_fcid=dmfcur.fc_id
and dmprod.pr_id=dtpur.pu_prid and dttpur.tp_id=dtpur.pu_tpid
and dmbom.bo_unid=dmunit.un_id 
and dmrev.re_id=dmbom.bo_reid 
and pr_active=1 and (select pr_codenum from dmprod where dmprod.pr_id=dmbom.bo_bomfor)='MYCODE' 
group by pr_codenum, pr_descrip, fc_name,un_name,
pu_price, bo_bomfor

and the output is:

pr_co   des  Date       CURR    BOM    Rev  Ext      mea    price
01-       T  2015-04-22  USD    MYCODE  01   127     mg      2
01-       T  2015-02-26  USD    MYCODE  01   290     mg      4
04-       M  2016-01-12  EUR    MYCODE  01   300     mg      7
05-       S  2016-02-25  EUR    MYCODE  01   260     mg      4
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
Deni
  • 11
  • 2

1 Answers1

0

Notice the different prices in the last column. You are including pu_price in your GROUP BY clause, so it's returning different lines for each different price. If you want to group all the 01- materials on the same line, regardless of price, then just leave pu_price out of your GROUP BY clause.

MarkNFI
  • 556
  • 2
  • 9
  • how ? if the pu_price is needed to show in the select – Deni Mar 11 '16 at 20:15
  • If you need to group all the 01-materials on the same line, regardless of price, then there's no logical way to show multiple prices in one line. First decide what you want to show, then maybe we can help you figure out how to show it. – Ross Presser Mar 11 '16 at 20:36
  • I re-read your question, and I see you want to use the price from the most recent date. I don't know sql-server well enough to give a reliable answer, but I would start by removing pu_price from the GROUP BY, and then do a sub-query to get the corresponding price for your most recent date. Unfortunately I don't know how to query sql-server for the matching price, but I think that's the question you need to ask here. (Sorry, I misread your question on the first read-thru, otherwise I would have posted my answer as just a comment.) – MarkNFI Mar 11 '16 at 21:00
  • Hi Ross, I need to show the max (date) and also the price of it, ext .. so everything that is in the Select part. I know if I remove the price it will be shown in one line, but how to do the subquery for the matching price. the price is also need for the EXT column also (sum(bo_quant)*pu_price) – Deni Mar 11 '16 at 21:21
  • Hi experts, Do you have any answer for my question above. thanks – Deni Mar 14 '16 at 20:51