假设有一个数据收集系统,每当记录更改时,它就会将其保存为具有前缀的新记录(例如,M- [QUE中的最新数字,并且是唯一的])。
假设我得到以下数据集:
Customer | Original_Val
1 1020
2 1011
3 1001
我需要为下表找到每个客户的最新价值:
Customer | Most_Recent_Val | Pretained_To_Val | date
1 M-2000 M-1050 20170225
1 M-1050 M-1035 20170205
1 M-1035 1020 20170131
1 1020 NULL 20170101
2 M-1031 1011 20170105
2 1011 NULL 20161231
3 1001 NULL 20150101
我所需的输出将是:
Customer | Original_Val | Most_Recent_Val | date
1 1020 M-2000 20170225
2 1011 M-1031 20170105
3 1001 1001 20150101
对于客户1,有4个级别,即(M-2000&lt; - M-1050&lt; - M-1035&lt; -1020)注意,每个客户的深度不超过10个级别。<<<<<<<</p>
非常感谢!预先感谢。
找到每个客户的最小和最大值,然后将其加入。这样的东西:
Select
[min].Customer
,[min].Most_Recent_Val as Original_Val
,[max].Most_Recent_Val as Most_Recent_Val
,[max].date
From
(
Select
Customer
,Most_Recent_Val
,date
From
table t1
inner join (
Select
Customer
,MIN(date) as MIN_Date
From
table
Group By
Customer
) t2 ON t2.Customer = t1.Customer
and t2.MIN_Date = t1.Date
) [min]
inner join (
Select
Customer
,Most_Recent_Val
,date
From
table t1
inner join (
Select
Customer
,MAX(date) as MAX_Date
From
table
Group By
Customer
) t2 ON t2.Customer = t1.Customer
and t2.MAX_Date = t1.Date
) [max] ON [max].Customer = [min].Customer