I needed guidance with a question I am working on and I have no previous experience solving it.
Basically, this is what the data looks like:
Customer Nr Service ID Months left till service expiry
57833357 149080495 0
57624280 141110847 0
57885974 149080449 0
57885974 149080480 7
57885974 149080499 1
Scenario:
I am trying to figure out the MAX() of "Months left till service expiry" column, for each Customer Nr.
Requirement:
What I am basically looking for is, when picking the MAX() for each customer Nr, also pick out the respective service ID for the column that returns MAX(Months left till service expiry).
For instance, in the data above,customer nr =57885974 appears three times on different service ID:s. The service ID=149080480 has 7 months left till expiry and that is the MAX() for customer nr :57885974 .
How can make so it is only taking the Customer Nr, service ID( for the MAX() months left till service expiry) and months left till service expiry?
Example below:
Customer Nr Service ID Months left till service expiry
57833357 149080495 0
57624280 141110847 0
57885974 149080480 7
My code currently looks like this:
SELECT DISTINCT
Customer_nr,
[Months left till service expiry]=CASE WHEN DATEDIFF(DAY,Date,EndDate) BETWEEN 0 AND 30 THEN '1'
WHEN DATEDIFF(DAY,Date,EndDate) BETWEEN 31 AND 60 THEN '2'
ELSE SOMETHING END
FROM TABLE A
GROUP BY Customer_nr,
serviceID,
CASE WHEN DATEDIFF(DAY,Date,EndDate) BETWEEN 0 AND 30 THEN '1'
WHEN DATEDIFF(DAY,Date,EndDate) BETWEEN 31 AND 60 THEN '2'
ELSE SOMETHING END
In the next table I simply do a group by with Customer_nr and MAX( [Months left till service expiry]) and it works. However, I would also need the respective serviceID.
How can I do that?
You can just use row_number():
select a.*
from (select a.*,
row_number() over (partition by Customer_nr order by [Months left till service expiry] desc) as seqnum
from a
) a
where seqnum = 1;