StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL: How to count the number of unique days between date ranges that may overlap?
text
Body
copied!<p>I have a tricky problem. I have a table of social security contributions as below (<a href="http://www.sqlfiddle.com/#!6/b6a72/1" rel="nofollow">SQL Fiddle</a>)</p> <pre><code> IdPersona fecha_ingreso fecha_egreso 5690180 01/01/1987 30/11/2012 5690180 01/01/2010 30/11/2012 5690180 11/06/2012 15/11/2012 5690180 12/04/2012 25/04/2012 5690180 16/03/2012 30/03/2012 5690180 18/06/2011 15/10/2011 5690180 20/12/2012 20/01/2013 5690180 21/11/2012 15/12/2012 </code></pre> <p>Each row represents a job for the individual. The individual may have more than one job concurrently. I need to count how many days of contributions each individual made in a particular period, for example in the last year, The problem is that I must count only distinct days of contribution and not count the same day more than once for an individual. How can I achieve this?</p> <hr> <p>Outline possible solution to count the number of days but not unique 1-Define date range, a time window for which count the contributions made by the people. 2-For each RespondentID I have to count how many different days with input window has during this period?</p> <pre><code>DECLARE @FchRef DATETIME SET @FchRef ='2011-05-01' --Fhc referencia de comienzo del programa a partir de cual calcular aportes DECLARE @PeriRef SMALLINT SET @PeriRef =24 --Periodo a partir de la Fch ref para contar aportes DECLARE @FchCotDesde DATETIME SET @FchCotDesde=Dateadd(MONTH, -24, @FchRef) --Fch a partir de la cual calcular aportes SELECT ut.documento 'CI_UT', sum(DATEDIFF(DAY, CASE WHEN CONVERT(DATETIME, act.fecha_ingreso, 103) < @FchCotDesde THEN @FchCotDesde ELSE CONVERT(DATETIME, act.fecha_ingreso, 103) END, CASE WHEN fecha_egreso = '' THEN @FchRef ELSE CONVERT(DATETIME, act.fecha_egreso, 103) END)) 'DiasContados' FROM dbo.[UT2011_12] ut LEFT JOIN dbo.DatosPersonalesyDomicilios dat ON cast(cast(ut.documento AS DECIMAL(12, 0))AS VARCHAR) = dat.nro_documento LEFT JOIN dbo.Actividades act ON act.pers_identificador = dat.pers_identificador AND ( CONVERT(DATETIME, act.fecha_egreso, 103) = '' OR CONVERT(DATETIME, act.fecha_egreso, 103) >= @FchCotDesde ) -- La Fch de egreso es vacia ó con Fch posterior a fch ref AND CONVERT(DATETIME, act.fecha_ingreso, 103) <= @FchRef --La Fch de inicio de act es anterior a la FchRef WHERE ut.UT_2011_Inscriptos = 1 OR ut.UT_2012_Inscriptos = 1 GROUP BY ut.documento </code></pre> <p>THE problem with this solution is that it works if the date range (ingress and egress) overlap. It occurred to me as a solution to the problem: 1 - create a cursor that contains each of the people 2 - I create a loop that loops through day by day window period and if you throw one makes contributions and 0 but, for each of individuals. How will the solution I propose is extremely complex. Can you think of any better solution?</p> <p>With this script you can create the table</p> <pre><code> create table #aux( Idpersona int ,FechaIngreso datetime ,FechaEgreso datetime ) insert into #aux values(5690180,'1987/01/01','2012/11/30') insert into #aux values(5690180,'2010/01/01','2012/11/30') insert into #aux values(5690180,'2012/06/11','2012/11/15') insert into #aux values(5690180,'2012/04/12','2012/04/25') insert into #aux values(5690180,'2012/03/16','2012/03/30') insert into #aux values(5690180,'2011/06/18','2011/10/15') insert into #aux values(5690180,'2012/12/20','2013/01/20') insert into #aux values(5690180,'2012/11/21','2012/12/15') select * from #aux </code></pre> <p>I good way to see the problem is with one graphic</p> <pre><code>111111111111111111111111111000000000000000000000000000000000000000000000000000000000 |----A1----| |----A2----| |----A3----| 00000111111111111111110000001111111111111111111111111000000000000000000000000000000 |------B1-------| |----------B2-----------| </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload