Join on agragate function between tables

A have a number of similar tables and what I want to do is to get the count of records grouped by day of week. All tables have date as an indexed unique column but the actual timestamps differs and have no relation. For one table I use this simple querry:

Select DatePart(dw,dato) AS DOW, Count(dato) AS NOR FROM AWP2
where dato > '2006-08-11'
Group By DatePart(dw,dato)

A typical result:

DOW NOR
3 8934
6 22397
7 23328
1 23401
4 1938
2 24399
5 1112

Trying to join two or more tables in all sorts of variants of this:

Select datePart(dw,a1.dato) AS DOW1,Count(a1.dato) [Amount 1],
datePart(dw,a2.dato) AS DOW2, Count(a2.dato) [Amount 2]
FROM AWP1 A1 Inner Join AWP2 A2 on datePart(dw,a1.dato) = datePart(dw,a2.dato)
Where a1.dato > '2006-08-11' AND a2.dato > '2006-08-11'
Group By datePart(dw,a1.dato), datePart(dw,a2.dato)

Here I get this as a typical result:

DOW1 Amount 1 DOW2 Amount 2
6 332953802 6 332953802
3 42248886 3 42248886
1 330281714 1 330281714
7 335759904 7 335759904
4 1232568 4 1232568
5 210168 5 210168
2 366985359 2 366985359

Where the numbers are way off.

Any suggestions?

When you Join the tables, based only on the day of the week, your resultset will have each record in Table1 for a day of the week combined with each record in Table2 for the same day of the week. That is why your numbers are so far off.

Here is a simple example that shows what's happening.

Drop Table Table1

Create Table Table1(

pkid int not null Identity(1,1),

dato datetime not null,

valCol varchar(10)

)

Drop Table Table2

Create Table Table2(

pkid int not null Identity(1,1),

dato datetime not null,

valCol varchar(10)

)

insert Table1 values( '1/1/2006', 'Fred' )

insert Table1 values( '1/2/2006', 'Barney' )

insert Table2 values( '1/1/2006', 'Wilma' )

insert Table2 values( '1/1/2006', 'Pebbles' )

insert Table2 values( '1/2/2006', 'Betty' )

insert Table2 values( '1/2/2006', 'BamBam' )

Select DatePart(dw, dato ), Count(*)

From Table1

Group

By DatePart(dw, dato )

Select DatePart(dw, dato ), Count(*)

From Table2

Group

By DatePart(dw, dato )

Select DatePart(dw, t1.dato ),

DatePart(dw, t2.dato),

t1.valCol,

t2.valCol

From Table1 t1

Join Table2 t2

On DatePart(dw, t1.dato) = DatePart(dw, t2.dato )

valCol valCol

-- -- - -

1 1 Fred Wilma

1 1 Fred Pebbles

2 2 Barney Betty

2 2 Barney BamBam

Select DatePart(dw, t1.dato ),

DatePart(dw, t2.dato),

Count(t1.dato),

Count(t2.dato)

From Table1 t1

Join Table2 t2

On DatePart(dw, t1.dato) = DatePart(dw, t2.dato )

Group

By DatePart(dw, t1.dato ),

DatePart(dw, t2.dato)

-- -- -- --
1 1 2 2
2 2 2 2
Select Coalesce( t1.dow, t2.dow ),

t1.howmany,

t2.howmany

From (

Select DatePart(dw, dato ) dow,

Count(*) howmany

From Table1

Group by DatePart(dw, dato )

) t1

Full Outer Join

(

Select DatePart(dw, dato ) dow,

Count(*) howmany

From Table2

Group by DatePart(dw, dato )

) t2

On t1.dow = t2.dow

howmany howmany
-- -- --
1 1 2
2 1 2

Select dayofweek,

(Select Count(*) from Table1 where DatePart(dw, dato ) = dayofweek ),

(Select Count(*) from Table2 where DatePart(dw, dato ) = dayofweek )

From (

Select 1 as dayofweek

Union All

Select 2 as dayofweek

Union All

Select 3 as dayofweek

Union All

Select 4 as dayofweek

Union All

Select 5 as dayofweek

Union All

Select 6 as dayofweek

Union All

Select 7 as dayofweek

)t1

dayofweek
-- -- --
1 1 2
2 1 2
3 0 0
4 0 0
5 0 0
6 0 0
7 0 0

There are several ways to get the days of the week. If you know that one of the tables will have records for every day, you can use that. There is a system table that has lists of numbers, that is another good source.

|||

Excellent stuff. I allready guessed the reason for my results but no idea how to stop it.