date to season - 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

General SQL Server Forums

New to SQL Server Programming

date to season

Author

Topic

pureclass85
Starting Member

29 Posts

Posted - 2009-03-10 : 16:44:34

basicly i have one table (lets say table A) that has dates of a events

and a second table which is a season table which is like this:

seasons VALUES('winter','12');
repeating though every month

i have been trying to find the SQL statements required to implement a function named season that takes a date, and uses the season table to return the season of the year.

i have a table b as well which contains a list of expected events but there is no date just the season it is expected

then after this i need to compare the date of table a , get the season from the season table and then compare the season and to find
SQL query that gives the names of events where they have been observed in summer where there is no expectation for them to be observed during that season.

just to make clear:
table a has dates of events
there is a seasons table that contains season and month number
table b has expected events and the season

i am tring to create a function that that takes a date, and uses the season table to return the season of the year.

and then give the names of events where they have been observed in summer where there is no expectation for them to be observed during that season.

i would be greatfull of any pointers

thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-10 : 17:26:05

You have explained it well...but you also need to provide some sample data and expected output for a correct solution to be provided.

pureclass85
Starting Member

29 Posts

Posted - 2009-03-10 : 17:36:24

sample data:

here is one record from each table
table a ('stage event','2002/02/02','France','information on event.');
seasons ('spring', '4');
table b ('indoor event','France','spring','expected information');

thanks

i was thinking for comparing table a with seasons prob this code
EXTRACT(MONTH from tableA.date)
but not sure quite how to use it

pureclass85
Starting Member

29 Posts

Posted - 2009-03-11 : 06:24:37

i understand this isnt very easy, thats why i am stuck. but if you are good enough i would really be greatful of any pointers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 06:46:24

[code]DECLARE @TableA TABLE
(
col1 VARCHAR(40),
col2 DATETIME,
col3 VARCHAR(40),
col4 VARCHAR(40)
)

INSERT @TableA
SELECT 'stage event', '2002/04/02', 'France', 'information on event.'

DECLARE @Seasons TABLE
(
col1 VARCHAR(40),
col2 INT
)

INSERT @Seasons
SELECT 'spring', 4

DECLARE @TableB TABLE
(
col1 VARCHAR(40),
col2 VARCHAR(40),
col3 VARCHAR(40),
col4 VARCHAR(40)
)

INSERT @TableB
SELECT 'indoor event', 'France', 'spring', 'expected information'

SELECT *
FROM @TableA AS a
INNER JOIN @Seasons AS s ON s.Col2 = DATEPART(MONTH, a.Col2)[/code]

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

pureclass85
Starting Member

29 Posts

Posted - 2009-03-11 : 07:16:18

i am using my sql and i cannot seam to get that to work peso. i have the table already
first:
i am tring to create a function that that takes a date and uses the season table to return the season of the year.

second:
and then give the names of events where they have been observed in summer where there is no expectation for them to be observed during that season.

thanks peso i am looking at inner joins

pureclass85
Starting Member

29 Posts

Posted - 2009-03-11 : 07:29:46

thanks peso managed to use inner join to display date and corrosponding season

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-11 : 07:29:48

Here's a season function so no need for a season table:
CREATE Function dbo.fnSeason (@D INT)
--Takes month number and returns the season name
RETURNS varchar(50)
AS

BEGIN
declare @S varchar (50)
IF @D >1 and @D<=4
set @S='Spring'
Else
IF @D >4 and @D<=7
set @S='Summer'
Else
IF @D >7 and @D<=10
set @S='Autumn'
Else
set @S='Winter'
RETURN @S
END

-----------------------------------------
--USAGE
--Events occurring in wrong season:
-----------------------------------------

SELECT [Date]
,TableA.[Event],dbo.fnSeason(month(TableA.[Date])) AS MonthOfEvent
FROM TableA
Join
dbo.TableB
on TableA.[Event] = TableB.[Event]

where dbo.fnSeason(month([Date]))<>TableB.Season
GO

pureclass85
Starting Member

29 Posts

Posted - 2009-03-11 : 07:38:23

i have manged to get a table now displayed with table a listed with the correct season thanks to peso i now need to compare this with table b diplaying any event that was not expected in table b that was recorded in table A in summer

p.s i have to keep the seasons table as it is used for other things but good idea and thanks

pureclass85
Starting Member

29 Posts

Posted - 2009-03-11 : 08:59:31

thanks dark dusky that function just came in very usefull thanks!!

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-12 : 05:30:46

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources