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.