Month query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Month query

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-09-23 : 08:33:50

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. Furthermore, the months are identified as 1, 2 and so on.

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?

Kristen
Test

22859 Posts

Posted - 2005-09-23 : 08:44:32

Have you got a Tally table? (A table just containing sequential numbers)

If so maybe you could JOIN to that where "NUMBER BETWEEN 1 AND 3" and then add NUMBER on to the Start Month

Kristen

rheitzman
Starting Member

16 Posts

Posted - 2005-09-23 : 17:38:09

How about:

SELECT DISTINCT MONTH(Show_Date).....
WHERE theater & show
ORDER BY Show_Date

With some extra tricks you can get the strings for the month names:

select distinct convert(varchar(3),Show_Date,0), ...

SWAG - not tested

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-09-27 : 18:52:03

I'm not sure how that works rheitzman. Doesn't that query only retrieve the month that the start date is on? I need all of the months that a show spans.

Kristen, I've been thinking about your idea and I can't see how it would work. I managed to join to a tally table only to discover that Dec, for example, is not between Nov and Jan of the following year because, as your idea suggests, 1 is less than 11 and so 12 is not between 1 and 11. But I can't see how to 'normalize' the month values in order to avoid the modulus effect.

My idea currently is to fill a temp table variable with the four different time spans and evaluate each of them using an EXISTS sub-query.

Kristen
Test

22859 Posts

Posted - 2005-09-28 : 00:18:44

"Dec, for example, is not between Nov and Jan"


DECLARE @tally TABLE
(
	MyNumber	int
)

INSERT INTO @tally
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5

SELECT *
FROM	@tally

SELECT	[Now] = GetDate(),
	[Plus tally] = DATEADD(Month, MyNumber, GetDate()),
	[Month of] = SUBSTRING(CONVERT(varchar(24), 
					DATEADD(Month, MyNumber, GetDate()), 113), 
				4, 3)
FROM	@tally
ORDER BY MyNumber

Kristen

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-09-29 : 10:46:34

This is the best I could come up with...

CREATE  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

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-09-29 : 11:19:18

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources