MS SQL Server
  1. MS SQL Server
  3. Sérialiser les dates dans un format 'range' sans utiliser de procédure
Intereting Posts
S'il vous plaît expliquer cette suppression top 100 SQL syntaxe Bind DropDownList t Colonne datable dans le contrôle DataGrid Sélectionnez la durée moyenne Comment calculer la différence entre les champs binarys? Une database SQL Server peut-elle répondre lors d'une transaction? Quelle est l'approche la plus simple à la list d'amis dans EF? Vue indexée: Union All / Full Join alternatives Order By Clause semble ne pas fonctionner Comment exécuter une sous-requête en fonction des résultats d'une requête SQL SQL Server: comment contraindre une table à contenir une seule ligne? Solution de procédure stockée SQL Server Soustraire une geometry SQL Server d'une autre Supprimer automatiquement les lignes dans Excel avant l'import dans SQL Server Comment puis-je faire une key primaire comme AUTOINCREMENT Renvoyer l'logging avec la valeur maximale en utilisant GROUP BY dans la vue SQL Server

Sérialiser les dates dans un format 'range' sans utiliser de procédure

J'ai besoin de find des plages de dates dans une colonne et de les sérialiser dans un format concis ( start – end pour une plage ou date pour une plage à une seule date).

J'ai un CTE ( readings ) qui returnne un set de données qui ressemble à: 

 ID VALUE DATE 1234567 A 2012-05-09 1234567 A 2012-05-10 1234567 A 2012-05-11 1234567 A 2012-05-16 1234567 A 2012-05-17 1234567 A 2012-05-20 1234567 B 2012-05-11 1234567 B 2012-05-12 1234567 B 2012-05-13 1234567 B 2012-05-14

J'ai pu get: 

 ID VALUE TOTAL_DAYS DATES 1234567 A 6 2012-05-09; 2012-05-10; 2012-05-11; 2012-05-16; 2012-05-17; 2012-05-20 1234567 B 4 2012-05-11; 2012-05-12; 2012-05-13; 2012-05-14

