I am struggling with the partition part of my query in SQL. I am querying a table that has multiple entries for the same asset being the assets itemno. The table contains all instances where the asset has been placed on a hire contract together with status, date of hire, dispatch document number etc.
I am trying to partition the table so that I get the latest status of the item in order to determine if the asset is available for hire or already on-hire or allocated. Everything works fine except where the item has an allocated status. When allocated the item does not yet have a dispatch document being "DOCNO#2".
I want to partition so that I get the latest status in the table but unfortunately due to bad data I can't use the hiredate only: too many duplicates. What I want to do is check whether the status is allocated and if so partition by DOCNO#2 but ASC so that the 0 value assigned to this field for an allocated asset is ranked 1st and if no allocated status for the item exists in the table I want to order by DOCNO#2 DESC, HIREDATE DESC, DOCDATE#5 ASC.
Here is my code but I am getting duplicates for the same item where the item has an allocated status in the table. See the data below. Same item is shown for Status allocated as well as for the Off-hired status with the DOCNO#2 = 6203.
How do I return only one of the partition results?
WHEN ContractItemStatus ='Allocated'
THEN (row_number() over (partition by ITEMNO ORDER BY DOCNO#2 ASC))
ELSE (row_number() over (partition by ITEMNO ORDER BY DOCNO#2 DESC, HIREDATE DESC, DOCDATE#5 ASC))
END AS Seq
ITEMNO SID STATUSNAME HIREDATE DOCNO#2 DOCDATE#5 CONTNO
921THS1498 9/06/2015 14:59 Off-Hired 5/05/2015 6203 8/06/2015 11334
921THS1498 12/09/2014 9:12 Off-Hired 9/09/2014 4928 12/09/2014 11039
921THS1498 28/08/2014 9:05 Off-Hired 8/06/2014 4380 21/08/2014 10946
921THS1498 9/05/2014 11:19 Off-Hired 29/08/2013 2946 4/05/2014 10573
921THS1498 27/09/2013 12:33 Off-Hired 1/03/2013 1589 28/08/2013 10300
921THS1498 21/03/2013 19:35 Off-Hired 29/10/2012 389 28/02/2013 265
921THS1498 10/07/2015 12:02 Allocated 2/09/2015 0 1899-12-30 11421