Value based on another field (Crystal Reports)

Question

Value based on another field (Crystal Reports)

Navigation

#1 by (0 votes)
#2 by (0 votes)

3

How do I get the Valor Unit of the last purchase in Crystal Reports on 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's 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 my result would look 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) would like to know how to do this in Crystal Reports. But in SQL too much would help me.

visual-studio-2013 crystal-reports

asked by anonymous 19.02.2015 / 19:27

2 answers

How to set a default option in select with data-live-search HTML? VIewModel relationships with EF

score 0 · Answer 1

Remaining my answer, I think I misunderstood before

What can be done in sql is to 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 largest purchase of a "CodItem".

score 0 · Answer 2

Sub-select LastCreco for CodItem :

SELECT CodItem, DataCompra, 'Qtde' = SUM (Qtde), ValueUnit, ValueTotal, 'LastCreco' = (SELECT TOP 1 S.ValorUnit FROM SUATABELA S WHERE S.CodItem = CodItem ORDER BY DataCompra) FROM SUATABELA WHERE DataCompra BETWEEN '2015-01-01' AND '2015-01-31' GROUP BY CodItem, DataCompra, ValueUnit, ValueTotal