January 28, 2020 at 7:33 pm
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
January 28, 2020 at 8:00 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy