?
Solved

Recurrence for Calendar using MS SQL Server

Posted on 2009-02-14
2
Medium Priority
?
420 Views
Last Modified: 2012-05-06
Hi,
I need help =) I'm very, very, very new to Sql (and very, very, very confused),
 so forgive me if I don't say things properly.  

I have a calendar of events and I want to be able to allow a user to input recurring events.  
I've been hunting around to try to figure out the best method to solve this problem.

From searching around on this site, I found some soltions (to different problems)
that used User Defined Functions, so I thought maybe this would be the route that I should take.

My simplified database schema is as follows:
(table Name = dbo.Events)

EventID      int
CustomerID      int
EventTitle      string
BeginningDate      DateTime
EndingDate      DateTime
Reccurence      int

The Recurrence has the following values

Reccurence = 0 :  No reccurence
Reccurence = 1: Event Reccurs on a weekly basis
Reccurence = 2: Event Reccurs on a monthly basis
Reccurence = 3: Event Reccurs on a yearly basis


I need to be able to retrieve all of the events for a customer.  If the event reccurence is 0, then it's
just a normal retrieval, but if it anything else, then I need to populate a table and join it to the
other results, which is why I thought that User Defined Functions would be a good solution.

After fighting with UDFs for the past 3 hours, I ran into soooo many problems.

1) Is it possible to create UDFs from MS Visual Studio 2005? I ask this becasue I couldn't even get the scalar UDF to work.

2) None of the example that I found showed how to pass in a variable from outside the function...kind of like a stored procedure.

what I mean is this

      fEvent(@CustomerID) is the parameter for the UDF

when I call the function in the SELECT STATEMENT

i.e.
      SELECT CustomerID, EventTitle, BeginningDate, EndingDate
      FROM dbo.Events E
      CROSS APPLY fEvent(E.CustomerID)
      ORDER BY BeginningDate


      What if I don't want to hardcode in the E.CustomerID.  It's dependent on which Customer I looking up.
      How can I do this?

3) CROSS comes up as a keyword for me but APPLY doesn't.  Any thougths as to why that is?  I'm using Visual Studio 2005 Professional
and MS Sql Server Express

4) Is this the best method to achieving my goal?  The only other was that I thought of was to have
a seperate entry for each occurence, but this would generate tons of records, and produce a lot of hits
on my database.  


Thank you so much any help!!!!  
0
Comment
Question by:tenderroni992003
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 23646541
1. It will definitely work. Might be there is some other issue in the function

2. Sure. You can Create it. An example below:

CREATE FUNCTION Sales.ufn_CustomerNamesInRegion
                 ( @Region nvarchar(50) )
RETURNS table
AS
RETURN (
        SELECT DISTINCT S.Name AS Store, A.City
        FROM Sales.Store AS S
        JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
        JOIN Person.Address AS A ON A.AddressID = CA.AddressID
        JOIN Person.StateProvince SP ON
        SP.StateProvinceID = A.StateProvinceID
        WHERE SP.Name = @Region
       )
GO
-- Example of calling the function for a specific region
SELECT *
FROM Sales.ufn_CustomerNamesInRegion(N'Washington')
GO

And in your case, Since it is a Scalar value you can also select like

      SELECT CustomerID, EventTitle, BeginningDate, EndingDate,fEvent(E.CustomerID)
      FROM dbo.Events E
      ORDER BY BeginningDate

3. APPLY is also a keyword and it will be used for returning the result sets of Table Valued Functions.

4. If you have problems with Functions, you can try Procedures where in you can implement all sorts of functionality without any hassles.
0
 

Author Closing Comment

by:tenderroni992003
ID: 31547000
Thank you so much for all of your help!  This led me in the right direction. Thank you thank you thank you!!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question