SQL Sum date from multiple rows

1

I'm trying to get the first startdate and the last enddate of a particular idnum and poolnum then sum up or add on the startdate and enddate to get the TAT. initially I have the script but couldn't get the exact result.

please see below sample DDL and desired result.
any help is very much appreciated. thank you.

DECLARE @sample TABLE(idnum nvarchar(4), itemid nvarchar(10),
				itemnum nvarchar(10), poolnum nvarchar(2), startdate datetime, enddate datetime	)


INSERT INTO @sample
select '1001','1030-B','P00001','PC','2016-11-26 14:13:00.000','2016-11-26 20:50:00.000'
union 
select '1001','1030-B','P00002','RP','2016-11-28 15:02:00.000','2016-11-29 10:00:00.000'
union
select '1001','1030-B','P00004','PL','2016-11-28 20:02:00.000','2016-11-28 21:06:00.000'
union 
select '1001','1030-B','P00005','PC','2016-12-01 19:59:53.000','2016-12-05 11:35:44.000'
union
select '1002','1030-B','P00006','PC','2017-01-20 13:46:11.000','2017-01-21 03:12:38.000'



;with cte as
( 
	select idnum, itemid, itemnum, poolnum, startdate, enddate ,row_number() over (partition by idnum order by startdate asc) rownum
	from @sample
	where poolnum='PC'
)
select c1.idnum, c1.itemid, c1.itemnum, c2.itemnum, c1.poolnum, c1.startdate, c2.enddate ,
     CAST(c2.enddate - c1.startdate as decimal(10,2))      --  datediff(dd, c1.startdate, c2.enddate ) as tat
from cte c1
	left join cte c2 on c1.idnum = c2.idnum and c1.rownum = c2.rownum-1



expected result
================


idnum|itemid|itemnum|poolnum|startdate				|enddate				|TAT result
-----------------------------------------------------------------------------------------
1001 |1030-B|P00001	|PC		|2016-11-26 14:13:00.000|2016-12-05 11:35:44.000|8.89
1002 |1030-B|P00006	|PC		|2017-01-20 13:46:11.000|2017-02-01 03:12:38.000|2
1001 |1030-B|P00002 |RP     |2016-11-28 15:02:00.000|2016-11-29 10:00:00.000|2
1001 |1030-B|P00004 |PL     |2016-11-28 20:02:00.000|2016-1
2

This problem, for me, is not clear.
Anyway, try to use FIRST_VALUE / LAST_VALUE to get the first startDate and last endDate.
Maybe next lines will help you :

;WITH aCTE
AS
(   select DISTINCT
         idnum
        ,FIRST_VALUE(startDate) OVER (PARTITION BY idNum 
                                      ORDER BY startdate ASC
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_StartDate
        ,FIRST_VALUE(enddate) OVER (PARTITION BY idNum 
                                      ORDER BY enddate DESC
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_EndDate
	from @sample
	where poolnum='PC'
)

SELECT
    idnum
    ,first_StartDate
    ,first_EndDate
    ,DATEDIFF(DD,first_StartDate,first_EndDate) AS TT
    ,CAST(first_EndDate - first_StartDate as decimal(10,2)) AS calculatedTT
FROM
    aCTE

output here:

idnum	first_StartDate		first_EndDate		TT	calculatedTT
1001	26.11.2016 14:13:00	05.12.2016 11:35:44	9	8,89
1002	20.01.2017 13:46:11	21.01.2017 03:12:38	1	0,56

http://rextester.com/UIL56386

3

Thank you for your reply. below is the modified script and I captured the desired output.

;with cte as
( 
	select idnum, itemid, itemnum, poolnum, startdate, enddate ,
	row_number() over (partition by idnum order by startdate asc) firstrnk,
		row_number() over (partition by idnum order by startdate desc) lastrnk
	from @sample

	where poolnum='PC'
)
select c1.idnum, c1.itemid, c1.itemnum, c2.itemnum, c1.poolnum, c1.startdate, c2.enddate ,
       CAST(c2.enddate - c1.startdate as decimal(10,2))      
from   cte c1
	left join ( select * from cte
				where lastrnk=1
				) c2
				on c1.idnum= c2.idnum and c2.poolnum='PC'
where c1.firstrnk=1