En utilisant: 

 readings AS ( ... ) , reading_aggr AS ( SELECT ID, [VALUE] ,count(distinct date) TOTAL_DAYS ,STUFF(( SELECT '; ' + cast(date as varchar) FROM readings r0 WHERE id=r0.id AND value=r0.value ORDER BY date FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)' ),1,2,'') AS DATES FROM readings GROUP BY id, [value] ) SELECT * FROM readings_aggr

Je voudrais le formater en tant que tel: 

 ID VALUE TOTAL_DAYS DATES 1234567 A 6 2012-05-09 - 2012-05-11; 2012-05-16 - 2012-05-17; 2012-05-20 1234567 B 4 2012-05-11 - 2012-05-14

Est-ce possible sans utiliser une approche procédurale?

Vous pouvez utiliser cette requête: 

  SELECT ID, VALUE, MIN([DATE]) AS startDate, MAX([DATE]) AS endDate FROM ( SELECT ID, VALUE, DATE, DATEDIFF(Day, '1900-01-01' , [DATE])- ROW_NUMBER() OVER( PARTITION BY ID, VALUE ORDER BY [DATE] ) AS DateGroup FROM readings ) rGroups GROUP BY ID, VALUE, DateGroup

pour get une expression de table contenant tous les intervalles de début de vos données: 

 ID VALUE startDate endDate -------------------------------------- 1234567 A 2012-05-09 2012-05-11 1234567 A 2012-05-16 2012-05-17 1234567 A 2012-05-20 2012-05-20 1234567 B 2012-05-11 2012-05-14

Ensuite, utilisez la requête ci-dessus dans reading_aggr : 

 ;WITH start_end_readings AS ( SELECT ID, VALUE, MIN([DATE]) AS startDate, MAX([DATE]) AS endDate FROM ( SELECT ID, VALUE, DATE, DATEDIFF(Day, '1900-01-01' , [DATE])- ROW_NUMBER() OVER( PARTITION BY ID, VALUE ORDER BY [DATE] ) AS DateGroup FROM readings ) rGroups GROUP BY ID, VALUE, DateGroup ), readings_aggr AS ( SELECT ID, [VALUE] ,count(distinct date) TOTAL_DAYS ,STUFF(( SELECT '; ' + cast(startDate as varchar) + CASE WHEN startDate <> endDate THEN ' - ' + cast(endDate as varchar) ELSE '' END FROM start_end_readings r0 WHERE r1.id=r0.id AND r1.value=r0.value ORDER BY startDate FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)' ),1,2,'') AS DATES FROM readings AS r1 GROUP BY id, [value] ) SELECT * FROM readings_aggr

pour get le résultat souhaité: 

 ID VALUE TOTAL_DAYS DATES =========================================================================== 1234567 A 6 2012-05-09 - 2012-05-11; 2012-05-16 - 2012-05-17; 2012-05-20 1234567 B 4 2012-05-11 - 2012-05-14

SQL Fiddle Demo ici

Vous serez probablement capable de le faire en utilisant un agrégat CLR.

Voici un exemple de MSDN qui concatène vos données set. Juste en changeant la virgule en un point-virgule, vous pourriez avoir votre format actuel avec une requête beaucoup plus propre.

https://msdn.microsoft.com/en-us/library/ms165055%28v=vs.90%29.aspx

Une fois cela en place, vous pouvez modifier la méthode Accumulate et / ou Terminate pour examiner les plages de données et de sortie lorsque cela est possible. Vous voudrez probablement accumuler les valeurs dans quelque chose comme une SortedList au lieu d'un SsortingngBuilder, puis effectuer l'parsing de plage dans la méthode Terminate.

Vous pouvez le faire comme: 

 DECLARE @t TABLE ( ID INT, V CHAR(1), D DATE ) INSERT INTO @t VALUES ( 1234567, 'A', '2012-05-09' ), ( 1234567, 'A', '2012-05-10' ), ( 1234567, 'A', '2012-05-11' ), ( 1234567, 'A', '2012-05-16' ), ( 1234567, 'A', '2012-05-17' ), ( 1234567, 'A', '2012-05-20' ), ( 1234567, 'B', '2012-05-11' ), ( 1234567, 'B', '2012-05-12' ), ( 1234567, 'B', '2012-05-13' ), ( 1234567, 'B', '2012-05-14' ); WITH cte1 AS ( SELECT ID , V , CASE WHEN MIN(D) <> MAX(D) THEN CONVERT(NVARCHAR(MAX), MIN(D), 121) + ' - ' + CONVERT(NVARCHAR(MAX), MAX(D), 121) ELSE CONVERT(NVARCHAR(MAX), MIN(D), 121) END AS D , COUNT(*) AS cn FROM ( SELECT ID , V , D , DATEADD(dd, -ROW_NUMBER() OVER ( PARTITION BY V ORDER BY D ), D) AS rn FROM @t ) a GROUP BY ID , V , rn ),-- SELECT * FROM cte1, cte2 AS ( SELECT ID , V , SUM(cn) TOTAL_DAYS , STUFF((SELECT '; ' + D FROM cte1 r0 WHERE cte1.id = r0.id AND cte1.V = r0.V FOR XML PATH('') , TYPE).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2, '') AS DATES FROM cte1 GROUP BY id , V ) SELECT * FROM cte2

Sortie: 

 ID V TOTAL_DAYS DATES 1234567 A 6 2012-05-09 - 2012-05-11; 2012-05-16 - 2012-05-17; 2012-05-20 1234567 B 4 2012-05-11 - 2012-05-14

L'idée est d'abord get des îles ( https://www.simple-talk.com/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/ ) et ensuite appliquer vos trucs. Je sais que @Betsos m'a dépassé, mais c'est un peu différent. Mais l'idée est la même.