Date Filter

Transact-SQL
1

Hi, I have a sql 2008 r2 view that shows all the purchases of all the items on stock, my problem is that on a PO can be three lines of an item, I need to select just the last of every item on every PO, Example :

PO       |   LotNr   |    LotDate     |    ItemCode   |   Price     |

50002334| 29350 | 2014-10-24 | Code-01 | 169.820 |--line must be deleted same po,same lot and same item
50002334| 29350 | 2014-10-30 | Code-01 | 161.380 |
50002334| 29350 | 2014-10-24 | Code-02 | 95.20 |
50004027| 30252 | 2014-11-02 | Code-01 | 169.820 |
50004027| 30252 | 2014-11-02 | Code-03 | 75.20 |

I need something like this :

 PO       |   LotNr   |    LotDate     |    ItemCode   |   Price     |

50002334| 29350 | 2014-10-30 | Code-01 | 161.380 |
50002334| 29350 | 2014-10-24 | Code-02 | 95.20 |
50004027| 30252 | 2014-11-02 | Code-01 | 169.820 |
50004027| 30252 | 2014-11-02 | Code-03 | 75.20 |

this is my actual code :

SELECT TOP (100) PERCENT LotNumber AS LotNr, datum AS LotDate, warehouse_location AS Location, Type, oms25 AS Note, artcode AS ItemCode, AvgPrice AS PPriceMX, QtyIn AS Qty,
bdr_val AS StockValue, valcode AS Cur, bkstnr_sub AS PO, LTRIM(RTRIM(crdnr)) AS SupplierNr
FROM dbo.ViewVK_StockPositions1
WHERE (Type = 'A') AND (artcode = '8-IRROMPIBLE') AND (LotNumber = '29350')
ORDER BY ItemCode

Thanks in advance for your suggestions.

2

row_number to the rescue:

with cte
  as (select *   /* here you should specify the fields you need instead of selecting all */
            ,row_number() over(partition by bkstnr
                                           ,lotnumber
                                           ,artcode
                               order by datum desc
                              )
             as rn
        from dbo.viewvk_stockpositions1
       where [type]='A'
         and artcode='8-IRROMPIBLE'
         and lotnumber='29350'
     )
select *   /* here you should specify the fields you need instead of selecting all */
  from cte
 where rn=1
;
1 Like
3

It works perfect, the row_number turns Me crazy, its to much for me but with Your help finally works, now I'll be trying to understand it.

Thank You so much.

4

Hi bitsmed,

I found some problem with the code, its running ok but when I reopen it the code is not saved, it isn't retaining the changes, i enter it again and save it but the same case, the view lost the changes and I dont know why can You help Me again?

Thanks in advance

5

Please show us the whole code, when creating your view.
Also show us the error text you receive.

6

I'm not receiving an error, the code it's saving ok but when I reopen it appairs without the changes previously saved.

the code is this :

WITH CTE
AS (SELECT LotNr, LotDate, Location,Type, Note, ItemCode, PPriceMX, Qty, StockValue, Cur, PO, SupplierNr, PODate, POCur, POXRate, ROW_NUMBER() OVER (PARTITION BY Type, LotNr, ItemCode
ORDER BY LotDate DESC) AS RN
FROM Dbo.ViewVK_StockPositions2
WHERE [Type]='A' )
SELECT LotNr, LotDate, Location,Type, Note, ItemCode, PPriceMX, Qty, StockValue, Cur, PO, SupplierNr, PODate, POCur, POXRate
FROM CTE
WHERE RN=1

7

This is not a view unless you wrap it in a "create view" or "alter view" statement.
Please describe the process when you "reopen".
Also from which program.

8

I'm using sql 2008 r2 and I'm trying to open it as view design

9

This is from within Microsoft SQL Server Management Studio, right?

Just to make sure (I know this way seems more difficult), let try this:

  • right-click on the view and choose:
  • now you edit the query and press F5
10

That ways the code is ok thank You