D
With a temporary board I will go line to row taking the creation date and the end date, I accumulate the difference of days plus one to count also the creation date, erase all the records that are between the dates we have just selected and follow my cycle:CREATE TABLE #tablaTemporal(
ticketnumber VARCHAR(max),
fechacreacion DATETIME,
fechafin DATETIME,
cantidaddias INT
)
INSERT INTO #tablaTemporal VALUES
('73252-1', '2018-04-09', '2018-04-11', 3),
('73252-2', '2018-04-09', '2018-04-10', 2),
('73252-3', '2018-04-12', '2018-04-16', 5)
DECLARE @CONTADOR INT, @ACUMULADOR INT = 0, @FECHACREACION DATETIME, @FECHAFIN DATETIME
SELECT @CONTADOR = COUNT(*) FROM #tablaTemporal
WHILE (@CONTADOR > 0)
BEGIN
SELECT TOP(1) @FECHACREACION = fechacreacion, @FECHAFIN = fechafin
FROM #tablaTemporal
WHERE ticketnumber LIKE '%73252%' ORDER BY fechacreacion, fechafin DESC
SET @ACUMULADOR = @ACUMULADOR + DATEDIFF(DAY, @FECHACREACION, @FECHAFIN) + 1
DELETE FROM #tablaTemporal
WHERE fechacreacion >= @FECHACREACION
AND fechafin <= @FECHAFIN
AND ticketnumber LIKE '%73252%'
SELECT @CONTADOR = COUNT(*) FROM #tablaTemporal
END
SELECT @ACUMULADOR AS 'Resultado'
DROP TABLE #tablaTemporal
My previous answer and this are valid the reason why it didn't give me the result you expected has to do with the DateDiff function.If you do these querys, you'll realize what I'm talking about:SELECT DATEDIFF (DAY, '2018-04-09', '2018-04-11') --DEVUELVE 2 Y DEBERÍA SER 3
SELECT DATEDIFF (DAY, '2018-04-09', '2018-04-10') --DEVUELVE 1 Y DEBERÍA SER 2
SELECT DATEDIFF (DAY, '2018-04-12', '2018-04-16') --DEVUELVE 4 Y DEBERÍA SER 5
SELECT DATEDIFF (DAY, '2018-04-12', '2018-04-12') --DEVUELVE 0 Y DEBERÍA SER 1
What happens is that the function that does is to draw the differences but does not carry a count, so to work the way you want it you just have to add 1 to that difference.Datepart limitsThe following instructions have the same startdate and enddate values. Those dates are adjacent and have an hourly difference of 0.0000001 seconds. The difference between startdate and enddate in each instruction crosses a time or time limit of your datepart. Each instruction returns 1. If startdate and enddate have different year values but have the same calendar week values, DATEDIFF will return 0 to datepart week.SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
It gets out is the difference between date and date so logically to that difference you have to add one to tell you the start date, in conclusion with the query in this way should work:CREATE TABLE #tablaTemporal(
ticketnumber VARCHAR(max),
fechacreacion DATETIME,
fechafin DATETIME,
cantidaddias INT
)
INSERT INTO #tablaTemporal VALUES
('73252-1', '2018-04-09', '2018-04-11', 3),
('73252-2', '2018-04-09', '2018-04-10', 2),
('73252-3', '2018-04-12', '2018-04-16', 5)
SELECT DATEDIFF(DAY, MIN(fechacreacion), MAX(fechafin)) + 1 AS 'Resultado'
FROM #tablaTemporal
WHERE ticketnumber LIKE '%73252%'
DROP TABLE #tablaTemporal
If I' be touched to decide between one and another query I'd prefer the first one because I would respect the gaps that may exist between dates, the second query would work only if the dates are run and there are no gaps.