Insert New Rows in sql based on difference between 2 dates

  • PSB

    SSChampion

    Points: 13181

    Hi,

     

    I want to create a new records based on the difference between Start and End dates when StartDate is not NULL . The New value created can be first of every month . I need this field for reporting purpose to get a count of employees my month .

     

    CREATE TABLE #MonthsYearBetwenDates

    (

    CName VARCHAR(100) NULL,

    StartDate Date NULL,

    Enddate Date NULL,

    ReportDate Date NULL

    )

    Insert INTO #MonthsYearBetwenDates (CName,StartDate,EndDate)

    SELECT 'Employee1','01/01/2020','02/28/2020' UNION ALL

    SELECT 'Employee2','04/05/2020','12/05/2020' UNION ALL

    SELECT 'Employee3',NULL,NULL UNION ALL

     

    SELECT 'Employee4','01/01/2019',NULL UNION ALL

     

    SELECT 'Employee5','05/01/2019','10/31/2019'

    SELECT * FROM #MonthsYearBetwenDates

    -- Desired output

     

    SELECT 'Employee1' AS CName,'01/01/2020' AS StartDate,'02/28/2020' AS EndDate, '01/01/2020' As ReportDate UNION ALL

    SELECT 'Employee1' AS CName,'01/01/2020' AS StartDate,'02/28/2020' AS EndDate, '02/01/2020' UNION ALL

    SELECT 'Employee2' AS CName,'04/05/2020' AS StartDate,'12/05/2020' AS EndDate, '04/01/2020' As ReportDate UNION ALL

    SELECT 'Employee2' AS CName,'04/05/2020' AS StartDate,'12/05/2020' AS EndDate, '05/01/2020' As ReportDate UNION ALL

    SELECT 'Employee2' AS CName,'04/05/2020' AS StartDate,'12/05/2020' AS EndDate, '06/01/2020' As ReportDate UNION ALL

    SELECT 'Employee2' AS CName,'04/05/2020' AS StartDate,'12/05/2020' AS EndDate, '07/01/2020' As ReportDate UNION ALL

    SELECT 'Employee2' AS CName,'04/05/2020' AS StartDate,'12/05/2020' AS EndDate, '08/01/2020' As ReportDate UNION ALL

    SELECT 'Employee2' AS CName,'04/05/2020' AS StartDate,'12/05/2020' AS EndDate, '09/01/2020' As ReportDate UNION ALL

    SELECT 'Employee2' AS CName,'04/05/2020' AS StartDate,'12/05/2020' AS EndDate, '10/01/2020' As ReportDate UNION ALL

    SELECT 'Employee2' AS CName,'04/05/2020' AS StartDate,'12/05/2020' AS EndDate, '11/01/2020' As ReportDate UNION ALL

    SELECT 'Employee2' AS CName,'04/05/2020' AS StartDate,'12/05/2020' AS EndDate, '12/01/2020' As ReportDate UNION ALL

    SELECT 'Employee3' AS CName,NULL,NULL,NULL UNION ALL

     

    SELECT 'Employee4' AS CName,'01/01/2019' AS StartDate ,NULL,NULL as ReportDate UNION ALL

    SELECT 'Employee5' AS CName,'05/01/2019' AS StartDate,'10/31/2019' AS EndDate, '05/01/2019' As ReportDate UNION ALL

    SELECT 'Employee5' AS CName,'05/01/2019' AS StartDate,'10/31/2019' AS EndDate, '06/01/2019' As ReportDate UNION ALL

    SELECT 'Employee5' AS CName,'05/01/2019' AS StartDate,'10/31/2019' AS EndDate, '07/01/2019' As ReportDate UNION ALL

    SELECT 'Employee5' AS CName,'05/01/2019' AS StartDate,'10/31/2019' AS EndDate, '08/01/2019' As ReportDate UNION ALL

    SELECT 'Employee5' AS CName,'05/01/2019' AS StartDate,'10/31/2019' AS EndDate, '09/01/2019' As ReportDate UNION ALL

    SELECT 'Employee5' AS CName,'05/01/2019' AS StartDate,'10/31/2019' AS EndDate, '10/01/2019' As ReportDate

     

    DROP TABLE #MonthsYearBetwenDates

     

    Thanks,

    PSB

  • jonathan.crawford

    SSCertifiable

    Points: 6582

    You don't need to do that to get counts by month. I'm sure there are multiple ways to do this, I would use a calendar table of some sort and then pull out the counts on the 1st of each month, something like this:

    --you would replace calenderTable with whatever your calendarTable name and column name are:
    CREATE TABLE #calendarTable (iRow int identity(1,1), reportDate date);
    INSERT INTO #calendarTable (reportDate)
    SELECT '1/1/2020' UNION ALL
    SELECT '1/2/2020' UNION ALL
    SELECT '1/3/2020' UNION ALL
    SELECT '1/4/2020' UNION ALL
    SELECT '1/31/2020' UNION ALL
    SELECT '2/1/2020' UNION ALL
    SELECT '3/1/2020' UNION ALL
    SELECT '4/1/2020' UNION ALL
    SELECT '5/1/2020' UNION ALL
    SELECT '6/1/2020' UNION ALL
    SELECT '7/1/2020' UNION ALL
    SELECT '8/1/2020' UNION ALL
    SELECT '9/1/2020' UNION ALL
    SELECT '10/1/2020' UNION ALL
    SELECT '11/1/2020' UNION ALL
    SELECT '12/1/2020'
    ;

    SELECT COUNT(*),#calendarTable.reportDate
    FROM #MonthsYearBetwenDates sourceTable
    CROSS JOIN #calendarTable
    WHERE sourceTable.StartDate<=#calendarTable.reportDate
    AND sourceTable.EndDate >= #calendarTable.reportDate
    --only the 1st of the month dates
    AND DAY(#calendarTable.reportDate) = 1
    GROUP BY #calendarTable.reportDate
    ORDER BY #calendarTable.reportDate

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply