Question:
How do I get the Valor Unit of the last purchase in Crystal Reports in a resulting table like the following?
+-------------+---------------+------+------------+---------------+
| CodItem | DataCompra | Qtde | ValorUnit | ValorTotal |
+-------------+---------------+------+------------+---------------+
| Cod1 | 17/01/15 | 25 | 10,00 | 250,00 |
| Cod1 | 09/01/15 | 35 | 20,00 | 700,00 |
| Cod1 | 11/01/15 | 50 | 5,00 | 250,00 |
| Cod1 | 22/01/15 | 30 | 10,00 | 300,00 |
| Cod2 | 19/01/15 | 10 | 5,00 | 50,00 |
| Cod2 | 15/01/15 | 15 | 10,00 | 150,00 |
+-------------+---------------+------+------------+---------------+
The resulting table SQL is as follows:
SELECT CodItem
,DataCompra
,'Qtde' = SUM(Qtde)
,ValorUnit
,ValorTotal
FROM...
WHERE DataCompra BETWEEN '2015-01-01' AND '2015-01-31'
GROUP BY CodItem
,DataCompra
,ValorUnit
,ValorTotal
And I would like my result like this:
+-------------+---------------+------+------------+---------------+---------------+
| CodItem | DataCompra | Qtde | ValorUnit | ValorTotal | UltimoPreco |
+-------------+---------------+------+------------+---------------+---------------+
| Cod1 | 17/01/15 | 25 | 10,00 | 250,00 | 10,00 |
| Cod1 | 09/01/15 | 35 | 20,00 | 700,00 | 10,00 |
| Cod1 | 11/01/15 | 50 | 5,00 | 250,00 | 10,00 |
| Cod1 | 22/01/15 | 30 | 10,00 | 300,00 | 10,00 |
| Cod2 | 19/01/15 | 10 | 5,00 | 50,00 | 5,00 |
| Cod2 | 15/01/15 | 15 | 10,00 | 150,00 | 5,00 |
+-------------+---------------+------+------------+---------------+---------------+
Preferably (and only if possible) I would like to know how to do this in Crystal Reports. But in SQL it would also help me a lot.
Answer:
Redoing my answer , I think I misunderstood before
What can be done in sql is use a subselect
SELECT CodItem ,DataCompra ,'Qtde' = SUM(Qtde) ,ValorUnit ,ValorTotal
FROM tabela t1
WHERE DataCompra BETWEEN '2015-01-01' AND '2015-01-31'
And DataCompra = (SELECT MAX(DataCompra)
From tabela t2
Where t2.CodItem = t1.CodItem)
GROUP BY CodItem ,DataCompra ,ValorUnit ,ValorTotal
In other words, the subselect only brings the highest purchase data for a "CodItem".