Join problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Join problem

Author

Topic

Babelfish
Starting Member

4 Posts

Posted - 2006-08-18 : 18:19:03

I try to count number of records grouped by each day of week. Doing so for one table cause no problems:

Select Count(date) Amount
FROM table where date > DateAdd(d,-7,GetDate() Group By DatePart(dw,date)

Doing so for multiple tables returned in the same result set is what I cant get working. If I get something in return I get a product much larger than the actual count.
Any suggestions? Btw. I use MSSQL 2005

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-18 : 18:43:42

It would be easier to help you if you showed us the code you are having trouble with, explained what the problem is, and showed us the table structures, sample data, and expected output.

CODO ERGO SUM

Babelfish
Starting Member

4 Posts

Posted - 2006-08-18 : 19:59:13

quote:
Originally posted by Michael Valentine Jones

It would be easier to help you if you showed us the code you are having trouble with, explained what the problem is, and showed us the table structures, sample data, and expected output.

CODO ERGO SUM

Ok. There really isn't much to show. The table columns I need are shown in the query example above. It's just more tables all with a timestamp (datetime) field.

Amongst others I've tried this:
Select DatePart(dw,a1.date) AS DOW,
Count(a1.date) [Amount 1],
DatePart(dw,a2.date) AS [Amount 2],
Count(a2.date) a2Antall
FROM AWP1 A1, AWP2 A2
Where a1.date > '2006-08-11' AND a2.date > '2006-08-11'
Group By DatePart(dw,a1.date), DatePart(dw,a2.date)
Having DatePart(dw,a1.date) = DatePart(dw,a2.date)

Returning
DOW Amount 1 DOW 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

Count in each table is in the range of thousands

Result set I want is:
DayOfWeek Amount 1 Amount 2 Amount 3
--------- -------- -------- --------
1 3455 2993 2155
2 3299 3444 2333
.
.
7 2333 3422 2999

Where Amount 1, Amount 2 and Amount 3 represent a count in each table only limitied by start and stop dates. Only "link" between tables would be the day of week.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-19 : 04:33:58

Are this tables which are linked to each other with some key???

How about somthing like this?


Select DatePart(dw,a1.date) AS DOW,Count(a1.date) [Amount1],
DatePart(dw,a2.date) AS [Amount 2],
Count(a2.date) a2Antall
FROM AWP1 A1 Inner Join AWP2 A2 
DatePart(dw,a1.date) = DatePart(dw,a2.date) 
Where a1.date > '2006-08-11' AND a2.date > '2006-08-11'
Group By DatePart(dw,a1.date), DatePart(dw,a2.date)

Or May be 

Select DatePart(dw,a1.date) AS DOW,Count(a1.date) [Amount1],
DatePart(dw,a2.date) AS [Amount 2],
Count(a2.date) a2Antall
FROM AWP1 A1 Inner Join AWP2 A2 
a1.date = a2.date
Where a1.date > '2006-08-11' 
Group By DatePart(dw,a1.date), DatePart(dw,a2.date)

Chirag

Babelfish
Starting Member

4 Posts

Posted - 2006-08-19 : 17:47:39

quote:

Originally posted by chiragkhabaria

Are this tables which are linked to each other with some key???

How about somthing like this?


Select DatePart(dw,a1.date) AS DOW,Count(a1.date) [Amount1],
DatePart(dw,a2.date) AS [Amount 2],
Count(a2.date) a2Antall
FROM AWP1 A1 Inner Join AWP2 A2 
DatePart(dw,a1.date) = DatePart(dw,a2.date) 
Where a1.date > '2006-08-11' AND a2.date > '2006-08-11'
Group By DatePart(dw,a1.date), DatePart(dw,a2.date)

Or May be 

Select DatePart(dw,a1.date) AS DOW,Count(a1.date) [Amount1],
DatePart(dw,a2.date) AS [Amount 2],
Count(a2.date) a2Antall
FROM AWP1 A1 Inner Join AWP2 A2 
a1.date = a2.date
Where a1.date > '2006-08-11' 
Group By DatePart(dw,a1.date), DatePart(dw,a2.date)

Chirag

Thanks for the try.
First one gave me the same as i've been getting: billons, instead of thosands in count() and the same result for both amount a1 and amaount a2. Must be getting into some kind of loop. The second one gave me a few to a couple of hundreds and again the same count. Probably where the dates by coincidence are exactly the same, down to the second.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-20 : 01:40:40

How did you got your Expected Result Set?

Are these tables link to each other in any way??

Chirag

Babelfish
Starting Member

4 Posts

Posted - 2006-08-20 : 04:00:04

quote:
Originally posted by chiragkhabaria

How did you got your Expected Result Set?

Are these tables link to each other in any way??

Chirag

Working kode:
Select Count(date) Amount
FROM table where date > DateAdd(d,-7,GetDate() Group By DatePart(dw,date)

No there is no link. Thats why I tried to join with the day of week.

Edit: Found a solution elsewhere:

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

Or

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

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-20 : 04:24:22

try this


Select t1.Dow, Amount1,Amount2 From 
(
  Select DatePart(dw,[date]) DOW, Count(1) As Amount1 From AWP1 
  Where [date] > DateAdd(d,-7,GetDate())
  Group by DatePart(dw,[date]) 
) As t1
Inner Join 
(
  Select DatePart(dw,[date]) DOW, Count(1) As Amount2 From AWP2 
  Where [date] > DateAdd(d,-7,GetDate())
  Group by DatePart(dw,[date]) 
) As t2
On t1.DOW = t2.DOW

Chirag

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-20 : 04:36:42

quote:
Originally posted by Babelfish

quote:
Originally posted by chiragkhabaria

How did you got your Expected Result Set?

Are these tables link to each other in any way??

Chirag

Working kode:
Select Count(date) Amount
FROM table where date > DateAdd(d,-7,GetDate() Group By DatePart(dw,date)

No there is no link. Thats why I tried to join with the day of week.

Edit: Found a solution elsewhere:

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

Or

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

Aha.. but in the query which you got there is no date filter of the past 7 days.

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources