Next Coming BirthDay - 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 2008 Forums

Transact-SQL (2008)

Next Coming BirthDay

Author

Topic

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-11-29 : 19:41:35

Hello,

I would like to arrange days by the next upcoming birthday (current date [30/11/2014]).

name birthday
----------------------
yannis 1979-06-29
natalia 1980-08-19
kostas 1983-12-27
christos 1979-07-22
kosmas 1978-04-28

Next coming Birthday, sorted

kostas 1983-12-27
kosmas 1978-04-28
yannis 1979-06-29
christos 1979-07-22
natalia 1980-08-19

select
*
from
( SELECT rtrim(c.lastname) + ' ' + rtrim(c.firstname) AS Employee_name,
c.DateOfBirth,
MONTH(c.DateOfBirth) AS m,
DAY(c.DateOfBirth) As d
from dbo.Employees c
) AS tmp

I tried to order as follows as this would have achieved it i think ? ... but it errors and i don't think its possible to sort values as follows ?
--order by
--(tmp.m,tmp.m) < ( MONTH(CURDATE()), DAY(CURDATE()))

Help please, any alternative approach .. i know i am close, i think lol

Thanks in advance

Marcus

I learn something new everyday.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-12-01 : 06:32:24

[code]
-- *** Test Data ***
CREATE TABLE #t
(
EmpName varchar(20) NOT NULL
,Birthday date NOT NULL
);
INSERT INTO #t
VALUES ('yannis', '19790629')
,('natalia', '19800819')
,('kostas', '19831227')
,('christos', '19790722')
,('kosmas', '19780428');
-- *** End Test Data ***

SELECT *
FROM #t
ORDER BY
CASE
WHEN DATEADD(YEAR, 1900 - YEAR(Birthday), Birthday) < DATEADD(YEAR, 1900 - YEAR(CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
THEN DATEADD(YEAR, 1901 - YEAR(Birthday), Birthday)
ELSE DATEADD(YEAR, 1900 - YEAR(Birthday), Birthday)
END;
[/code]

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-12-01 : 13:48:18

An alternative:


CREATE TABLE #t
(
	EmpName varchar(20) NOT NULL
	,Birthday date NOT NULL
);
INSERT INTO #t
VALUES ('yannis', '19790629')
	,('natalia', '19800819')
	,('kostas', '19831227')
	,('christos', '19790722')
	,('kosmas', '19780428')
	,('kosmas', '20120229')
-- *** End Test Data ***

SELECT *
FROM #t
CROSS JOIN (
    SELECT MONTH(CURRENT_TIMESTAMP) * 100 + DAY(CURRENT_TIMESTAMP) AS current_mmdd
) AS assign_alias_names1
CROSS APPLY (
    SELECT MONTH(Birthday) * 100 + DAY(Birthday) AS birthday_mmdd        
) AS assign_alias_names2
ORDER BY 
	birthday_mmdd + CASE WHEN current_mmdd > birthday_mmdd THEN 1200 ELSE 0 END

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-12-01 : 18:15:12

Damm , how do u guys do it .. Top Class !!

Marcus

I learn something new everyday.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources