Assistance with Join - 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 2005 Forums

Transact-SQL (2005)

Assistance with Join

Author

Topic

tfulmino
Starting Member

7 Posts

Posted - 2007-11-15 : 08:32:53

I need some assistance with the following query...

I have two tables. The first table has student absences within a date period. The table ONLY contains a row for the student IF the student has missed a day during that period. The second table contains the ALL the date periods available.

I would like the output to show ALL the date periods. If a rec exists for the student, then show the row data, otherwise display the studentid and dateperiod with a 0 (zero) for days absent.

Below is some code to replicate the tables.

select 13024 as StudentID, '2007-09-14 00:00:00.000' as DatePeriod, 2007 as SchoolYear, 1 as DaysAbsent
into #StudentDaysAbsent UNION ALL
select 13025, '2007-09-14 00:00:00.000', 2007, 2
select 13025, '2007-10-5 00:00:00.000', 2007, 1
select 13025, '2007-10-26 00:00:00.000', 2007, 1

select 'Sep 7 2007 12:00AM' as DatePeriod
into #DatePeriod UNION ALL
select 'Sep 14 2007 12:00AM' as DatePeriod UNION ALL
select 'Sep 21 2007 12:00AM' as DatePeriod UNION ALL
select 'Sep 28 2007 12:00AM' as DatePeriod UNION ALL
select 'Oct 5 2007 12:00AM' as DatePeriod UNION ALL
select 'Oct 12 2007 12:00AM' as DatePeriod UNION ALL
select 'Oct 19 2007 12:00AM' as DatePeriod UNION ALL
select 'Oct 26 2007 12:00AM' as DatePeriod

drop table #DatePeriod
drop table #StudentDaysAbsent

Thanks in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 08:35:49

can you also post the expected output ?

KH
[spoiler]Time is always against us[/spoiler]

tfulmino
Starting Member

7 Posts

Posted - 2007-11-15 : 08:45:33

Sorry about that... forgot about the output.

StudentID DatePeriod SchoolYear DaysAbsent
13024 Sep 7 2007 12:00AM 2007 0
13025 Sep 7 2007 12:00AM 2007 0
13024 Sep 14 2007 12:00AM 2007 1
13025 Sep 14 2007 12:00AM 2007 2
13024 Sep 21 2007 12:00AM 2007 0
13025 Sep 21 2007 12:00AM 2007 0
13024 Sep 28 2007 12:00AM 2007 0
13025 Sep 28 2007 12:00AM 2007 0
13024 Oct 5 2007 12:00AM 2007 0
13025 Oct 5 2007 12:00AM 2007 1
13024 Oct 12 2007 12:00AM 2007 0
13025 Oct 12 2007 12:00AM 2007 0
13024 Oct 19 2007 12:00AM 2007 0
13025 Oct 19 2007 12:00AM 2007 0
13024 Oct 26 2007 12:00AM 2007 0
13025 Oct 26 2007 12:00AM 2007 1

I also notice one of the queries is incorrect. correction:

select 13024 as StudentID, '2007-09-14 00:00:00.000' as DatePeriod, 2007 as SchoolYear, 1 as DaysAbsent
into #StudentDaysAbsent UNION ALL
select 13025, '2007-09-14 00:00:00.000', 2007, 2 UNION ALL
select 13025, '2007-10-5 00:00:00.000', 2007, 1 UNION ALL
select 13025, '2007-10-26 00:00:00.000', 2007, 1

select 'Sep 7 2007 12:00AM' as DatePeriod
into #DatePeriod UNION ALL
select 'Sep 14 2007 12:00AM' as DatePeriod UNION ALL
select 'Sep 21 2007 12:00AM' as DatePeriod UNION ALL
select 'Sep 28 2007 12:00AM' as DatePeriod UNION ALL
select 'Oct 5 2007 12:00AM' as DatePeriod UNION ALL
select 'Oct 12 2007 12:00AM' as DatePeriod UNION ALL
select 'Oct 19 2007 12:00AM' as DatePeriod UNION ALL
select 'Oct 26 2007 12:00AM' as DatePeriod

select *
from #StudentDaysAbsent

select *
from #DatePeriod

drop table #DatePeriod
drop table #StudentDaysAbsent

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 08:54:08

[code]DECLARE @Students TABLE
(
StudentID int
)

INSERT INTO @Students
SELECT 13024 UNION ALL
SELECT 13025

DECLARE @StudentDaysAbsent TABLE
(
StudentID int,
DatePeriod datetime,
SchoolYear int,
DaysAbsent int
)
INSERT INTO @StudentDaysAbsent
SELECT 13024 AS StudentID, '2007-09-14 00:00:00.000' AS DatePeriod, 2007 AS SchoolYear, 1 AS DaysAbsent UNION ALL
SELECT 13025, '2007-09-14 00:00:00.000', 2007, 2 UNION ALL
SELECT 13025, '2007-10-5 00:00:00.000', 2007, 1 UNION ALL
SELECT 13025, '2007-10-26 00:00:00.000', 2007, 1

DECLARE @DatePeriod TABLE
(
DatePeriod datetime
)
INSERT INTO @DatePeriod
SELECT 'Sep 7 2007 12:00AM' AS DatePeriod UNION ALL
SELECT 'Sep 14 2007 12:00AM' AS DatePeriod UNION ALL
SELECT 'Sep 21 2007 12:00AM' AS DatePeriod UNION ALL
SELECT 'Sep 28 2007 12:00AM' AS DatePeriod UNION ALL
SELECT 'Oct 5 2007 12:00AM' AS DatePeriod UNION ALL
SELECT 'Oct 12 2007 12:00AM' AS DatePeriod UNION ALL
SELECT 'Oct 19 2007 12:00AM' AS DatePeriod UNION ALL
SELECT 'Oct 26 2007 12:00AM' AS DatePeriod

SELECT s.StudentID, d.DatePeriod, DaysAbsent = ISNULL(a.DaysAbsent, 0)
FROM @Students s CROSS JOIN @DatePeriod d
left JOIN @StudentDaysAbsent a
ON s.StudentID = a.StudentID
AND d.DatePeriod = a.DatePeriod
ORDER BY d.DatePeriod, a.StudentID

/*

StudentID DatePeriod DaysAbsent
----------- ----------- -----------
13024 2007-09-07 0
13025 2007-09-07 0
13024 2007-09-14 1
13025 2007-09-14 2
13025 2007-09-21 0
13024 2007-09-21 0
13024 2007-09-28 0
13025 2007-09-28 0
13024 2007-10-05 0
13025 2007-10-05 1
13025 2007-10-12 0
13024 2007-10-12 0
13024 2007-10-19 0
13025 2007-10-19 0
13024 2007-10-26 0
13025 2007-10-26 1

(16 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

tfulmino
Starting Member

7 Posts

Posted - 2007-11-15 : 09:02:04

khtan - worked perfectly. I guess I am missing the concept of the cross join.

Thanks for all your assistance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources