March 11, 2011 at 7:51 am
Hi all (again),
I know I've come out from nowhere and been asking all these questions, but honestly this is the best place for SQL-related queries and you guys have been absolute legends so far, so thank you all!
Right, onto my question. I have an events view that retrieves events from a table and joins them using a "counter" table in order to perform some rudimentary "reoccurrence" technique. In the end by running the view I get event name, id, start date and end date. The code below summarises what sort of data I'm retrieving.
--create a function to get the month name
CREATE FUNCTION dbo.f_GetMonthName (
@p_MonthNumber INT
)
RETURNS VARCHAR(9)
AS
BEGIN
RETURN (SELECT CASE @p_MonthNumber
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END)
END
GO
declare @events table (
eventid INT PRIMARY KEY NOT NULL
,eventname VARCHAR(100) NOT NULL
,startdate DATETIME NOT NULL
,enddate DATETIME NULL
)
INSERT INTO @events (eventid, eventname, startdate, enddate)
SELECT 1,'Holiday','2011-01-01 00:00:00','2011-01-08 00:00:00' UNION
SELECT 2,'Doctors Appointment','2011-02-07 12:00:00',NULL UNION
SELECT 3,'My birthday','2011-03-17 00:00:00',NULL UNION
SELECT 4,'Football Practise','2011-04-06 17:30:00','2011-04-06 19:00:00' UNION
SELECT 5,'Football Tournament','2011-04-10 10:00:00','2011-04-11 15:00:00'
;WITH cEvents (EventID, EventName, StartDate, EndDate, StartMonth)
AS (
SELECT e.eventid, e.eventname, e.startdate, e.enddate, DATEPART(MONTH,e.startdate) AS StartMonth
FROM @events e
UNION ALL
SELECT e.eventid, e.eventname, e.startdate, e.enddate, DATEPART(MONTH,e.startdate) AS StartMonth
FROM @events e INNER JOIN
cEvents ce
ON( DATEPART(MONTH,e.startDate) = ce.StartMonth)
)
SELECT ce.eventid, ce.eventname, ce.startdate, ce.enddate, dbo.f_GetMonthName(ce.StartMonth) AS MonthName
FROM cEvents ce
GO
--drop the function again (for test)
IF OBJECT_ID('dbo.f_GetMonthName') IS NOT NULL
DROP FUNCTION dbo.f_GetMonthName
GO
Now the easy part is done, I'd like to retrieve these events in a way that I have a MONTH NAME header and then the list of events for that month below. As you can see, I've tried using a CTE as it seems like it's hierarchical data, but this just causes a MAX RECURSION error.
As always, any and all help is much appreciated.
Thanks in advance,
Kev.
March 11, 2011 at 9:52 am
You don't need that function. SQL Server already has such a function. Try the following code and see.
SELECT DATENAME(mm,GETDATE())
--Jeff Moden
March 11, 2011 at 9:52 am
Ok, so I'm not sure if I'm onto anything, but would it be easier if I maintained a separate MONTHS table, which basically looked like this:
CREATE table MONTHS (
monthid INT PRIMARY KEY NOT NULL
,monthname VARCHAR(9)
)
INSERT INTO MONTHS
SELECT 1,'January' UNION
SELECT 2,'February' UNION
SELECT 3,'March' UNION
SELECT 4,'April' UNION
SELECT 5,'May' UNION
SELECT 6,'June' UNION
SELECT 7,'July' UNION
SELECT 8,'August' UNION
SELECT 9,'September' UNION
SELECT 10,'October' UNION
SELECT 11,'November' UNION
SELECT 12,'December' UNION
That way I could join onto it to make my life easier...? Sorry, such a noob.:blush:
March 11, 2011 at 9:54 am
Jeff Moden (3/11/2011)
You don't need that function. SQL Server already has such a function. Try the following code and see.
SELECT DATENAME(mm,GETDATE())
I was just reading about that before I posted my last message. Agreed, the function would just be a bit of a waste...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply