null values return

Hi,
How do I join 2 tables with null value return?
Table1
Month, Year, Hours
Table2
Month, Year, Accounting_hrs
Here is my current statement.
Select Table1.month as MONTH, Table1.year as YEAR,
Table1.sumhrs/Table2.accounting_hrs AS AVERAGE
from Table2,
(
select month, year, sum (hours) as sumhrs
from Table1
where year = 2005
group by month, year
)
where
(
Table1.year = Table2.year
and
Table1.month = Table2.month
)
order by Table1.Month
RESULT:
MONTH YEAR AVERAGE
(Table1.hours/Table2.Accounting_hrs)
Jan 2005 5
Feb 2005 10
I would like the results to look like this:
MONTH YEAR AVERAGE
(Table1.hours/Table2.Accounting_hrs)
Jan 2005 5
Feb 2005 10
Mar 2005 NULL
Apr 2005 NULL
May 2005 NULL
etc.
TIA!!
Joyce
*** Sent via Developersdex http://www.examnotes.net ***Probably the easiest method to solve this problem is the build a calendar ta
ble
that contains a row for every day from now until some arbitrary point in the
future. That makes this problem trival to solve. For example you might have
(granted without the specific DDL of your solution, this may not be perfect)
Create Table Calendar
(
SpecificDate DateTime
, Month TinyInt
, Year SmallInt
, W TinyInt
, Quarter TinyInt
)
Select C.Month, C.Year
, Sum(T1.Hours) As SumHours
, Sum(T1.Hours) / Sum(T2.Accounting_Hrs) As SumHours
From Calendar As C
Left Join Table1 As T1
On C.Month = T1.Month
And C.Year = T1.Year
Left Join Table2 As T2
On C.Month = T2.Month
And C.Year = T2.Year
Group By C.Month, C.Year
If you know that for every value in Table2 there exists a value in Table1, t
hen
you can adjust the query slightly like so:
Select C.Month, C.Year
, Sum(T1.Hours) As SumHours
, Sum(T1.Hours / T2.Accounting_Hrs) As SumHours
From Calendar As C
Left Join (Table1 As T1
Join Table2 As T2
On T1.Month = T2.Month
And T1.Year = T2.Year)
On C.Month = T1.Month
And C.Year = T1.Year
Group By C.Month, C.Year
This solution of course presumes that Sum(Accounting_Hrs) will not be zero.
It
should also be noted that if either Sum(T1.Hours) or Sum(T2.Accounting_Hrs)
is
null that you will get Null for the result.
HTH,
Thomas|||Actually, as I think about you'll get bad results joining directly to the
calendar table. You would need to group your Calendar table first like so:
Select C.Month, C.Year
, Sum(T1.Hours) As TotalHours
, Sum(T1.Hours) / Sum(T2.Accounting_hrs) As AverageHours
From (
Select C1.Month, C1.Year
From Calendar As C1
Where C1.Year = 2005
Group By C1.Month, C1.Year
) As C
Left Join Table1 As T1
On C.Month = T1.Month
And C.Year = T1.Year
Left Join Table2 As T2
On C.Month = T2.Month
And C.Year = T2.Year
Thomas
"Joyce L" <jsh_57@.hotmail.com> wrote in message
news:OdVy8sbSFHA.2384@.tk2msftngp13.phx.gbl...
> Hi,
> How do I join 2 tables with null value return?
> Table1
> Month, Year, Hours
> Table2
> Month, Year, Accounting_hrs
> Here is my current statement.
> Select Table1.month as MONTH, Table1.year as YEAR,
> Table1.sumhrs/Table2.accounting_hrs AS AVERAGE
> from Table2,
> (
> select month, year, sum (hours) as sumhrs
> from Table1
> where year = 2005
> group by month, year
> )
> where
> (
> Table1.year = Table2.year
> and
> Table1.month = Table2.month
> )
> order by Table1.Month
> RESULT:
> MONTH YEAR AVERAGE
> (Table1.hours/Table2.Accounting_hrs)
> Jan 2005 5
> Feb 2005 10
>
> I would like the results to look like this:
> MONTH YEAR AVERAGE
> (Table1.hours/Table2.Accounting_hrs)
> Jan 2005 5
> Feb 2005 10
> Mar 2005 NULL
> Apr 2005 NULL
> May 2005 NULL
> .etc.
> TIA!!
> Joyce
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thank you very much Thomas!!!
*** Sent via Developersdex http://www.examnotes.net ***