Year wise dealer info using cursor

Transact-SQL
1

Hi Team,

I have the dealer information for the automobile policy, I need to fetch it yearly wise

I will create a Temp Table that may be help to understand it better

CREATE TABLE #DealerRecords
(
EnrollID VARCHAR(5),
StartDate VARCHAR(12),
EndDate VARCHAR(12),
DealerID VARCHAR(5)
)

INSERT INTO #DealerRecords
SELECT 'E1', '01/01/2014', '08/31/2014', 'D001'
UNION ALL
SELECT 'E1', '09/01/2014', '07/31/2015', 'D001'
UNION ALL
SELECT 'E2', '02/01/2015', '12/31/2016', 'D001'
UNION ALL
SELECT 'E3', '02/01/2016', '05/31/2016', 'D001'
UNION ALL
SELECT 'E3', '08/01/2016', '12/31/2019', 'D002' -- GAP between previous End Date and Current Start Date
UNION ALL
SELECT 'E4', '08/01/2014', '12/31/2015', 'D001'
UNION ALL
SELECT 'E4', '02/01/2016', '5/31/2016', 'D002' -- GAP between previous End Date and Current Start Date
UNION ALL
SELECT 'E5', '01/01/2015', '12/31/2078', 'D001'

The Objective is i have to fetch the dealers record in yearly wise.

Please find below expected output , Table format.. Copy and pasted from Excel so may be layout different, But i need in the regular table format only
Enroll ID Year StartDate EndDate DealerID

EnrollID
Year
StartDate
EndDate
DealerID

E1
2014
01/01/2014
12/31/2014
D001

E1
2015
01/01/2015
07/31/2015
D001

E2
2015
02/01/2015
12/31/2015
D001

E2
2016
01/01/2016
12/31/2016
D001

E3
2016
02/01/2016
05/31/2016
D001

E3
2016
08/01/2016
12/31/2019
D002

E4
2014
08/01/2014
12/31/2014
D001

E4
2015
01/01/2015
12/31/2015
D001

E4
2016
02/01/2016
05/31/2016
D002

E5
2015
01/01/2015
12/31/2015
D001

E5
2016
01/01/2016
12/31/2078
D001

We have to pull report upto current year only, So if the End Date is greater than the Current Year just pull up to current year.

This is what i came up with so far,

CREATE TABLE #DealerRecords_Yearly
(
EnrollID VARCHAR(5),
[Year] VARCHAR(4),
StartDate VARCHAR(12),
EndDate VARCHAR(12),
DealerID VARCHAR(5)
)

DECLARE @varEnrollID VARCHAR(5)
DECLARE @varStartDate VARCHAR(12)
DECLARE @varEndDate VARCHAR(12)
DECLARE @varDealerID VARCHAR(5)

DECLARE @Yearly_Cursor CURSOR
SET @Yearly_Cursor = CURSOR FOR
SELECT EnrollID, StartDate, EndDate, DealerID
FROM #DealerRecords
ORDER BY EnrollID, EndDate

OPEN @Yearly_Cursor

FETCH NEXT FROM @Yearly_Cursor INTO @varEnrollID, @varStartDate,@varEndDate,@varDealerID

WHILE @@FETCH_STATUS = 0
BEGIN
-- DO PROCESS
FETCH NEXT FROM @Yearly_Cursor INTO @varEnrollID, @varStartDate,@varEndDate,@varDealerID

END

CLOSE @Yearly_Cursor
DEALLOCATE @Yearly_Cursor

I really appreciate all your support, your time really appreciated. please let me know if you need more details.

3

Your dates need to be datatype DATE, not VARCHAR(12), and then you can do something like:

; WITH X AS
(
-- ** There are smarter ways to get a list of values than this
--	This is just to give the idea!
	SELECT	[YearStart] = CONVERT(date, '20140101'), [YearEnd] = CONVERT(date, '20141231')
	UNION ALL
	SELECT	CONVERT(date, '20150101'), CONVERT(date, '20151231')
	UNION ALL
	SELECT	CONVERT(date, '20160101'), CONVERT(date, '20161231')
),
Y AS
(
SELECT	EnrollID,
	DealerID,
	MIN(StartDate) AS StartDate,
	MAX(EndDate) AS EndDate
FROM	#DealerRecords
GROUP BY EnrollID, DealerID
)
SELECT	EnrollID,
	[Year] = YEAR(X.YearStart),
	StartDate,
	EndDate,
	DealerID
FROM	X
	JOIN Y
		 ON Y.StartDate <= X.YearEnd
		AND Y.EndDate >= X.YearStart
--
ORDER BY EnrollID, [Year]

It needs a bit of work to aggregate records with contiguous End/Start dates (so that the results show the true, merged, start date rather than just the start-date for the matched transaction), but hopefully is something for you to work with.

My advice is to avoid using a CURSOR at all costs ...