Hi,

I have a drop down which shows the current month plus the following three. So its November, it'll contain Nov, Dec, Jan and Feb. And that's Jan and Feb from the following year.

Also, I have a table of theatres and a table of theatre shows (which reference the theatre that they're showing on) and each show has a start date and an end date.

Now, given a specific theatre, I need to know which of the months in the drop down, if any, are a month for which the theatre has a show on.

The idea is that the drop down only shows the months for which the selected theatre has a show on.

I can think of a way to do this (and its really inefficient) and that is to goto the database once for each month's date range and say - is there a show on during this month for this cinema? If so, then that's a valid month.

Ideally I would get back a list of valid months from the database in one request.

Any ideas?

Here's how I would approach this.

I would determine the Startdate and Enddate for your 3 month range. Do a query to the db where Shows a between those two months.

Then, I would manually populate a combo control with all the months by interating the shows and adding any months that are not yet in the combo, perhaps you could do this in the event build handler of a grid control or something.

You could do it as two queris. Once to get a collection of records grouped by month/year then one of shows with the date range. I guess it depends on how many shows you are expecting for each date range.

Also, would it be so bad to just display (There are no shows for the month) if a month was selected with no shows in it. Then you don't have to worry about only including months in the range where there was a show.

BOb

pilotboba wrote:

Also, would it be so bad to just display (There are no shows for the month) if a month was selected with no shows in it.

Well that would make it really easy. In fact, it would probably make more sense to the user if there weren't gaps in the months.

I'll see how it goes down with the client.

I'm afraid I will not compromise!

Thus I don't suppose this SP is possible in LLBLGen?

ALTER  PROCEDURE sp_Theatres#GetValidTheatreShowMonth

    @theatreID int

AS

DECLARE @timeSpans TABLE
(
    spanStart DATETIME,
    spanEnd DATETIME
)

DECLARE @nextMonth DATETIME, @today DATETIME

SET @today = CONVERT(varchar(10), GetDate(), 101)
SET @nextMonth = DATEADD(Month, 1, @today)
SET @nextMonth = DATEADD(day, -(DATEPART(day, @nextMonth) - 1), @nextMonth)

INSERT INTO @timeSpans VALUES ( @today, @nextMonth )
INSERT INTO @timeSpans VALUES ( @nextMonth, DATEADD(Month, 1, @nextMonth) )
INSERT INTO @timeSpans VALUES ( DATEADD(Month, 1, @nextMonth), DATEADD(Month, 2, @nextMonth) )
INSERT INTO @timeSpans VALUES ( DATEADD(Month, 2, @nextMonth), DATEADD(Month, 3, @nextMonth) )

SELECT
    DATENAME(m, spanStart) AS 'MonthName', DATEPART(month, spanStart) AS 'MonthID', DATEPART(year, spanStart) AS 'Year'
FROM
    @timeSpans
WHERE
    EXISTS (
        SELECT
            showID
        FROM
            tbl_show_theatre
        WHERE
            startDate <= spanEnd
            AND
            endDate >= spanStart
            AND
            theatreID = @theatreID
        )
ORDER BY
    Year, MonthID

OK here is my question why are you going to the server to do simple date math in the first place?

This is all very easily codable in C#, VB.NET or even VB6, and never hitting a server at all.

Edit: Nevermind I overlooked the sub select to a real table, the first read through I only saw the temp table.

I would do the date math on the client pull back a subset typed list of @theaterid based on the maximum ranges given your dates and write a small client side function to fitler the results to the expected result if I was going to implement it fully in llblgen.

John

Oh I noticed that the end of the time spans needs to be the last day of the month instead of the first day of the next month.

That's a good idea because then it would be easy to cache the date ranges and so not have to keep creating them and also the SQL Server date handling is really cumbersome. I could use a dynamic list to avoid bringing back all of the fields of the shows within the max range and use DataTable.Select to look for a show within a specific month.

Which is quicker though? Re-creating the date ranges at the DB or bringing back some shows to the web server to look through? I suppose if I cached this relatively static data it would be pretty irrelevant which method was used.

Looked at it more, and your proc doesn't really do anything useful as far as I can tell, given today you get (ignoring the end dates are off by 1):

 spanStart                                            spanEnd                                               
------------------------------------------------------ ------------------------------------------------------
2005-09-29 00:00:00.000                             2005-10-01 00:00:00.000
2005-10-01 00:00:00.000                             2005-11-01 00:00:00.000
2005-11-01 00:00:00.000                             2005-12-01 00:00:00.000
2005-12-01 00:00:00.000                             2006-01-01 00:00:00.000

(4 row(s) affected)

In your temp table.

And

 MonthName       MonthID     Year       
------------------------------ ----------- -----------
September                     9        2005
October                       10          2005
November                       11         2005
December                       12         2005

(4 row(s) affected)

Is all that will ever be returned by the actual select given that any actual show is playing at a given theater.

I would think you would just query the tbl_show_theatre given the date range of the above query and want to return the actual show's descriptions, start dates/times of each show for a given theater which would be actual useful information, or the query should include a showid as part of the filter to determine the times for a specific show.

The actual query suplied just says is any show showing which is probably not very useful.

If you do the later, this is very doable in LLBLGen as you can supply date ranges as part of your predicate on the tbl_show_theatre table and apply ordering to come up with a show list for the next period of time.

John

Is all that will ever be returned by the actual select given that **any **actual show is playing at a given theater.

That's not true. There may be a show that starts and ends in September but no show at all which is showing in November. So the November range would be considered invalid and so would not be returned in the set.

The actual query suplied just says is any show showing which is probably not very useful.

You pick a theatre from a drop down, it posts back and populates the months drop down with all of the months for which a show is on at that theatre. Then the user can select a month and press 'Submit' and would then see the theatre's details along with all the show(s) details for the selected month.

For a given theatre and month, there may not actually be a show on so you're preventing the user from submitting the form only to be shown a page which says - "There are no shows on at this theatre this month." And yes some months _are _empty.

How does that sound?

if you need date-voodoo in the db, you might have to use a proc instead of turn to predicates. However, it might be possible to add the datetime functions to the fields by using a custom IExpression implementation with a few lines of code.

Please check this thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

It will give you a general idea what to do. You can extend this if you need more parameters to pass to a function.