Split overlapping start and end dates to count days in multiple periods

1

I need to write a query that will do a date diff calculation, but if the start month/year is not the same as the end month/year, we need to calculate the number of days in one period as a separate field from the number of days in the next period. I envision the output will look something like:
ID, Days , Period (where period is 1 2015 m/year)

The query result will create multiple ID rows for each instance where that ID's start and end date is not in the same period, or just one row when the start and end date is in the same period.

2

It would be easier for someone who wants to respond if you posted consumable data like in the example below. The example below is my guess on what you are trying to accomplish, so it may or may not be useful.

Posting the table schema (as in my create table statement) along with sample data (as in my insert statement) and a similar set of create and insert statements that show the output you are looking for would be very helpful.

CREATE TABLE #tmp(StartDate DATE, EndDate DATE);
INSERT INTO #tmp VALUES
	('20140207','20160318'),
	('20150119','20150811');
	
SELECT
	*,
	DATEDIFF(DAY,
		CASE 
			WHEN startDate >= F THEN StartDate
			ELSE Y.F
		END,
		CASE 
			WHEN EndDate <= L THEN t.EndDate
			ELSE Y.L
		END) + 1
FROM
	#tmp t
	INNER JOIN 
	( VALUES
		('20140101','20141231'),
		('20150101','20151231'),
		('20160101','20161231') 
	) Y(F,L) ON 
		Y.L >= t.StartDate
		AND y.F <= t.EndDate;
1 Like
3

Thank you James
Your query is very helpful, however
I am not quite sure how to create a test query at the moment, but let me know if this helps in what I am trying to get.

consider the following source data
ID StartDate EndDate
12345 20120928 20130310

I would like the following output data

Desired Result

ID PeriodMonth PeriodYear Days
12345 9 2012 3
12345 10 2012 31
12345 11 2012 30
12345 12 2012 31
12345 1 2013 10

Does that make sense?

4

I ended up finding a pretty slick function that has provided a good solution to this issue. I found this function at https://www.mssqltips.com/sqlservertip/2800/sql-server-function-to-return-a-range-of-dates/.

James, thank you very much for your help, however I think I am good to go at least with this problem. I will try to build future questions in a more consumable format going forward.

Cheers!