Daily view group request as From-To

Question

I have the data in the table below:

SERVICE_ID DATE_SEQ DAY_SEQ 101004 2012-10-18 1 101004 2012-10-19 2 101001 2012-10-20 3 101004 2012-10-21 4 

I want the request generation date to be shown as a range with the date {From-to}, it will be grouped by service identifier, and the date in the sequence if it finally displays the output:

 **Service_id** **From-To** **Date** 101004 2012-10-18 - 2012-10-19 1-2 101001 2012-10-20 - 2012-10-20 3-3 101004 2012-10-21 - 2012-10-21 4-4 

Answer 1

try it

 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 

Answer 2

try this, the idea is to run internal queries in the main request based on the id of the main request:

 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 

Answer 3

Perhaps I did not understand this question.

(the CONCAT () function depends on the DBMS, and you may need to specify dates in the text)

This seems like a direct "group by" request:

 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 

Hope this helps!

Answer 4

I artificially in the second table comes down to a sequence of one, as this will connect to the tables.

 SELECT CASE WHEN (t1.service_id = t2.service_id) THEN t1.service_id ELSE t2.service_id END AS service_id, CASE WHEN (t1.service_id = t2.service_id) THEN CAST(t1.date_seq AS nvarchar(10)) + ' - ' + CAST(t2.date_seq AS nvarchar(10)) ELSE CAST(t2.date_seq AS nvarchar(10)) + ' - ' + CAST(t2.date_seq AS nvarchar(10)) END AS date_seq, CASE WHEN (t1.service_id = t2.service_id) THEN CAST(t1.day_seq AS nvarchar(10)) + ' - ' + CAST(t2.day_seq AS nvarchar(10)) ELSE CAST(t2.day_seq AS nvarchar(10)) + ' - ' + CAST(t2.day_seq AS nvarchar(10)) END AS day_seq FROM dbo.test28 t1 INNER JOIN dbo.test28 t2 ON t1.day_seq = t2.day_seq - 1 

Answer 5

The following will work for sure (even if there are no time intervals between them) -

 --STEP 1. CREATE DUMMY TABLE WITH DUPLICATE_SEQ COLUMN. YOU MAY ADD ONE MORE COLUMN IN --SAME TABLE DECLARE @TABLE TABLE (SERVICE_ID INT, DATE_SEQ DATETIME, DAY_SEQ INT, DUPLICATE_SEQ INT DEFAULT(0)) INSERT INTO @TABLE(SERVICE_ID, DATE_SEQ, DAY_SEQ) VALUES (101004, '2012-10-18', 1), (101004, '2012-10-19', 2), (101001, '2012-10-20', 3), (101004, '2012-10-21', 4) --STEP 2. LOGIC TO UPDATE TABLE WITH DUPLICATE_SEQ VALUE FOR SEQUENTIAL DUPLICATES ONLY DECLARE @PREVIOUS_SERVICE_ID INT DECLARE @DUPLICTE_COUNTER INT = 1 SET @PREVIOUS_SERVICE_ID = (SELECT TOP 1 SERVICE_ID FROM @TABLE) UPDATE @TABLE SET DUPLICATE_SEQ = @DUPLICTE_COUNTER ,@DUPLICTE_COUNTER = CASE WHEN @PREVIOUS_SERVICE_ID = SERVICE_ID THEN @DUPLICTE_COUNTER ELSE @DUPLICTE_COUNTER + 1 END ,@PREVIOUS_SERVICE_ID = CASE WHEN @PREVIOUS_SERVICE_ID = SERVICE_ID THEN @PREVIOUS_SERVICE_ID ELSE SERVICE_ID END --STEP 3. LOGIC TO UPDATE TABLE WITH DUPLICATE_SEQ VALUE FOR SEQUENTIAL DUPLICATES ONLY SELECT SERVICE_ID AS 'Service_id' , CONVERT(CHAR(10), MIN(DATE_SEQ), 126) + ' - ' + CONVERT(CHAR(10), MAX(DATE_SEQ), 126) AS 'From-To' , CAST(MIN(DAY_SEQ) AS VARCHAR(10)) + ' - ' + CAST(MAX(DAY_SEQ) AS VARCHAR(10)) AS 'Date' FROM @TABLE GROUP BY DUPLICATE_SEQ, SERVICE_ID ORDER BY 3 ASC 

EXIT -

 Service_id From-To Date 101004 2012-10-18 - 2012-10-19 1 - 2 101001 2012-10-20 - 2012-10-20 3 - 3 101004 2012-10-21 - 2012-10-21 4 - 4