Advertisement
Advertisement
| 05.13.2008 at 06:20AM PDT, ID: 23397608 |
|
[x]
Attachment Details
|
||
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: 69: 70: |
Below is the stored procedure I am starting with: --------------------------------------------------- I would have two input parameters for a date range and an eventid and a clientid. ------------------------------------------------------------- USE [seMRM76] GO /****** Object: StoredProcedure [dbo].[rptCC_Letter] Script Date: 05/13/2008 08:50:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[rptCC_Letter] ( @StartDate DateTime, @EndDate DATETIME, @clientid INT ) AS BEGIN SET NOCOUNT ON; SELECT ISNULL((a.reservation_id),'') AS resid, ISNULL((a.[general/meeting title]),'') AS title, ISNULL(RTRIM(b.ContactName),'') AS contactname, ISNULL(RTRIM(b.ContactFirstName),' ') + ' ' + ISNULL(RTRIM(b.ContactLastName),' ') AS ClientName, CONVERT(VARCHAR(40),GETDATE(), 107) AS TodaysDate, ISNULL(RTRIM(b.CompanyName),'') AS companyname, ISNULL(RTRIM(b.Address1),'NA') AS address1, ISNULL(RTRIM(b.City),'') + ISNULL(RTRIM(b.State),'') + ISNULL(RTRIM(b.Zip),'') AS address2, DATENAME(weekday, a.[Meeting Start]) + ', ' + DATENAME(MONTH, a.[Meeting Start]) + SUBSTRING(CONVERT(VARCHAR(30), a.[Meeting Start], 107), 4, 20) as meetstart, DATENAME(weekday, a.[Meeting End]) + ', ' + DATENAME(MONTH, a.[Meeting End]) + SUBSTRING(CONVERT(VARCHAR(30), a.[Meeting End], 107), 4, 20) as meetend, ISNULL((a.[General/#Attending]),'') AS attending, right(ISNULL(RTRIM(a.[Meeting Start]),''),7) + '-' + right(ISNULL(RTRIM(a.[Meeting End]),''),7) AS startendName, right(a.[Meeting Start],7) + '-' + right(a.[Meeting End],7) AS meetstartend, CONVERT(char(20),a.[Meeting Start],101) AS startdate, CONVERT(char(20),a.[Meeting End],101) AS enddate, ISNULL ((d.Location_ID),'') AS LocID, ISNULL(RTRIM(d.[Location name]),'') AS locationName, ISNULL((a.[room_id]),'') AS room, ISNULL((b.[phone]),'') AS phone, ISNULL((b.[fax]),'') AS fax, ISNULL((b.[email]),'') AS email, ISNULL((R.[meeting room]),'') AS meetingroom, F.CoordFName + ' ' + F.CoordLName AS Host, E.[EventName] AS eventname FROM Reservations A INNER JOIN Client B ON B.ClientID = A.[general/clientid] LEFT JOIN Rooms R ON R.Room_id = A.Room_id LEFT JOIN Locations D ON D.Location_ID = R.Location_ID LEFT JOIN vw_events E ON E.eventid = A.[general/eventid] LEFT JOIN vw_Coordinators F ON F.coordinatorid = E.coordinatorid WHERE A.[General/ClientID] = @clientid AND (A.[actual start] BETWEEN @StartDate AND @EndDate) AND (D.[Location Name] = 'Convention Center') --AND A.[actual start] > getdate() --order by a.[Meeting Start],R.[meeting room] END |