How To Calculate No. Of Days Between Two Datetime Column In Sql Server

There are two tables, T1 includes start date column and T2 includes end date column. Both the columns have both date and time. T1 has all the rows unique while T2 can have multipl

Solution 1:

;WITH MaxEndDate AS
(
    SELECT
        T2.PrimaryKey,
        MaxEndDate = MAX(EndDate)
    FROM
        T2
    GROUPBY
        T2.PrimaryKey
)
SELECT
    T1.PrimaryKey,
    T1.StartDate,
    M.MaxEndDate,
    AmountDays = DATEDIFF(DAY, T1.StartDate, M.MaxEndDate)
FROM
    T1
    INNER JOIN MaxEndDate AS M ON T1.PrimaryKey = M.PrimaryKey

Solution 2:

I would recommend creating a temporary table using T2 something like

SelectDistinct ID
,MAX(ENDDATE) As [ENDDATE]
INTO #TMPTABLE1 

then you include this into T1

Select A.ID
,Startdate
,B.ENDDATE
,Datediff(day,A.STARTDATE,B.ENDDATE) as DAYS
From T1 as A inner join 
#TEMPTABLE1 as B on A.ID = B.ID

Solution 3:

--------get no of days among multiple dates----------

DECLARE@iINT , @numrowsINT , @daysINT , @Fdate DATETIME , @Tdate DATETIME;

SET@days=0;
    DECLARE@employee_table TABLE ( idx SMALLINTPRIMARY KEY IDENTITY(1,
    1) ,
    EmpId INT ,
    SinceDate DATETIME ,
    TillDate DATETIME );
    -- populate employee table INSERT @employee_tableSELECT
            EmpId ,
            SinceDate ,
            TillDate 
        FROM
            T_EmpPosting_PIS 
        WHERE
            EmpId =18AND OfficeTypeID =1ORDERBY
            TransDate;
    --SELECT*FROM@employee_table -- enumerate the table SET@i=1;

SET@numrows= ( SELECTCOUNT(*) 
    FROM@employee_table );
    IF @numrows>0 WHILE ( @i<= ( SELECTMAX(idx) 
    FROM@employee_table ) ) BEGINSET@Fdate= ( SELECT
    SinceDate                        
FROM@employee_table                        
WHERE
    idx =@i                      );
    IF ( @i=@numrows )             BEGINSET@Tdate= GETDATE();

END;

    ELSEBEGINSET@Tdate= ( SELECT
        TillDate                                
    FROM@employee_table                                
    WHERE
        idx =@i                              );

END;

SET@days= ( SELECT
        DATEDIFF(DAY,
        @Fdate,
        @Tdate)                     )   @days;

SET@i=@i1;

END;
    SELECT@days;