Geek asks and answers

Periodic time series. Return first date of last contiguous block of recent data in T-SQL

03/10/2011 11/03/2011 12/03/2011 13/03/2011 14/03/2011 15/03/2011 QUERYRESULT
aaaaa 03/14/2011
                                    bbb 03/13/2011
cccc 03/03/2011

So, for the very last block of contiguous dates, we will return the earliest date from this particular block, as shown in the "queryresult" column.

Now I understand that I accidentally transferred the data in the example that I gave. For the source, we can assume that the scheme: [ID] 1,2,3, ...; [type] a, b, c ..; [The date]

I can see something with the recursive CTE / window / ranking functions, but I can not understand it.

+3
source share
2 answers

simpler than I thought and don't need CTE or window / ranking functions:

provided that the data

ID mydate pfo
1 12/03/2011 a
4 03/03/2011 a
6 03/14/2011 a
10 03/03/2011 a
2 03/03/2011 b
5 03/03/2011 b
8 03/15/2011 b
3 03/03/2011 c
7 03/14/2011 c
9 03/15/2011 c
11 03/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
+2
source 
;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
+1
source

More articles:


All Articles