如何选择具有日期范围的会计年度？ [英] how to select fiscal year(s) with a date range?

问题描述

我有一个财政年度表，如下所示。现在，如何选择日期范围如'01 / 15/2015'和'02/20/2015'的会计年度（通常，日期范围将返回第一行，因为该范围内的日期是2015财年（2015-01-01至2015-12-31），但如何使用sql查询选择它？）



此外，用户也应该能够选择具有不同日期范围的会计年度，例如' 01/15/2012 '和' 02/20/2015 ' 。



tblFiscalYear：

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

提前致谢。



Attiq-ul-Islam

Thanks in advance.

Attiq-ul-Islam

推荐答案

全部交易，



谢谢大家你善意的举措。但是，我按照以下方式解决了问题 -



01.我使用以下商店程序提取了日期范围的所有日子。



创建程序getAllDaysBetweenTwoDate

（

@FromDate DATETIME，

@ToDate DATETIME

）

AS

BEGIN



DECLARE @TOTALCount INT

SET @ FromDate = DATEADD（DAY，-1，@ FromDate）

选择@ 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



返回

结束

GO



** ************************************ *******************

02.然后我每天都用以下方式找到不同的财政年度 -



创建proc SpGetFiscalYearsByDateRange

（

@StartDate varchar（50），

@EndDate varchar（50 ），

@CompanyId varchar（20）

）

As

声明@SpName nvarchar（300）

声明@Day日期

声明@RowFound int

声明@Count int



BEGIN



设置@SpName ='getAllDaysBetweenTwoDate @FromDate ='''+ @ StartDate +'''，@ ToDate ='''+ @ EndDate +'''' ;

创建表#AllDays

（

ID int IDENTITY（1,1）PRIMARY KEY，

oDay datetime

）



插入#AllDays（oDay）

EXEC sp_executesql @SpName

设置@RowFound = @@ ROWCOUNT



- 创建用于存储财政年度的临时表



创建表#FiscalYear

（SYear date，

EYear date

）



声明@sDate varchar（50）

声明@eDate varchar（50）



/ *

- 声明光标



声明@FyCursor光标

设置@FyCursor =光标fast_forward

来自#AllDays的选择oDay < br $> b $ b打开@FyCursor

从@FyCursor获取NEXT

到@Day

WHILE @@ FETCH_STATUS = 0

BEGIN

选择@sDate = StartDate，@ eDate =来自tblFiscalYear的EndDate，其中CompanyID = @CompanyId和@Day在StartDate和EndDate之间



如果不是EXISTS（从#FiscalYear中选择1，其中SYear = @sDate和EYear = @eDate）

BEGIN

插入#FiscalYear（SYear，EYear） ）从tblFiscalYear中选择StartDate，EndDate，其中CompanyID = @CompanyId和@Day在StartDate和EndDate之间

END



从中获取NEXT @FyCursor

END

* /



- 提高性能我用while循环替换了光标< br $>


设置@Count = 1



而@Count< = @RowFound

BEGIN

选择@Day = oDay来自#AllDays，其中ID = @Count



选择@sDate = StartDate，@ eDate = EndDate来自tblFiscalYear，其中CompanyID = @CompanyId和@Day在StartDate和EndDate之间



如果不是EXISTS（从#FiscalYear中选择1，其中SYear = @sDate和EYear = @eDate）

BEGIN

插入#FiscalYear（SYear，EYear）从tblFiscalYear选择StartDate，EndDate，其中CompanyID = @CompanyId和@Day在StartDate和EndDate之间

END

set @Count = @Count +1

END



选择不同的SYear，EYear来自#FiscalYear



drop table #AllDays

drop table #FiscalYear



END

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

这篇关于如何选择具有日期范围的会计年度？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！