Showing posts from December, 2010

RANK() and alternate Sub Query in T-SQL

Consider, we have data table called Orders_Header which hold order header details.

As you can see, PO number(PONo)  ‘0050426995’ is duplicating. Because this table containing PO amendments too. If you notice, DownloadData is in order, meaning first version of PO received on 2010-06-26 17:07:04.910, rest of them subsequently.
The requirement is we want to get following output.

Normally we can write a sub query for this and get the output.
SELECT PONo,(SELECTCOUNT(PONo)+1 -- to omit zeroFROM ORDERS_Header WHERE PONo=oh.PONo AND DownloadDate< oh.DownloadDate) AS Revision,DownloadDateFROM ORDERS_Header ohORDERBY PONO,DownloadDate
By running this query, you can see the following Actual Execution Plan.

From MS-SQL 2005 onwards this can be easily achieved by RANK() function. 

SElECT PONo, RANK()OVER(PARTITION BY PONo ORDERBY DownloadDate) AS Revision ,DownloadDate FROM dbo.ORDERS_HeaderORDERBY PONO,DownloadDate

Check its Actual Execution Plan and note the difference.

Movex Tech Training from Lawson

My current employer (Brandix Lanka) arranged a MoveX technical training from Lawson, our ERP vendor. It took 12 days and today is the last day. Mr. Daniel Rodrigo Olea , EMEA Technical Project Manager of Lawson software is the presenter and delivered marvelous sessions to our team. Very first day, we were totally blank of what he is talking about. But he guided us in a way that we were able to understand almost everything in Movex code. Areas that we covered in the training,MAK tool Creating/modifying Movex Program Creating/modifying View Definition Creating/modifying M3 API Managing Out Interface Data tables, Data Structures, CL in M3 Lawson web services to interact with M3 programs and APIs. and much more…View Full Album