RE: Repost - help to merge 2 queries

Hi Mike,

You are welcome. Here is something to try:

SELECT C.Date,
Sum([A].[ASA]*IIf(IsNull([B].[Site]),Null,[A].[Calls]))/Sum(IIf(IsNull([B].[Site]),Null,[A].[Calls]))
AS ASA, Sum(IIf(IsNull([B].[Site]),Null,[A].[Calls])) AS Calls
FROM (C LEFT JOIN A ON C.Date = A.Date) LEFT JOIN B ON (A.Site = B.Site) AND
(A.Skill = B.Skill)
GROUP BY C.Date;

I will be interested if it works for more than the test data. The key
seems to be to do both of the left joins and to check the value of B.Site (or
B.Skill) while doing the summing in order to tell it which values to
include/exclude.

Nice question!

Clifford Bass

"uk_firebrand" wrote:

Hi Clifford,

Again, appreciate the reply. I am not in work but sample data / construct as
follows: -

Table A (Raw Data)
Date Site Skill ASA Calls
01/01/2008 1 1 1 10
01/01/2008 1 2 1 10
01/01/2008 1 3 1 10
01/01/2008 2 1 1 10
01/01/2008 2 2 1 10
01/01/2008 2 3 1 10
01/01/2008 3 1 1 10
01/01/2008 3 2 1 10
01/01/2008 3 3 1 10

Table B (Criteria i.e. a department)
Inner Joins on Table A
Site Skill
1 1
1 2
2 1
3 2
3 3

Table C (Dummy Table)
Left Join Table A
Date
01/01/2008
02/01/2008
etc
etc
etc

Expected results are as seen when using 2 separate queries i.e: -
1st query with inner joins between table A and B (Skill/Site Fields)
2nd query with left join on Table C and 1st query (Date field)

Results Query
Date ASA Calls
01/01/2008 1 50
02/01/2008 Blank Blank
03/01/2008 etc etc
etc etc etc


Date - All dates from Table C i.e. left join on Table A to show results
where exist, nulls if no results for a date
Results where Skill & Site in Table A/B match (inner joins)

Calculated fields: ASA - Equivalent of SumProduct(ASA,Calls)/Sum(Calls)
Calls - Sum(Calls)

I hope the above makes sense.

I've quickly read the reply from John Spencer and what he has written lends
itself to my knowledge of SQL - with a bit of MS syntax shenanigans thrown in
there to confuse us poor souls - but, as I said, I am not in work to test
this. I will do tomorrow. Thanks John!!

Thanks again - like everyone else in these forums, I really appreciate the
generosity of all the people who donate their time / expertise in answering
these posts.

Best regards to you all. . .
--
Mike Peate
.