Re: Compare two date into the same table

Hi


SELECT pres.referencedate,
pres_1.referencedate,
pres.location,
pres.class,
pres.npeople-Nz(pres_1.npeople, 0) AS diff_people
FROM pres LEFT JOIN pres AS pres_1
ON (pres.class = pres_1.class)
AND
(pres.location = pres_1.location)
WHERE pres.referencedate=#2/1/2005#
AND
Nz(pres_1.referencedate,#1/1/2005# )=#1/1/2005#;





When you use an outer join, and have the unpreserved table, here pres_1, in
the WHERE clause, keep in mind that due to the nature of the outer join,
that table CAN SUPPLY NULL values. Remove the WHERE clause to convince
yourself. If you write the WHERE clause without handling these NULL, you
kill the outer join effect, since the WHERE is likely to remove these
records with a NULL.




Hoping it may help,
Vanderghast, Access MVP


"Alex_Firenze" <alessandro.caforio@xxxxxxxxx> wrote in message
news:1128421444.852715.149360@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Hi all,
> Please, help me to find the right query....
> I would like to compare, within the same table, the figures related to
> a reference date and the previous one...
>
> Example, My table:
> referencedate;location;class;npeople
> 01/01/2005;london;math;100
> 01/01/2005;london;finance;80
> 01/01/2005;london;statistics;50
> 01/01/2005;brighton;statistics;15
> 01/02/2005;london;math;90
> 01/02/2005;london;finance;85
> 01/02/2005;london;statistics;40
> 01/02/2005;brighton;statistics;12
> 01/02/2005;brighton;math;15
>
> At the moment I am not able to manage the new entrances.. Indeed my
> result is:
>
> pres.referencedate; pres_1.referencedate;location;class;diff_people
> 01/02/2005;01/01/2005;london;math;-10
> 01/02/2005;01/01/2005;london;finance;5
> 01/02/2005;01/01/2005;london;statistics;-10
> 01/02/2005;01/01/2005;brighton;statistics;-3
>
> But I would expect also the line:
> 01/02/2005;01/01/2005;brighton;math;15
>
> How can I take into account new Classes or Locations that are appear in
> the reference date but not in the previous??
>
> My query:
>
> SELECT pres.referencedate, pres_1.referencedate, pres.location,
> pres.class, [pres].[npeople]-[pres_1].[npeople] AS diff_people
> FROM pres LEFT JOIN pres AS pres_1 ON (pres.class = pres_1.class) AND
> (pres.location = pres_1.location)
> WHERE (((pres.referencedate)=#2/1/2005#) AND
> ((pres_1.referencedate)=#1/1/2005#));
>
> Thank you in advance
>
> Alex
>


.

Relevant Pages

  • Re: CROSS JOIN
    ... > was that depreciating the original FROM .. ... WHERE syntax would never ... > The problem is that the WHERE clause is done after the FROM clause. ... but thats just outer join stuff. ...
    (comp.databases)
  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... That means that your filter, c.custid = "AB1234", is applied after all rows of the customer table have been LEFT OUTER JOINed to the my_view VIEW with the results stored into a temp table. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • [Info-ingres] RES: [Info-ingres] Wrong results returned from outer join
    ... Wrong results returned from outer join ... I get the expected zero rows when using the following versions ... " Outer joins specified in the from clause are not the same as joins ... the from clause specifies ...
    (comp.databases.ingres)
  • Re: Better "Join" vs "Where" clause?
    ... running the SQL directly, in the SQL Server tools, rather than ... WHERE clause has been deprecated, ... AFAIK one cannot perform an outer join in Access without using the explicit ...
    (microsoft.public.access.queries)
  • Re: exclude lines after outer join, like left joins in 7.24
    ... an EXISTS clause for the NOT EXISTS clause if you don't need any ... OUTER join using ANSI syntax and join in the ON clause and filter out ...
    (comp.databases.informix)
Loading