Periods/years query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Periods/years query

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-04-01 : 04:53:08

I have a table

Status Period Year
Open 12 2007
Closed 11 2007
Closed 12 2007
Open 1 2008
Open 3 2008
Open 2 2008

How can I search for rows with a status of 'open' between 2007 period 11 and 2008 period 2 please?

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-04-01 : 04:56:38

convert the period and year to date and use between to check

between '01/11/2007' and 01/03/2008 if u want to avoid 01/03 then use > & < operator

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-01 : 04:57:42

[code]DECLARE @Sample TABLE
(
Status VARCHAR(12),
Period TINYINT,
Year SMALLINT
)

INSERT @Sample
SELECT 'Open', 12, 2007 UNION ALL
SELECT 'Closed', 11, 2007 UNION ALL
SELECT 'Closed', 12, 2007 UNION ALL
SELECT 'Open', 1, 2008 UNION ALL
SELECT 'Open', 3, 2008 UNION ALL
SELECT 'Open', 2, 2008

SELECT *
FROM @Sample
WHERE 100 * Year + Period BETWEEN 200711 AND 200802[/code]

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

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-04-01 : 05:41:25

Many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-01 : 06:37:13

SELECT	*
FROM	@Sample
WHERE	dateadd(month,period-1,dateadd(year,[year]-1900,0))>=dateadd(month,11-1,dateadd(year,2007-1900,0)) and
        dateadd(month,period-1,dateadd(year,[year]-1900,0))<dateadd(month,2,dateadd(year,2008-1900,0))

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources