SQL Server Performance

How to show continuity or connectivity with Query

Discussion in 'ALL SQL SERVER QUESTIONS' started by boutwater, Apr 13, 2012.

  1. boutwater Member

    Hello, I'm trying to figure out how best way to group records that are consecutive. For example, I take service calls for my company, and I put an entry in my time database each day on the days that I am covering the phones. I would like a query that groups and totals the days that I am doing service. So, suppose this is my data:
    • 4/1/12 - Covering Phones
    • 4/2/12 - Covering Phones
    • 4/3/12 - Covering Phones
    • 4/5/12 - Covering Phones
    • 4/6/12 - Covering Phones
    • 4/9/12 - Covering Phones
    • 4/10/12 - Covering Phones
    • 4/11/12 - Covering Phones
    The report would show:
    • 3 days from 4/1/12-4/3/12
    • 2 days from 4/5/12-4/6/12
    • 3 days from 4/9/12-4/11/12
    There are other places that I'd like to use a similar report (but for minutes rather than days). For example, we track error on conveyor locations for a customer. if there is an error, we output the timestamp and the location every 10 seconds for when the error is active. I'd like to do the similar grouping here where I show the length that an error stayed on and the start and stop time for each occurance. Thanks in advance for any help you can provide!
    Ben
  2. Shehap MVP, MCTS, MCITP SQL Server

    If we take first that examples of dates and we assume a table named test having this data entity as below :

    CREATETABLE [dbo].[Test](
    [Date] [date] NULL,
    [data] [nchar](10)NULL
    )ON [PRIMARY]

    Then you could work out like this query below which could be circulated to other similar business needs

    CREATEtable #TABLE(ROWNO int,datedate, sumflag int)

    insertinto #TABLE(ROWNO ,date)selectROW_NUMBER()OVER (ORDERBY Date),DATE FROM Test

    UPDATE #TABLE SET sumflag=1 FROM #TABLE T INNERJOIN

    (SELECT (ROW_NUMBER()OVER (ORDERBY Date)+1)AS previousROWNO,DATEAS PreviousDATE FROM TEST ) S

    on s.previousROWNO =T.ROWNO ANDDATEDIFF(D, s.PreviousDATE, T.date)=1

    ;
    with CTEView as
    (
    selectROW_NUMBER()OVER (ORDERBY FIRSTDATE ASC)AS ROWNO, S.Firstdate ,SUM(sumflag)AS'Sums' from #TABLE T innerjoin(selectdistinctcasewhen sumflag ISNullthen dateendas Firstdate FROM #TABLE ) S on T.date>S.firstdate groupby s.Firstdate

    )

    select S.Firstdate ,(SELECTMAX(DATE)FROM #TABLE WHEREDATE< T.Firstdate)as EndDate, s.Sums-ISNULL(t.Sums, 0)+1 as'Sum of Consectutive days'

    From CTEVIEW S INNERJOIN CTEVIEW T ON S.ROWNO=T.ROWNO-1

    union

    select (firstdate),(selectMAX(DATE)FROM #TABLE),(Sums)+1 as'Sum of Consectutive days'from CTEView WHERE ROWNO=(SELECTMAX(ROWNO)FROM CTEView)

    Droptable #table

Share This Page