Defferent Between Dates

Transact-SQL
1

How To Get This Query

DateFrom DateTo NoDay
2015-01-01 2015-01-04 3
2015-01-07 2015-01- 18 11
2015-01-11 2015-01-22 11

i need sum (NoDay) after Excluding overlapping dates

2

try This
select DateFrom,DateTo ,DATEDIFF (dd,DateFrom ,DateTo )NoDay.

Could you please explain what you mean "sum (NoDay) after Excluding overlapping dates"

3 
; with
cte as
(
    select    rn = row_number() over (order by DateFrom), DateFrom ,DateTo, grp = 1
    from    tbl
),
rcte as
(
    select    rn, DateFrom, DateTo, grp = 1
    from    cte
    where    rn = 1

    union all

    select    c.rn, c.DateFrom, c.DateTo,
        grp = case when c.DateFrom between r.DateFrom and r.DateTo
               then r.grp
               else r.grp + 1
               end
    from    rcte r
        inner join cte c    on    r.rn    = c.rn - 1
),
result as
(
    select    days = datediff(day, min(DateFrom), max(DateTo))
    from    rcte
    group by grp
)
select    sum(days)
from    result
4

If you have a Numbers table in your database, you can use that to do this query. If you don't have one, create a temporary one like this

CREATE TABLE #N (n INT NOT NULL PRIMARY KEY);

;WITH cte (n) AS
(
	SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
	SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
INSERT INTO #N
SELECT ROW_NUMBER() OVER (ORDER BY a.n)
FROM cte a CROSS JOIN cte b CROSS JOIN cte c

After you create the numbers table then you can query like this:

-- TEST DATA
CREATE TABLE #Data
    (DateFrom DATE, DateTo DATE, NoDay INT);
    INSERT INTO #Data VALUES
      ('20150101','20150104',3),('20150107','20150118',11), ('20150111','20150122',11);


-- Query
    SELECT
    	 COUNT(DISTINCT DATEADD(dd,n.n,DateFrom))
    FROM #Data d
    	INNER JOIN #N n ON
    		DATEADD(dd,n.n,DateFrom) <= DateTo
1 Like