how to select fiscal year(s) with a date range?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I have a fiscal year table like following. Now, how to select fiscal year(s) with a date range like '01/15/2015' and '02/20/2015'(generally, the date rang will return the first row,because the days in the range are in fiscal year 2015 (2015-01-01 to 2015-12-31), but how to select it using sql query?)

Moreover, users also should be able to select fiscal years with different date range like '01/15/2012' and '02/20/2015'.

tblFiscalYear:

ID  CompanyId   StartDate       EndDate
5	7	2015-01-01	2015-12-31
6	7	2014-07-01	2014-12-31
7	7	2013-01-01	2013-12-31
8	7	2012-01-01	2012-12-31

Thanks in advance.

Attiq-ul-Islam

Posted 11-Feb-15 18:56pm

Attiq-ul Islam

Updated 11-Feb-15 20:03pm

Maciej Los

v2

Add a Solution

Comments

Maciej Los 12-Feb-15 2:05am

Unclear... ;(

Kornfeld Eliyahu Peter 12-Feb-15 2:09am

A date range can indicate more than one fiscal years (even can indicate the hole table), so by what criteria do you want to pick the the one?

Jeet Gupta 12-Feb-15 2:11am

can you give an example. how you give the date range and on that given data what will be the output you want.

John C Rayan 12-Feb-15 4:56am

Do you expect something like this?
select year(endDate)
from tblFiscalYear
where (StartDate between @start_date and @end_date)
or (endDate between @start_date and @end_date)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Attiq-ul Islam · Accepted Answer · 2015-02-14T18:30:00

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 years

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