January 26, 2009 at 4:45 pm
I need to display a schedule of classes between 2 periods on a monthly basis. Each location only has a max of 1 class per month. However, some locations do not any classes for a particular month.
Is there a way that I can have a dummy row showing in the query result for the locations that do not have a class in a month?
Example of the dummy row:
locationID = 31
CourseDate = 2009-01-01 00:00:00
Course_Number = 105
Location_Name = Pasadena
Now, my query displays the results but of course it skips the month with no classes:
SELECT Course_Schedule.LocationID, Course_Schedule.CourseDate, Course_Schedule.Course_Number, Location.Location_Name
FROM Course_Schedule INNER JOIN
Location ON Course_Schedule.LocationID = Location.LocationID
WHERE (Location.Active = '1') AND (Course_Schedule.CourseDate
BETWEEN @begDate AND @endDate)
ORDER BY Location.Location_Name, Course_Schedule.CourseDate
Thank you.
January 26, 2009 at 9:02 pm
How about using LEFT or RIGHT JOIN
January 27, 2009 at 11:39 am
I don't think a left or right join would work since the date is not in any table.
January 27, 2009 at 11:42 am
You'd have to build a table of months (or a full-on calendar table) and do a cross join from that to the locations, and then a left outer join from those to the courses.
Easy enough to build a table of months. Do you need help with any of that, or can you fly with it from the description?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2009 at 11:43 am
Typically, you want to use a Tally or Numbers table to "fill in the gaps" for sequences or dates.
I couldn't get my mental arms around your example to change this code to reflect your need, sorry.
here is an example, maybe you can adapt it to your needs:
[font="Courier New"]
SET DATEFORMAT MDY
DROP TABLE #myBalances
CREATE TABLE #myBalances (
[TranDate] DATETIME,
[TransactionAmount] money,
[Balance] money)
INSERT INTO #myBalances VALUES ('12/1/2008',100,100)
INSERT INTO #myBalances VALUES ('12/3/2008',-60,40)
INSERT INTO #myBalances VALUES ('12/4/2008',10,50)
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '12/1/2008'
SET @EndDate = '12/6/2008'
SELECT DATEADD(DD, 0, n.number) AS TranDate, ISNULL(b.TransactionAmount, 0) AS TransactionAmount, x.Balance
FROM Numbers n
LEFT JOIN #myBalances b ON b.TranDate = DATEADD(DD, 0, n.number)
LEFT JOIN #myBalances x ON x.TranDate = (SELECT MAX(TranDate) AS TranDate FROM #myBalances WHERE TranDate <= DATEADD(DD, 0, n.number))
WHERE number BETWEEN DATEDIFF(DD, 0, @StartDate) AND DATEDIFF(DD, 0, @EndDate)
[/font]
Lowell
February 2, 2009 at 1:24 pm
GSquared (1/27/2009)
You'd have to build a table of months (or a full-on calendar table) and do a cross join from that to the locations, and then a left outer join from those to the courses.Easy enough to build a table of months. Do you need help with any of that, or can you fly with it from the description?
GSQUARED,
I got the months table going. I am not sure how to build the cross join and left outer join.
DECLARE @curDate DATETIME
DECLARE @begDate DATETIME
SET @curDate = getdate()
SET @begDate = DATEADD(mm, DATEDIFF(mm,0,@curDate), 0)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#tempMonths]') AND type in (N'U'))
DROP TABLE [dbo].[#tempMonths]
CREATE TABLE #tempMonths (
[myMonth] DATETIME)
--insert dates
declare @counter int
set @counter = -1
while @counter < 12
begin
set @counter = @counter + 1
INSERT INTO #tempMonths
([myMonth])
VALUES
(DATEADD(mm, @counter, @begDate))
end
February 7, 2009 at 12:11 am
hi, that happen's because you are using inner join .... use full outer join instead of that
Raj Acharya
February 24, 2009 at 12:58 pm
I tried using a left join. However, it still does not show rows that only match the date and not the class.
ALTER PROCEDURE [dbo].[usp_matrix_courses_month] --usp_matrix_courses_month 1
@northSouth int
--1=north, 2=south
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @curDate DATETIME
DECLARE @begDate DATETIME
SET @curDate = getdate()
SET @begDate = DATEADD(mm, DATEDIFF(mm,0,@curDate), 0)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#tempMonths]') AND type in (N'U'))
DROP TABLE [dbo].[#tempMonths]
CREATE TABLE #tempMonths (
[myMonth] DATETIME)
--insert months
declare @counter int
set @counter = -1
while @counter < 11
begin
set @counter = @counter + 1
INSERT INTO #tempMonths
([myMonth])
VALUES
((DATEADD(mm, @counter, @begDate)))
end
DECLARE @endDate DATETIME
SET @endDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@curDate)+12,0)) --1 year
--insert classes
SELECT Course_Schedule.LocationID, Course_Schedule.CourseDate, Course_Schedule.Course_Number, Course_Schedule.ClassWaitlist012,
Location.Location_Name, Location.Active, Location.NorthSouth,
DATEADD(mm, DATEDIFF(mm,0,Course_Schedule.CourseDate), 0) as myMonth
INTO #tempClasses
FROM Course_Schedule INNER JOIN
Location ON Course_Schedule.LocationID = Location.LocationID
WHERE (Location.Active = '1')
AND (Location.NorthSouth = @northSouth)
AND (Course_Schedule.CourseDate BETWEEN @begDate AND @endDate)
AND Course_Schedule.Course_Number not in(101,104)
ORDER BY Location.Location_Name, Course_Schedule.CourseDate
SELECT #tempMonths.myMonth as monthNow, #tempClasses.LocationID, #tempClasses.CourseDate,
#tempClasses.Course_Number, #tempClasses.ClassWaitlist012,
#tempClasses.Location_Name, #tempClasses.Active, #tempClasses.NorthSouth
FROM #tempMonths
LEFT JOIN
#tempClasses ON #tempMonths.myMonth = #tempClasses.myMonth
ORDER BY #tempClasses.Location_Name, #tempClasses.CourseDate
END
March 3, 2009 at 10:02 pm
Hey there, Norbert... did you get this worked out or do you still need help?
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply