Intermittent time series. Return first date of last contiguous block of most recent data in T-SQL
10/03/2011 11/03/2011 12/03/2011 13/03/2011 14/03/2011 15/03/2011 QUERYRESULT a a a a a 14/03/2011 开发者_运维技巧 b b b 13/03/2011 c c c c 12/03/2011
So for the most recent block of contiguous dates, return the earliest date from that particular block, as shown in the 'queryresult' column.
I realise now that I've inadvertently transposed the data in the example I gave For source can assume schema is: [ID] 1,2,3,...; [type] a,b,c..; [date]
I can sort of see something with recursive CTE/window/ranking functions, but can't quite figure it out.
easier than I thought and doesn't need CTE or windowing/ranking functions:
assuming data is
ID mydate pfo 1 12/03/2011 a 4 13/03/2011 a 6 14/03/2011 a 10 16/03/2011 a 2 12/03/2011 b 5 13/03/2011 b 8 15/03/2011 b 3 12/03/2011 c 7 14/03/2011 c 9 15/03/2011 c 11 16/03/2011 c
then
SELECT MAX( n.mydate) ,n.pfo FROM testtable n LEFT OUTER JOIN testtable n2 ON n.pfo = n2.pfo AND DATEADD(D,-1,n.mydate) = n2.mydate WHERE n2.mydate IS NULL GROUP BY n.pfo
;WITH data (type, date) AS (
SELECT 'a', CAST('20110310' AS datetime) UNION ALL
SELECT 'a', CAST('20110311' AS datetime) UNION ALL
SELECT 'a', CAST('20110312' AS datetime) UNION ALL
SELECT 'a', CAST('20110314' AS datetime) UNION ALL
SELECT 'a', CAST('20110315' AS datetime) UNION ALL
SELECT 'b', CAST('20110313' AS datetime) UNION ALL
SELECT 'b', CAST('20110314' AS datetime) UNION ALL
SELECT 'b', CAST('20110315' AS datetime) UNION ALL
SELECT 'c', CAST('20110310' AS datetime) UNION ALL
SELECT 'c', CAST('20110312' AS datetime) UNION ALL
SELECT 'c', CAST('20110313' AS datetime) UNION ALL
SELECT 'c', CAST('20110314' AS datetime)
),
grouped AS (
SELECT
type,
date,
groupID = DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY type ORDER BY date), date)
FROM data
),
startdates AS (
SELECT
type,
groupStartDate = MIN(date)
FROM grouped
GROUP BY type, groupID
)
SELECT
type,
LastGroupStartDate = MAX(groupStartDate)
FROM startdates
GROUP BY type
精彩评论