Case When Partition Statement

Transact-SQL
1

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?

(Select case
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

2

I was not able to follow your explanation of the requirement, so the code below is based just on looking at the code fragment you posted.

SELECT
	ROW_NUMBER() OVER 
		(PARTITION BY ItemNo ORDER BY 
			DOCNO#2, 
			CASE WHEN ContractItemStatus ='Allocated' THEN HIREDATE END DESC,
			CASE WHEN ContractItemStatus ='Allocated' THEN DOCDATE#5 END ASC 
		)

If that works as expected, by sense is that you wouldn't need the case expressions, a simple order by clause such as the one shown below should give the same results

SELECT
	ROW_NUMBER() OVER 
		(PARTITION BY ItemNo ORDER BY 
			DOCNO#2, HIREDATE DESC ,DOCDATE#5 ASC
		)
3

I can't use the hiredate only: too many duplicates.

hiredate seems to include time: the time is duplicated also? Or are you ignoring the time ... if so, don't do that :smile:

4

Hi Scott

The hiredate only has the date- no time. Docdate #5 has the time but I can't use that as this is the return date of the asset which if not yet returned is populated with the 1899-12-30 etc date and time.

5

Hi James

The issue with this is that you have 7 different statuses for the item in one table. One of them is "Allocated" so it will be sorted as per the he first case and then the other 6 entries will be sorted on the second case statement. This will return two Seq 1 values and when joined will duplicate the itemno.

I need to somehow state that if a specific itemno has a status of allocated irrespective of the other statuses it may have in the table the complete set of entries for that itemno should be sorted by DOCNO#2 ASC and if no status allocated exists for an itemno in the table it should be sorted by DOCNO#2 DESC, HIREDATE DESC DOCDATE#5 DASC. So I need a check first to see if an itemno has an allocated status within the table. Can that be built into the partition?

Thanks

6

Hi again Scott,

I just checked in the database and yes you are right the hiredate is set up with date and time but unfortunately the time is not captured. All entries show 00:00:00.000!

7

I must admit that I didn't understand the logic you are trying to implement. A set of representative sample data and the corresponding expected output would be most helpful.