问按日显示从-到

EN

DECLARE @TB AS TABLE(
  SERVICE_ID INTEGER,
  DATE_SEQ SMALLDATETIME,
  DAY_SEQ NVARCHAR(10),
  FARE_SUP_ID INT
)

INSERT INTO @TB VALUES
  (101004,'2012-10-18 00:00:00',1,267),
  (101001,'2012-10-19 00:00:00',2,171),
  (101004,'2012-10-20 00:00:00',3,267),
  (101004,'2012-10-21 00:00:00',4,171),
  (101001,'2012-10-22 00:00:00',5,171),
  (101001,'2012-10-23 00:00:00',6,171),
  (101004,'2012-10-24 00:00:00',7,267)

SELECT * fROM @TB

;WITH TB1 AS (


SELECT 
  T1.SERVICE_ID,
  MIN(T1.DATE_SEQ) AS CHECK_IN, 
  MAX(T2.DATE_SEQ) AS CHECK_OUT,
  MIN (T1.DAY_SEQ) + ' - ' + MAX(T2.DAY_SEQ) AS DAY_SEQ,
  MAX(T1.FARE_SUP_ID) AS FARE_SUP_ID,
  1 AS DIFF
  FROM @TB T1 INNER JOIN @TB T2
    ON T1.SERVICE_ID = T2.SERVICE_ID
  WHERE DATEDIFF(DY, T1.DATE_SEQ ,T2.DATE_SEQ) = 1
  GROUP BY 
    T1.SERVICE_ID

UNION ALL

SELECT  T3.SERVICE_ID,T3.DATE_SEQ AS CHECK_IN ,T4.DATE_SEQ AS CHECK_OUT 
  ,T3.DAY_SEQ,T3.FARE_SUP_ID,DATEDIFF(DY, T3.DATE_SEQ ,T4.DATE_SEQ)  AS DIFF
  FROM @TB T3 INNER JOIN @TB T4
    ON T3.SERVICE_ID = T4.SERVICE_ID
  WHERE DATEDIFF(DY, T3.DATE_SEQ ,T4.DATE_SEQ) = 0
    AND 
    T3.DATE_SEQ NOT IN (
      SELECT T1.DATE_SEQ
        FROM @TB T1 INNER JOIN @TB T2
          ON T1.SERVICE_ID = T2.SERVICE_ID
        WHERE DATEDIFF(DY, T1.DATE_SEQ ,T2.DATE_SEQ) = 1
    )
    AND 
      T4.DATE_SEQ NOT IN (
        SELECT T2.DATE_SEQ
          FROM @TB T1 INNER JOIN @TB T2
            ON T1.SERVICE_ID = T2.SERVICE_ID
          WHERE DATEDIFF(DY, T1.DATE_SEQ ,T2.DATE_SEQ) = 1
      )
  )
SELECT 
  *
  FROM TB1
  ORDER BY DAY_SEQ

select 
SERVICE_ID, 
(select cast(min(DATE_SEQ) AS VARCHAR(20)) from my_table where SERVICE_ID = mt.SERVICE_ID) + ' - ' +(select cast(min(DATE_SEQ) AS VARCHAR(20)) from my_table where SERVICE_ID = mt.SERVICE_ID) as FROM_TO,
(select cast(min(DAY_SEQ) AS VARCHAR(20)) from my_table where SERVICE_ID = mt.SERVICE_ID) + ' - ' +(select cast(min(DAY_SEQ) AS VARCHAR(20)) from my_table where SERVICE_ID = mt.SERVICE_ID) as DATE
from my_table mt

SELECT Service_id, 
CONCAT(MIN_DATE," - ", MAX_DATE) AS FromTo,
CONCAT(MIN_SEQ," - ", MAX_SEQ) AS Date
FROM
(
  SELECT Service_id, 
  MIN(DATE_SEQ) AS MIN_DATE, 
  MAX(DATE_SEQ) AS MAX_DATE, 
  MIN(DAY_SEQ) AS MIN_SEQ, 
  MAX(DAY_SEQ) AS MAX_SEQ
  FORM YOUR_TABLE
  GROUP BY Service_id ) AS A