Nested Select Outer Join

[rjeffres]

Hello,

Doing a quick query to tables already in system, so cannot edit the tables
or data. Trying to get counts of "Hits", like an advertisement hit, for
multiple time periods. Individual Selects work fine and so does the Nested
Select; however, if an Ad was not viewed for a given time period then 0 is
not shown and the history is not shown. I need help in the last WHERE clause
to be an Outer Join on table a, b, and c.

Here is the Nested Select:
SELECT a.Company, a.T14, b.T7, c.T1
FROM [SELECT Company, Count(*) as T14
FROM Company, Hits
where Company.Id = Hits.CompanyId
and CDate(Mid([HitTS],5,2) & "/" & Mid([HitTS],7,2) & "/" &
Left([HitTS],4)) >=Now() - 14
Group by Company]. AS a, [SELECT Company, Count(*) as T7
FROM Company, Hits
where Company.Id = Hits.CompanyId
and CDate(Mid([HitTS],5,2) & "/" & Mid([HitTS],7,2) & "/" &
Left([HitTS],4)) >=Now() - 7
Group by Company]. AS b, [SELECT Company, Count(*) as T1
FROM Company, Hits
where Company.Id = Hits.CompanyId
and CDate(Mid([HitTS],5,2) & "/" & Mid([HitTS],7,2) & "/" &
Left([HitTS],4)) >=Now() - 1
Group by Company]. AS c
WHERE a.Company = b.Company and a.Company = c.Company;

 

[Sergey Poberezovskiy]

The following should do the trick:

Select c.Company,
Sum(IIf(h.TS >= Now() - 14, 1, 0)) As T14,
Sum(IIf(h.TS >= Now() - 7, 1, 0)) As T7,
Sum(IIf(h.TS >= Now() - 1, 1, 0)) As T1
From Company As c
Left Join
(Select CompanyId,
CDate(Mid([HitTS],5,2) & "/" & Mid
([HitTS],7,2) & "/" & Left([HitTS],4)) As TS
Fom Hits) As h
On c.CompanyId = h.CompanyId
Group By
c.CompanyId


P.S. I haven't had a chance to test the syntax, so let me
know if it does not work as you expected.

 

[rjeffres]

Excellent!!! Thank you Sergey. Small tweaks made to run on my test db shown
below, but you provided the answer I was looking for. Thanks again.

:

The following should do the trick:

Select c.Company,
Sum(IIf(h.TS >= Now() - 14, 1, 0)) As T14,
Sum(IIf(h.TS >= Now() - 7, 1, 0)) As T7,
Sum(IIf(h.TS >= Now() - 1, 1, 0)) As T1
From Company As c
Left Join
(Select CompanyId,
CDate(Mid([HitTS],5,2) & "/" & Mid([HitTS],7,2) & "/" & Left([HitTS],4))
As TS
From Hits) As h
On c.Id = h.CompanyId
Group By
c.Company