MS SQL Server
  1. MS SQL Server
  3. Nombre de jours du count SQL dans la plage de dates
Intereting Posts
SQL select avec clause IN SSIS – lire datatables de la feuille et passer chaque ligne à une procédure stockée SSIS (VS 2012) Problème avec la composante de script VB Sqlconnection ne supporte pas les transactions parallèles Sélectionnez la sum des articles en fonction de leur date d'input mensuelle Appel de la fonction MSSQL sans spécifier de schéma Mise à jour d'une table avec INSERT vs UPDATE (performance vs clearness) utilisant l'auto-referencement dans le server sql Comment les index de vue SQL Server fonctionnent-ils sous le capot? login à l'instance par défaut de SQL Server 2016 LocalDB Le meilleur moyen d'aplatir / dénormaliser les tables de search SQL? Est-il nécessaire d'utiliser SSIS pour créer des cubes SSAS à l'aide des données Oracle? Ouverture d'une connection DbContext dans une connection SqlConnection existante Doit déclarer l'erreur de variable scalaire Performances de requête lors de l'utilisation de variables

Nombre de jours du count SQL dans la plage de dates

Voici un exemple de ce que j'ai dans ma table (SQL Server): 

Start End 2014-08-31 2014-09-01 2014-09-09 2014-09-11 2014-09-11 2014-09-26 2014-09-15 2014-09-23 2014-09-16 2014-09-22 2014-09-17 2014-09-19 2014-09-17 2014-09-26 2014-09-17 2014-10-03 2014-09-17 2014-09-26 2014-09-17 2014-09-18 2014-09-18 2014-10-04 2014-09-18 2014-09-19 2014-09-18 2014-09-19 2014-09-19 2014-09-20

Ceci est une table de request de congé. J'ai besoin d'get le nombre total de jours. La sortie devrait être comme ça. 

 2014-08-31 == 1 2014-09-01 == 1 2014-09-09 == 1 2014-09-10 == 1 2014-09-11 == 2 2014-09-12 == 1 2014-09-13 == 1 2014-09-14 == 1 2014-09-15 == 2 2014-09-16 == 2 2014-09-17 == 8 2014-09-18 == 11 2014-09-19 == 11 2014-09-20 == 8

Utilisation de LEFT JOIN :

SQL Fiddle 

 SELECT l.dt, COUNT(t.Start) AS cnt FROM Leave l LEFT JOIN tbl t ON dt BETWEEN Start AND [End] GROUP BY l.dt

En utilisant OUTER APPLY :

SQL Fiddle 

 SELECT l.dt, a.cnt FROM Leave l OUTER APPLY( SELECT COUNT(*) AS cnt FROM tbl WHERE dt BETWEEN Start AND [End] )a

Si vous n'avez pas de table de congé, vous pouvez en créer une dynamic. En utilisant une théorie similaire à celle de Felix, voici comment build votre requête:

SQLFiddle: http://sqlfiddle.com/#!6/750b9/16 

 create table test (starting date, ending date); insert into test values ('2014-08-31','2014-09-01'), ...

Maintenant, la requête: 

 with -- create a dynamic in-memory table holding a list of dates datecte as ( select cast('2014-08-01' as datetime) dateval union all select dateval+1 from datecte where dateval+1 < '2014-09-30' ), -- call it leave leave as ( select dateval from datecte ) -- combine the dynamically created in-memory date table -- with actual dataset select l.dateval, count(t.starting) as counter from leave l left join test t on dateval between starting and ending group by l.dateval having count(t.starting) > 0

Résultats: 

 | dateval | counter | |-----------------------------|---------| | August, 31 2014 00:00:00 | 1 | | September, 01 2014 00:00:00 | 1 | | September, 09 2014 00:00:00 | 1 | | September, 10 2014 00:00:00 | 1 | | September, 11 2014 00:00:00 | 2 | | September, 12 2014 00:00:00 | 1 | | September, 13 2014 00:00:00 | 1 | | September, 14 2014 00:00:00 | 1 | | September, 15 2014 00:00:00 | 2 | | September, 16 2014 00:00:00 | 3 | | September, 17 2014 00:00:00 | 8 | | September, 18 2014 00:00:00 | 11 | | September, 19 2014 00:00:00 | 11 | | September, 20 2014 00:00:00 | 8 | | September, 21 2014 00:00:00 | 7 | | September, 22 2014 00:00:00 | 7 | | September, 23 2014 00:00:00 | 6 | | September, 24 2014 00:00:00 | 5 | | September, 25 2014 00:00:00 | 5 | | September, 26 2014 00:00:00 | 5 | | September, 27 2014 00:00:00 | 2 | | September, 28 2014 00:00:00 | 2 | | September, 29 2014 00:00:00 | 2 |

