Date Range falls into Certain Year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Date Range falls into Certain Year

Author

Topic

m_funky
Starting Member

5 Posts

Posted - 2012-04-03 : 03:13:59

I require code to do the following:

I have member data, eg member 001 has a startdate of 2011-01-01 and an enddate of 999-12-31 (future dated thus currently an active member). I need to check if members are active for a certain year. Using a '1' if the member was active during that year and a '0' if not.

See example below ..

This is what my results should look like ..

member | startdate | enddate | 2010 | 2011 | 2012 |
001 | 2010-01-01 | 9999-12-31 | 1 | 1 | 1 |
002 | 2011-12-01 | 2011-12-31 | 0 | 1 | 0 |
003 | 2010-05-15 | 2011-01-31 | 1 | 1 | 0 |
004 | 2012-04-02 | 9999-12-31 | 0 | 0 | 1 |
005 | 2010-09-20 | 2010-11-23 | 1 | 0 | 0 |

Please Help | Thank You

m_funky
Starting Member

5 Posts

Posted - 2012-04-03 : 03:16:27

Oh yes ..

I need code to calculate the values of the 0/1 in the year (2010/2011/2012) fields.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-03 : 03:32:28

[code]
case when dateadd(year, [year] - year(startdate), startdate)
between startdate and enddate
then 1
else 0
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

m_funky
Starting Member

5 Posts

Posted - 2012-04-03 : 03:46:18

Hi Khtan

Thank you for your help :)

I used the following code:
case when dateadd(year, 2010 - year(ProdEffDate), ProdEffDate) between ProdEffDate and ProdEndDate then 1 else 0 end [2010],
case when dateadd(year, 2011 - year(ProdEffDate), ProdEffDate) between ProdEffDate and ProdEndDate then 1 else 0 end [2011],
case when dateadd(year, 2012 - year(ProdEffDate), ProdEffDate) between ProdEffDate and ProdEndDate then 1 else 0 end [2012]

I then get the following result for a member which is active in 2010:

member | startdate | enddate | 2010 | 2011 | 2012 |
001 | 2009/07/01 | 2010/02/28 | 0 | 0 | 0 |

How can I also use the enddate as part of my calculation?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-03 : 03:57:36

case when '20101231' between ProdEffDate and ProdEndDate then 1 else 0 end [2010],
case when '20111231' between ProdEffDate and ProdEndDate then 1 else 0 end [2011],
case when '20121231' between ProdEffDate and ProdEndDate then 1 else 0 end [2012],

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-03 : 04:08:24

[code]DECLARE @Sample TABLE
(
Member INT NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL
)

INSERT @Sample
VALUES (001, '2010-01-01', '9999-12-31'),
(002, '2011-12-01', '2011-12-31'),
(003, '2010-05-15', '2011-01-31'),
(004, '2012-04-02', '9999-12-31'),
(005, '2010-09-20', '2010-11-23')

-- Solution by SwePeso
;WITH cteSource(Member, theYear, IsActive)
AS (
SELECT s.Member,
DATEPART(YEAR, s.StartDate) + v.Number AS theYear,
1 AS IsActive
FROM @Sample AS s
INNER JOIN master.dbo.spt_values AS v ON v.Type = 'P'
AND v.Number BETWEEN 0 AND DATEDIFF(YEAR, StartDate, CASE WHEN EndDate = '99991231' THEN GETDATE() ELSE EndDate END)
)
SELECT p.Member,
ISNULL(p.[2010], 0) AS [2010],
ISNULL(p.[2011], 0) AS [2011],
ISNULL(p.[2012], 0) AS [2012]
FROM cteSource AS s
PIVOT (
MAX(s.IsActive)
FOR s.theYear IN ([2010], [2011], [2012])
) AS p[/code]

N 56°04'39.26"
E 12°55'05.63"

m_funky
Starting Member

5 Posts

Posted - 2012-04-03 : 04:09:53

Hi SwePeso

What if the Member has a startdate of 2009/07/01 and an enddate of 2010/02/28?

Then the code [case when '20101231' between ProdEffDate and ProdEndDate then 1 else 0 end [2010]] would not work because 2010/12/31 does not fall between 2009/07/01 and 2010/02/28.

How can I adjust the code to cater for this?

m_funky
Starting Member

5 Posts

Posted - 2012-04-03 : 04:34:51

Than you SwePeso!

Perfect!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources