Dec 22, 2010

RANK() and alternate Sub Query in T-SQL

Consider, we have data table called Orders_Header which hold order header details.
Table
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.
output
Normally we can write a sub query for this and get the output.
SELECT PONo,
(
SELECT COUNT(PONo)+1 -- to omit zero
FROM ORDERS_Header WHERE PONo=oh.PONo AND DownloadDate< oh.DownloadDate
) AS Revision,
DownloadDate
FROM ORDERS_Header oh
ORDER BY PONO,DownloadDate

By running this query, you can see the following Actual Execution Plan.

Sub_plan



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


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



Check its Actual Execution Plan and note the difference.

Rank_plan

Dec 1, 2010

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. Daniel Rodrigo Olea

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,

  1. MAK tool
  2. Creating/modifying Movex Program
  3. Creating/modifying View Definition
  4. Creating/modifying M3 API
  5. Managing Out Interface
  6. Data tables, Data Structures, CL in M3
  7. Lawson web services to interact with M3 programs and APIs.
  8. and much more…