using CASE to split out a project lifespan into multiple year entries

Question

I have a budget table that lists projects, each with a number of entries listed by project ID, with individual start and end dates and a value.

I'm trying to create an additional table (or a view, but I'm not sure that's possible) which duplicates each row with a new entry for each year that the start date and end date encompasses, along with a simple sum to divide the line value by the number of days in that year. The sum bit works fine, and is not included here.

My problem is, I'm planning on inserting the new entries using the following code. However, this only works for one year and need to essentially run it five times to enter five different years into the same column. How would I go about inserting five years in one go?

declare @1415s datetime;
set @1415s = '2014-08-01';
declare @1415e datetime;
SET @1415e = '2015-07-31';

INSERT INTO [test]
(P_ID,startdate,enddate,Academic_Year)
SELECT
    b.P_ID
    CAST(b.Start_Date AS DATE) AS 'startdate',
    CAST(b.End_Date AS DATE) AS 'enddate',
    'Academic_Year'= CASE
        WHEN startdate <= @1415s AND enddate <= @1415e AND enddate >= @1415s THEN '14/15'
        WHEN startdate >= @1415s AND enddate <= @1415e THEN '14/15'
        WHEN startdate <= @1415s AND enddate >= @1415e THEN '14/15'
        WHEN startdate >= @1415s AND enddate >= @1415e AND startdate <= @1415e THEN '14/15'
        END
FROM Budget b

Possible duplicate of [How To Declare Input-Output Parameters In SQL Server Stored Procedure/Function?](https://stackoverflow.com/questions/49129536/how-to-declare-input-output-parameters-in-sql-server-stored-procedure-function) — pringi, Oct 02 '19 at 14:12
One way to do it, short form: create a temp table, populate it with all the start/end date pairs you are interested in, then modify your current query to JOIN on that table. — Philip Kelley, Oct 02 '19 at 14:20
I've proposed an answer below, but want to caution/encourage you to post questions in as simple and general terms as possible, per community guidelines, to make them more useful to others, more answerable and because StackOverflow is not here to solve specific programming requirements. — John Joseph, Oct 02 '19 at 15:09
@JohnJoseph Fair comment! I'll keep it in mind for future. New user, and all that :) Also, reviewing your post. It's a little more complicated as my "years" are offset from normal years, but I think I've got something together based on your post. — Nmcg, Oct 02 '19 at 15:45
@nmcg, you're new but you must be able to see the problem here. You should mark my response as an answer, clean up or improve your question, or provide the solution you implemented (maybe all three) You're keeping the value for yourself. If everyone did this, there'd be no stackoverflow. It's about community. — John Joseph, Oct 03 '19 at 01:31
@John, I'll do all three in good time when I get a moment. Is the lecturing tone strictly necessary? — Nmcg, Oct 03 '19 at 09:35

score 0 · Accepted Answer · answered Oct 02 '19 at 15:06

This can be accomplished by creating and cross-joining a table with every year in it, say as a smallint.

tYears.Yr

2016 2017 2018 2019

...then cross join that to your source table...

    select srctable.SomeStartDate, srctable.SomeEndDate, tYears.Yr from srctable
    cross join tYears
    where year(SomeStartDate) >= Yr and year(SomeEndDate) <= Yr

This will create one row for every source row for every year that that source row's lifespan covers.

using CASE to split out a project lifespan into multiple year entries

1 Answers1

tYears.Yr