Comments by Attiq-ul Islam (Top 16 by date)

Thanks a lot.

thanks a lot for your kind cooperation.

Deal all,

Thanks all of you for your kind initiatives. However, I solved the problem like following way -

01. I extracted all days of a date range, using following store procedure.

Create PROCEDURE getAllDaysBetweenTwoDate
(
@FromDate DATETIME,
@ToDate DATETIME
)
AS
BEGIN

DECLARE @TOTALCount INT
SET @FromDate = DATEADD(DAY,-1,@FromDate)
Select @TOTALCount= DATEDIFF(DD,@FromDate,@ToDate);

WITH d AS
(
SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER()
OVER (ORDER BY object_id), REPLACE(@FromDate,'-',''))
FROM sys.all_objects
)
SELECT AllDays From d

RETURN
END
GO

*********************************************************
02. Then I used every day to find the distinct fiscal years like following way -

create proc SpGetFiscalYearsByDateRange
(
@StartDate varchar(50),
@EndDate varchar(50),
@CompanyId varchar(20)
)
As
declare @SpName nvarchar(300)
declare @Day date
declare @RowFound int
declare @Count int

BEGIN

set @SpName = 'getAllDaysBetweenTwoDate @FromDate='''+@StartDate+''', @ToDate = '''+@EndDate+'''';
create table #AllDays
(
ID int IDENTITY(1,1) PRIMARY KEY,
oDay datetime
)

insert into #AllDays (oDay)
EXEC sp_executesql @SpName
set @RowFound = @@ROWCOUNT

-- create temp table for storing Fiscal year(s)

create table #FiscalYear
( SYear date,
EYear date
)

declare @sDate varchar(50)
declare @eDate varchar(50)

/*
-- declare cursor

declare @FyCursor cursor
Set @FyCursor = cursor fast_forward
for select oDay from #AllDays
open @FyCursor
fetch NEXT from @FyCursor
into @Day
WHILE @@FETCH_STATUS = 0
BEGIN
select @sDate = StartDate , @eDate = EndDate from tblFiscalYear where CompanyID = @CompanyId and @Day between StartDate and EndDate

IF NOT EXISTS (select 1 from #FiscalYear where SYear = @sDate and EYear = @eDate)
BEGIN
insert into #FiscalYear(SYear,EYear) select StartDate,EndDate from tblFiscalYear where CompanyID = @CompanyId and @Day between StartDate and EndDate
END

fetch NEXT from @FyCursor
END
*/

-- to improve performance I replaced the cursor with while loop

set @Count = 1

while @Count <= @RowFound
BEGIN
select @Day = oDay from #AllDays where ID = @Count

select @sDate = StartDate , @eDate = EndDate from tblFiscalYear where CompanyID = @CompanyId and @Day between StartDate and EndDate

IF NOT EXISTS (select 1 from #FiscalYear where SYear = @sDate and EYear = @eDate)
BEGIN
insert into #FiscalYear(SYear,EYear) select StartDate,EndDate from tblFiscalYear where CompanyID = @CompanyId and @Day between StartDate and EndDate
END
set @Count = @Count +1
END

select distinct SYear, EYear from #FiscalYear

drop table #AllDays
drop table #FiscalYear

END