Re: Left Outer Join not returning expected Null records

From: Bob Holmes (rholmes_at_REMOVEmmwec.org)
Date: 08/05/04 

Date: Thu, 5 Aug 2004 16:18:05 -0400

Tom,
    I tried it and from what I can see so far it might do the trick. Thank
you very much for such a prompt response to something that I have been
spending all day trying to figure out. 

--
Bob Holmes
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:OoZoybyeEHA.4068@TK2MSFTNGP11.phx.gbl...
> Try:
>
> select
>      empid
> ,    billcode
> ,    sum (case when month_number = 1 then hours else 0 end) as Jan
> ,    sum (case when month_number = 2 then hours else 0 end) as Feb
> ,    ... yadda ...
> from
>     Labor
> group by
>      empid
> ,    billcode
>
> --
> Tom
>
> ---------------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
>
> "Bob Holmes" <rholmes@REMOVEmmwec.org> wrote in message
> news:udMeTVyeEHA.1100@TK2MSFTNGP10.phx.gbl...
> Hi,
>     I developed a stored procedure in SQL2000 to be used a source for a
> report.  I have three tables:  Months, which has month_number, month_name
> and mnth (Jan, Feb, Mar...three-letter abbr. for each month); Labor, which
> has month_number, hours, empid, billcode; and Employee, which has empid,
> lname, fname, and all the other information about each employee.
> I would like to have results that show not just the hours worked on each
> project by each employee for each month, but show zeros or empty fields
for
> months not worked.
> As an example:
> If John worked on project "AA" in Jan, Feb and March, I don't want to see
> only the hours for Jan, Feb and March.  I want to see all twelve months
and
> nothing (or zero) for the other 9 months that he didn't work on this
> project:
> Project "AA"
>             Jan       Feb    Mar    Apr    May    Jun     Jul    Aug
Sep
> Oct    Nov    Dec
> John       20        22     19        0        0        0        0
0
> 0        0        0        0
>
> The best I can get is the three months with hours and the no record with
the
> other months.
> I found that if I restrict the selection to only one project (billcode),
> then I get twelve records, but when I try to collect records for all of
the
> projects, I only get the records that have hours.
>
> I hope my explanation wasn't too long or confusing.  If anyone can guide
in
> the right direction, I would appreciate it very much.
> I didn't post my query attempts because I didn't want to make this post
any
> longer than it is.  But, I basically tried different combinations of LEFT
> OUTER JOINS of Months LJ Labor LJ Employee.
>
> Thanks in advance
>
> --
> Bob Holmes
>
>

Relevant Pages

  • Re: Left Outer Join not returning expected Null records
    ... one record for each billcode, ... for each month); Labor, which ... > lname, fname, and all the other information about each employee. ...
    (microsoft.public.sqlserver.programming)
  • Re: Left Outer Join not returning expected Null records
    ... for each month); Labor, which ... lname, fname, and all the other information about each employee. ...
    (microsoft.public.sqlserver.programming)
  • Left Outer Join not returning expected Null records
    ... has month_number, hours, empid, billcode; ... lname, fname, and all the other information about each employee. ... project by each employee for each month, but show zeros or empty fields for ... I found that if I restrict the selection to only one project (billcode), ...
    (microsoft.public.sqlserver.programming)
  • Re: Still Struggling...
    ... means "Find the first record in the RecordsetClone in which EmpID is the ... Each employee can only have 1 classification (Admin., ... tblClassifications 1:M tblEmployees, ... tblTitles M:M tblTitlesEmps ...
    (microsoft.public.access.gettingstarted)
  • Re: Extract Outlook Address book properties into Excel
    ... Michael Bauer - MVP Outlook ... I think since the Employee names & Emp Code belongs to a Global Address ... Custom-defined type like EmpID. ... Dim xl as Excel.Application ...
    (microsoft.public.outlook.program_vba)