December 21, 2011 at 10:01 am
I have a report with a Project parameter. Based on that project selection, the FiscalYear parameter drop-down needs to show all fiscal years between the Project Start Date and Project End Date. The below query only gets me a list of start year and end year by project, but I need to show all years between start year and end year by project.
create table #Table1 (ProjectID int, [Attribute Name] nvarchar(50), DateTimeValue datetime)
insert into #Table1
select 1,'Start Date','8/8/2008'
union all
select 1,'End Date','4/30/2011'
union all
select 2,'Start Date','10/1/2011'
union all
select 2,'End Date','9/30/2014'
select distinct
ProjectID,
CASE WHEN DATEPART(MONTH,DateTimeValue) >=10 THEN DATEPART(YEAR,DateTimeValue)+1 ELSE DATEPART(YEAR,DateTimeValue) END as FiscalYear
from #Table1
where
[Attribute Name] in ('Start Date','End Date')
So if a project spans 2008 - 2011, the drop-down will only show 2008 and 2011 values. How do I get the years in between to display?
December 21, 2011 at 3:49 pm
I figured it out. Here's the code:
create table #Table1 (ProjectID int, [Attribute Name] nvarchar(50), DateTimeValue datetime)
insert into #Table1
select 1,'Start Date','8/8/2008'
union all
select 1,'End Date','4/30/2011'
union all
select 2,'Start Date','10/1/2011'
union all
select 2,'End Date','9/30/2014'
;with yearlist as
(
select
ProjectID,
CASE WHEN DATEPART(MONTH,DateTimeValue) >=10 THEN DATEPART(YEAR,DateTimeValue)+1 ELSE DATEPART(YEAR,DateTimeValue) END as FiscalYear
from #Table1
where
[Attribute Name] in ('Start Date')
union all
select
y1.ProjectID,
y1.FiscalYear + 1 as FiscalYear
from yearlist y1
inner join
(
select
ProjectID,
CASE WHEN DATEPART(MONTH,DateTimeValue) >=10 THEN DATEPART(YEAR,DateTimeValue)+1 ELSE DATEPART(YEAR,DateTimeValue) END as FiscalYear
from #Table1
where
[Attribute Name] in ('End Date')
) ey
ON
y1.ProjectID=ey.ProjectID
where
y1.FiscalYear + 1 <= ey.FiscalYear
)
select distinct ProjectID,Fiscalyear from yearlist order by ProjectID,FiscalYear desc;
drop table #Table1
December 21, 2011 at 8:43 pm
MattieMich7 (12/21/2011)
I figured it out. Here's the code:
create table #Table1 (ProjectID int, [Attribute Name] nvarchar(50), DateTimeValue datetime)insert into #Table1
select 1,'Start Date','8/8/2008'
union all
select 1,'End Date','4/30/2011'
union all
select 2,'Start Date','10/1/2011'
union all
select 2,'End Date','9/30/2014'
;with yearlist as
(
select
ProjectID,
CASE WHEN DATEPART(MONTH,DateTimeValue) >=10 THEN DATEPART(YEAR,DateTimeValue)+1 ELSE DATEPART(YEAR,DateTimeValue) END as FiscalYear
from #Table1
where
[Attribute Name] in ('Start Date')
union all
select
y1.ProjectID,
y1.FiscalYear + 1 as FiscalYear
from yearlist y1
inner join
(
select
ProjectID,
CASE WHEN DATEPART(MONTH,DateTimeValue) >=10 THEN DATEPART(YEAR,DateTimeValue)+1 ELSE DATEPART(YEAR,DateTimeValue) END as FiscalYear
from #Table1
where
[Attribute Name] in ('End Date')
) ey
ON
y1.ProjectID=ey.ProjectID
where
y1.FiscalYear + 1 <= ey.FiscalYear
)
select distinct ProjectID,Fiscalyear from yearlist order by ProjectID,FiscalYear desc;
drop table #Table1
That's a "Counting Recursive CTE". Please see the following article for why not to use such a thing.
http://www.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2011 at 8:52 pm
Jeff Moden (12/21/2011)
MattieMich7 (12/21/2011)
I figured it out. Here's the code:
create table #Table1 (ProjectID int, [Attribute Name] nvarchar(50), DateTimeValue datetime)insert into #Table1
select 1,'Start Date','8/8/2008'
union all
select 1,'End Date','4/30/2011'
union all
select 2,'Start Date','10/1/2011'
union all
select 2,'End Date','9/30/2014'
;with yearlist as
(
select
ProjectID,
CASE WHEN DATEPART(MONTH,DateTimeValue) >=10 THEN DATEPART(YEAR,DateTimeValue)+1 ELSE DATEPART(YEAR,DateTimeValue) END as FiscalYear
from #Table1
where
[Attribute Name] in ('Start Date')
union all
select
y1.ProjectID,
y1.FiscalYear + 1 as FiscalYear
from yearlist y1
inner join
(
select
ProjectID,
CASE WHEN DATEPART(MONTH,DateTimeValue) >=10 THEN DATEPART(YEAR,DateTimeValue)+1 ELSE DATEPART(YEAR,DateTimeValue) END as FiscalYear
from #Table1
where
[Attribute Name] in ('End Date')
) ey
ON
y1.ProjectID=ey.ProjectID
where
y1.FiscalYear + 1 <= ey.FiscalYear
)
select distinct ProjectID,Fiscalyear from yearlist order by ProjectID,FiscalYear desc;
drop table #Table1
That's a "Counting Recursive CTE". Please see the following article for why not to use such a thing.
I appreciate the heads up on this. What approach would you suggest for my specific scenario?
December 21, 2011 at 9:04 pm
This is what I'd likely do, instead.
WITH
cteNormalizeData AS
(
SELECT ProjectID,
StartYear = MAX(CASE WHEN [Attribute Name] = 'Start Date' THEN DATEPART(yy,DateTimeValue) ELSE 0 END),
EndYear = MAX(CASE WHEN [Attribute Name] = 'End Date' THEN DATEPART(yy,DateTimeValue) ELSE 0 END)
FROM #Table1
GROUP BY ProjectID
)
SELECT ProjectID,
FiscalYear = StartYear+t.N
FROM dbo.Tally t,
cteNormalizeData
WHERE t.N >=0 AND t.N <= EndYear-StartYear
;
If you don't know how a Tally Table works (this one is a zero based Tally Table) to replace certain forms of loops and recursion, please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply