We have two tables, Districts and DayAllotment
My Data in Day Allotment is something like this:
1 1012 23 First Fish 2008
2 1012 22 Second Fish 2008
3 1012 15 First Boat 2008
4 1012 14 Second Boat 2008
5 2012 23 First Boat 2008
6 2012 22 Second Fish 2008
7 2012 14 Second Boat 2008
So, let's say I want a result set that looks for All Fish allotments for 2008. I want a result set that looks like this:
DistrictID First Second
1012 23 22
1013 NULL 22
I tried something like this:
SELECT dist.DistrictID, fDays.DaysAllotted AS 'Jan.-Jun.', sDays.DaysAllotted AS 'Jul.-Dec.'
FROM dbo.law_Districts dist
LEFT JOIN law_DeputyDaysAllotment fDays ON dist.DistrictID = fDays.DistrictID
LEFT JOIN law_DeputyDaysAllotment sDays ON dist.DistrictID = sDays.DistrictID
WHERE ((fDays.HalfYearWindow = 'First' AND fDays.Year = @Year AND fDays.Type = @Type) OR (fDays.HalfYearWindow IS NULL))
OR ((sDays.HalfYearWindow = 'Second' AND sDays.Year = @Year AND sDays.Type = @Type) OR (sDays.HalfYearWindow IS NULL))ORDER BY dist.DistrictID
I've tried some other ways of joining, but have gotten results where some districts were dropped. I won't always know which side will have the NULL values.
I'm sure there are samples out there but I'm not sure what to search for. Would this be a cross join table?