Insert New Rows in sql based on difference between 2 dates

• PSB

  SSChampion

  Points: 13195

  More actions

  January 28, 2020 at 7:33 pm

  #3720998

  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: 6610

  More actions

  January 28, 2020 at 8:00 pm

  #3721002

  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