Skip to Content
0
Former Member
Jun 28, 2007 at 11:51 PM

Date Range Parameter File

74 Views

Post Author: agarrett

CA Forum: Data Connectivity and SQL

Hi there,

I have a report that prompts the user to specify a date range, I have to problems:

1) No matter what date range they give it displays results for all days.

2) How do I account for the Daylight Savings Time and the Eastern Time Zone in my report. The database I am pulling the info from is in GMT.

Please help

Here's what I have:

SELECT "Reservations"."General/Host", "Reservations"."General/Booked By", "Reservations"."Actual Start", "Reservations"."Meeting Start", "Reservations"."Actual End", "Reservations"."Meeting End", "Reservations"."DateCreated", "Reservations"."General/#Attending", "Reservations"."General/Select Event Type", "Reservations"."General/After Hours Event", "Rooms"."Meeting Room", "Reservations"."General/Meeting Title", "Reservations"."General/Comments", "Reservations"."General/Meeting Description" FROM "MRM"."dbo"."Rooms" "Rooms" INNER JOIN "MRM"."dbo"."Reservations" "Reservations" ON "Rooms"."Room_ID"="Reservations"."Room_ID" WHERE "Reservations"."General/After Hours Event"=1 ORDER BY "Rooms"."Meeting Room"

The parameter looks at StartDate and End Date.

Regards,

Avi

The support for the application I am using were only able to give a "Command" for one of their reports, I just don't know how to make it work with my report above (Did I mention I am new to this):

"Update Reservations Set [General/Reservation Type] ='' Where [General/Reservation Type] is null

SELECT res.Reservation_ID, res.&#91;General/Meeting Title&#93;, res.&#91;General/Host&#93;, res.Requirements, res.&#91;General/Booked By&#93;, res.&#91;General/Reservation Type&#93;, res.Room_ID, roo.&#91;Meeting Room&#93;, roo.Location_ID, actLoc.&#91;Location Name&#93; AS ActLocName, actLoc.&#91;Time Difference&#93; AS ActualLocTimeDifference, myLoc.&#91;Location Name&#93; AS MyLocName, myLoc.&#91;Time Difference&#93; AS MyLocTimeDifference, (DateAdd(n, (Case When (select actLoc.&#91;Time Difference&#93; - (DSTBias) /60 from locations Where res.&#91;Meeting Start&#93; >= DSTStartTime And res.&#91;Meeting Start&#93; <=DSTEndTime And isDST = 1And Location_ID = actLoc.&#91;Location_ID&#93;) is null then actLoc.&#91;Time Difference&#93; else(select actLoc.&#91;Time Difference&#93; - (DSTBias) /60 from locations Where res.&#91;Meeting Start&#93; >= DSTStartTime And res.&#91;Meeting Start&#93; <=DSTEndTime And isDST = 1And Location_ID = actLoc.&#91;Location_ID&#93;)End) * 60,res.&#91;Meeting Start&#93;)) AS dtActualLocMeetingStart,

