Function
Use Northwind
Go
CREATE FUNCTION Getstar (@ datetime)
RETURNS varchar (100)
As
BEGIN
--just one sentence of SQL
Return
(
--declare @ datetime
--set @ = getdate ()
Select Max (Star)
From
(
--the constellation, the starting date of the constellation, the day of the constellation start
Select ' Capricorn ' as star,1 as [month],1 as [day]
UNION ALL select ' Aquarius ', 1,20
UNION ALL select ' Pisces ', 2,19
UNION ALL SELECT ' Shepherd's Seat ', 3,21
UNION ALL SELECT ' Taurus ', 4,20
UNION ALL SELECT ' Gemini ', 5,21
UNION ALL SELECT ' Cancer ', 6,22
UNION ALL select ' Leo ', 7,23
UNION ALL select ' Virgo ', 8,23
UNION ALL SELECT ' Libra ', 9,23
UNION ALL SELECT ' Scorpio ', 10,24
UNION ALL select ' Sagittarius ', 11,22
UNION ALL select ' Capricorn ', 12,22
) stars
where DATEADD (Day,[day]-1,dateadd (Month,[month]-1,dateadd (Year,datediff (year,0,@), 0))
=
(
Select Max (DATEADD (Day,[day]-1,dateadd month,[month]-1,dateadd (Year,datediff (year,0,@), 0)))
From
(
Select ' Capricorn ' as star,1 as [month],1 as [day]
UNION ALL select ' Aquarius ', 1,20
UNION ALL select ' Pisces ', 2,19
UNION ALL SELECT ' Shepherd's Seat ', 3,21
UNION ALL SELECT ' Taurus ', 4,20
UNION ALL SELECT ' Gemini ', 5,21
UNION ALL SELECT ' Cancer ', 6,22
UNION ALL select ' Leo ', 7,23
UNION ALL select ' Virgo ', 8,23
UNION ALL SELECT ' Libra ', 9,23
UNION ALL SELECT ' Scorpio ', 10,24
UNION ALL select ' Sagittarius ', 11,22
UNION ALL select ' Capricorn ', 12,22
) stars
where @ >= DateAdd (Day,[day]-1,dateadd (Month,[month]-1,dateadd (Year,datediff (year,0,@), 0))
)
)
End
Go
--Test
Use Northwind
Select Dbo.getstar (Birthdate), COUNT (*)
From Employees
Group by Dbo.getstar (birthdate)
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.