Value based on another field (Crystal Reports)

Asked

Viewed 192 times

3

How do I get the Valor Unit of the last purchase in Crystal Reports in a resulting table as 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 SQL of the resulting table 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 I would like so:

+-------------+---------------+------+------------+---------------+---------------+
|   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.

visual-studio-2013 crystal-reports

2 answers

0

Repeating 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, subselect only brings the highest purchase data of a "Coditem".

  • But this would result in a table with only the largest purchase dates, right? I need the result as a new Query column. I’ll edit the question and add how I need the result.

  • Use subselect as a column, just "move" the solution

  • https://technet.microsoft.com/en-us/library/hh213018(v=sql.110). aspx depending on the bd use an "analityc Function" can solve , know that Oracle and Sql Server have.

0

Do a sub-select to retrieve the Ultimopreco for Coditem:

SELECT CodItem ,DataCompra ,'Qtde' = SUM(Qtde) ,ValorUnit ,ValorTotal, 'UltimoPreco' = (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 ,Valorunit ,Total value

Browser other questions tagged visual-studio-2013 crystal-reports

You are not signed in. Login or sign up in order to post.