SQLFiddle complet: http://sqlfiddle.com/#!6/750b9/17

Je veux juste partager ma résolution. 

 tb_LeaveRequest id StartDate EndDate 1 09/17/2014 09/18/2014 2 09/18/2014 10/04/2014 3 09/17/2014 10/03/2014 4 09/17/2014 09/26/2014 5 09/18/2014 09/19/2014 6 09/25/2014 09/25/2014 7 09/26/2014 09/26/2014 8 09/19/2014 09/20/2014 9 09/25/2014 09/25/2014 10 09/17/2014 09/19/2014 11 09/09/2014 09/11/2014 12 09/18/2014 09/19/2014 13 09/11/2014 09/26/2014 14 09/22/2014 09/23/2014 15 09/22/2014 09/23/2014 16 09/22/2014 09/22/2014 17 09/23/2014 09/23/2014 18 09/24/2014 09/24/2014 19 09/15/2014 09/23/2014 20 09/25/2014 09/25/2014 22 09/26/2014 09/26/2014 23 09/23/2014 09/23/2014 24 09/17/2014 09/26/2014 26 09/22/2014 09/22/2014 27 09/16/2014 09/22/2014 28 09/22/2014 09/22/2014

Ci-dessus, datatables de la table tb_LeaveRequest

Voici ma requête ci-dessous: 

 --Create temp table for tb_LeaveRequest with IsProcssed Column CREATE TABLE #tmpLeaveRequest (id int, StartDate datetime, EndDate datetime, IsProcessed bit) --Insert tb_LeaveRequest in temp table insert INTO #tmpLeaveRequest(id, StartDate, EndDate, IsProcessed) Select id, StartDate, EndDate, 0 from tb_LeaveRequest Order by StartDate --Create temp table for out CREATE TABLE #tmpOutput (Dys datetime, Counts int) While (Select Count(*) From #tmpLeaveRequest Where IsProcessed = 0) > 0 Begin DECLARE @Id as int DECLARE @StartDate as datetime DECLARE @DateDiff INT = 0; DECLARE @cnt INT = 0; Declare @NewDate as datetime Select Top 1 @Id = Id, @StartDate = StartDate, @DateDiff = DATEDIFF(D, StartDate, EndDate) From #tmpLeaveRequest Where IsProcessed = 0 ---- WHILE (@cnt <= @DateDiff) BEGIN set @NewDate = DATEADD(day, @cnt, @StartDate) if exists(select * from #tmpOutput where Dys = @NewDate) Begin Update #tmpOutput Set Counts = Counts + 1 Where Dys = @NewDate End else BEGIN Insert into #tmpOutput (Dys, Counts) values (@NewDate, 1) end set @cnt = @cnt + 1 END --End Loop Update #tmpLeaveRequest Set IsProcessed = 1 Where Id = @Id End --End Loop --Output Select * From #tmpOutput Order BY Dys --Drop tmp Table DROP TABLE #tmpOutput DROP TABLE #tmpLeaveRequest

Out serait: 

 Dys Counts 09/09/2014 1 09/10/2014 1 09/11/2014 2 09/12/2014 1 09/13/2014 1 09/14/2014 1 09/15/2014 2 09/16/2014 3 09/17/2014 8 09/18/2014 11 09/19/2014 11 09/20/2014 8 09/21/2014 7 09/22/2014 12 09/23/2014 10 09/24/2014 6 09/25/2014 8 09/26/2014 7 09/27/2014 2 09/28/2014 2 09/29/2014 2 09/30/2014 2 10/01/2014 2 10/02/2014 2 10/03/2014 2 10/04/2014 1

Voici la syntaxe 

  SELECT DATEDIFF(dd, Start, End) FROM TableName SELECT DATEDIFF(dd,'2014-08-31', '2014-09-01') AS 'DaysCount' FROM TableName -- result: 1