(DateAdd(n, (Case When (select actLoc.&#91;Time Difference&#93; - (DSTBias) /60 from locations Where res.&#91;Meeting Start&#93; >= DSTStartTime And res.&#91;Meeting Start&#93; <=DSTEndTime And isDST = 1And Location_ID = actLoc.&#91;Location_ID&#93;) is null then actLoc.&#91;Time Difference&#93; else(select actLoc.&#91;Time Difference&#93; - (DSTBias) /60 from locations Where res.&#91;Meeting Start&#93; >= DSTStartTime And res.&#91;Meeting Start&#93; <=DSTEndTime And isDST = 1And Location_ID = actLoc.&#91;Location_ID&#93;)End ) * 60,res.&#91;Meeting End&#93;)) AS dtActualLocMeetingEnd,

(DateAdd(n, (Case When (select myLoc.&#91;Time Difference&#93; - (DSTBias) /60 from locations Where res.&#91;Meeting Start&#93; >= DSTStartTime And res.&#91;Meeting Start&#93; <=DSTEndTime And isDST = 1And Location_ID = myLoc.&#91;Location_ID&#93;) is null then myLoc.&#91;Time Difference&#93; else(select myLoc.&#91;Time Difference&#93; - (DSTBias) /60 from locations Where res.&#91;Meeting Start&#93; >= DSTStartTime And res.&#91;Meeting Start&#93; <=DSTEndTime And isDST = 1And Location_ID = myLoc.&#91;Location_ID&#93;)End ) * 60 ,res.&#91;Meeting End&#93;)) AS dtMyLocMeetingEnd,

(DateAdd(n, (Case When (select myLoc.&#91;Time Difference&#93; - (DSTBias) /60 from locations Where res.&#91;Meeting Start&#93; >= DSTStartTime And res.&#91;Meeting Start&#93; <=DSTEndTime And isDST = 1And Location_ID = myLoc.&#91;Location_ID&#93;) is null then myLoc.&#91;Time Difference&#93; else(select myLoc.&#91;Time Difference&#93; - (DSTBias) /60 from locations Where res.&#91;Meeting Start&#93; >= DSTStartTime And res.&#91;Meeting Start&#93; <=DSTEndTime And isDST = 1And Location_ID = myLoc.&#91;Location_ID&#93;)End ) * 60,res.&#91;Meeting Start&#93;)) AS dtMyLocMeetingStart

FROM Locations AS myLoc, Reservations AS res INNER JOIN (Rooms AS roo INNER JOIN Locations AS actLoc ON roo.Location_ID = actLoc.Location_ID) ON res.Room_ID = roo.Room_ID

WHERE(actLoc.&#91;Location Name&#93; Like '{?Location to Search}' or Convert(Varchar(30), actloc.&#91;Location_ID&#93;)in ( {?LocationIDList}) )

AND

(DateAdd(n, (Case When (select myLoc.&#91;Time Difference&#93; - (DSTBias) /60 from locations Where res.&#91;Meeting Start&#93; >= DSTStartTime And res.&#91;Meeting Start&#93; <=DSTEndTime And isDST = 1And Location_ID = myLoc.&#91;Location_ID&#93;) is null then myLoc.&#91;Time Difference&#93; else(select myLoc.&#91;Time Difference&#93; - (DSTBias) /60 from locations Where res.&#91;Meeting Start&#93; >= DSTStartTime And res.&#91;Meeting Start&#93; <=DSTEndTime And isDST = 1And Location_ID = myLoc.&#91;Location_ID&#93;)End ) * 60,res.&#91;Meeting Start&#93;)) >= {?StartDate} + ' 00:00:00.000'

AND

(DateAdd(n, (Case When (select myLoc.&#91;Time Difference&#93; - (DSTBias) /60 from locations Where res.&#91;Meeting Start&#93; >= DSTStartTime And res.&#91;Meeting Start&#93; <=DSTEndTime And isDST = 1And Location_ID = myLoc.&#91;Location_ID&#93;) is null then myLoc.&#91;Time Difference&#93; else(select myLoc.&#91;Time Difference&#93; - (DSTBias) /60 from locations Where res.&#91;Meeting Start&#93; >= DSTStartTime And res.&#91;Meeting Start&#93; <=DSTEndTime And isDST = 1And Location_ID = myLoc.&#91;Location_ID&#93;)End ) * 60 ,res.&#91;Meeting End&#93;)) <= {?endDate} + ' 23:59:59.000'

AND ((myLoc.&#91;Location Name&#93;)= '{?My Location}') AND res.&#91;General/Reservation Type&#93; Like '%' + '{?Resource Type}' + '%'

And ( IsSpecialRequest =0 or IsSpecialRequest is Null)

Order by res.&#91;Meeting Start&#93;, actLoc.&#91;Location Name&#93;, Roo.&#91;Meeting Room&#93;"