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_MonthNumberINT
)
RETURNS VARCHAR(9)
AS
BEGIN
RETURN (SELECTCASE @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 (
eventidINT PRIMARY KEY NOT NULL
,eventnameVARCHAR(100) NOT NULL
,startdateDATETIME NOT NULL
,enddateDATETIME NULL
)
INSERT INTO @events (eventid, eventname, startdate, enddate)
SELECT1,'Holiday','2011-01-01 00:00:00','2011-01-08 00:00:00' UNION
SELECT2,'Doctors Appointment','2011-02-07 12:00:00',NULL UNION
SELECT3,'My birthday','2011-03-17 00:00:00',NULL UNION
SELECT4,'Football Practise','2011-04-06 17:30:00','2011-04-06 19:00:00' UNION
SELECT5,'Football Tournament','2011-04-10 10:00:00','2011-04-11 15:00:00'
;WITH cEvents (EventID, EventName, StartDate, EndDate, StartMonth)
AS (
SELECTe.eventid, e.eventname, e.startdate, e.enddate, DATEPART(MONTH,e.startdate) AS StartMonth
FROM@events e
UNION ALL
SELECTe.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)
)
SELECTce.eventid, ce.eventname, ce.startdate, ce.enddate, dbo.f_GetMonthName(ce.StartMonth) AS MonthName
FROMcEvents 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
Change is inevitable... Change for the better is not.
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 (
monthidINT PRIMARY KEY NOT NULL
,monthnameVARCHAR(9)
)
INSERT INTO MONTHS
SELECT1,'January' UNION
SELECT2,'February' UNION
SELECT3,'March' UNION
SELECT4,'April' UNION
SELECT5,'May' UNION
SELECT6,'June' UNION
SELECT7,'July' UNION
SELECT8,'August' UNION
SELECT9,'September' UNION
SELECT10,'October' UNION
SELECT11,'November' UNION
SELECT12,'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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply