[Solved] Customers who upgraded from which mobile to which in sql server?

by 


WITH recentPhone as (
SELECT Cname, MAX(MobileSI) as recentSI
from RECORDS
GROUP BY Cname
)
SELECT
CASE WHEN earlier.Mname is null THEN
RECORDS.Cname + ' has just got their first mobile, a ' + RECORDS.Mname
ELSE
RECORDS.Cname + ' has upgraded from a ' + RECORDS.Mname + ' to a ' + earlier.Mname
END as results
FROM
RECORDS
INNER JOIN recentPhone ON RECORDS.Cname = recentPhone.Cname AND RECORDS.MobileSI = recentPhone.recentSI
LEFT JOIN RECORDS AS earlier ON RECORDS.Cname = earlier.Cname AND (RECORDS.MobileSI - 1) = earlier.MobileSI

Query assumes your table name is RECORDS, please replace RECORDS with actual table name for this to work. This query finds the max MobileSI associated with a customer name(you should really use Customer IDs because name repetition is not the best unique identifier…). It then constrains RECORDS to return rows associated with that max ID. Then there is a left join to another instance of RECORDS to check if there is an earlier phone. There is a CASE statement in the main query to deal with a customer who has only purchased one phone thus far.

Have fun and be safe out there!

solved Customers who upgraded from which mobile to which in sql server?