Link to home
Start Free TrialLog in
Avatar of Mr Knackered
Mr KnackeredFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Get Top 1 of a Multi Part Group based on Date

What is the best way in SQL (MSSQL 2012) to get the TOP 1 of a Group (based on more than one column) for a given date?

Below is an example table of transactions and I want to pass in a date and CustomerID and get the latest transaction for each CategoryID and LineID up to the date:

----------------------------------------------------------------------------------------------------------
TranDateTime, CustomerID, CategoryID, LineID, Value1, Value2, etc
----------------------------------------------------------------------------------------------------------
2015-06-28 15:25, 1000, 490, 10, 77.19, 12.19
2015-06-29 09:02, 1000, 490, 10, 33.85, 45.66

2015-03-05 10:15, 1000, 500, 10, 50.19, 77.18,
2015-05-28 10:17, 1000, 500, 10, 36.25, 91.12,
2015-06-22 11:25, 1000, 500, 10, 77.63, 88.36,

2015-06-01 10:15, 1000, 501, 11, 104.1, 1.19,
2015-07-01 14:06, 1000, 501, 11, 39.1, 21.44,

Lets say the date passed in is 2015-07-01 and CustomerID is 1000 what I want to have as the return values are:

----------------------------------------------------------------------------------------------------------
TranDateTime, CustomerID, CategoryID, LineID, Value1, Value2, etc
----------------------------------------------------------------------------------------------------------
2015-06-29 09:02, 1000, 490, 10, 33.85, 45.66
2015-06-22 11:25, 1000, 500, 10, 77.63, 88.36,
2015-07-01 14:06, 1000, 501, 11, 39.1, 21.44,

Thanks in advance for any assistance you can provide
Avatar of Randy Poole
Randy Poole
Flag of United States of America image

Assuming you have a primary key named id:
Select * from yourtable where id in
(Select Top 3 id from yourtable where CustomerID=1000 and TranDateTime<='2015-07-01' 
order by trandatetime desc)

Open in new window

Avatar of Jim Horn
>What is the best way in SQL (MSSQL 2012) to get the TOP 1 of a Group (based on more than one column) for a given date?
Use a windowing function like RANK, PARTITION BY whatever 'a Group' means (CustomerID and CategoryID in the below code example), ORDER BY 'a given date' DESC, then just grab everything with a rank of 1.

<total air code>
;with a as ( 
   SELECT TranDateTime, CustomerID, CategoryID, LineID, Value1, Value2, 
   RANK() OVER (PARTITION BY CustomerID, CategoryID ORDER BY TranDateTime DESC) as rank_order ) 
SELECT * from a WHERE rank_order = 1

Open in new window

;with a as (
   SELECT TranDateTime, CustomerID, CategoryID, LineID, Value1, Value2,
   Row_number() OVER (PARTITION BY CustomerID, LineID ORDER BY TranDateTime DESC) as rn )
SELECT * from a WHERE rn = 1

added , LineID  to Jim's solution. Also change rank to row_number.
I added CategoryID in an edit after the initial post.  
I can't tell in the sample data if LineID should also be edited as all CategoryID - LineID combinations are the same.
SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Jim,

Question: Would this be Rank or row number? I am fuzzy a bit about it.
Avatar of Mr Knackered

ASKER

So this would do the job then:

DECLARE @DATE DateTime
SET @DATE = CONVERT(DATETIME, '2015-09-25 00:00:00', 102)

;with a as (
   SELECT TransDate, CustomerID, CategoryID, LineID, Value1, Value2, RANK() OVER (PARTITION BY CustomerID, CateogryID, LineID ORDER BY TransDate DESC) as rank_order
   FROM Table
   WHERE CustomerID = 1000 AND
   (DATEDIFF(ss, @DATE, DATEADD(dd, DATEDIFF(dd, 0, TransDate), 0)) <= 0))  
SELECT * from a WHERE rank_order = 1
Correct on the CategoryID and LineID, they are different and not always the same so the grouping needs to be on CustomerID, CategoryID and LineID.