Problems Joining Query

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 403,516 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,893 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 446 | Replies: 8 | Solved

•

Join Date: Jul 2008

Posts: 5

Reputation: bartbauldry is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

bartbauldry bartbauldry is offline

Offline

Newbie Poster

Problems Joining Query

Jul 11th, 2008

Hello I seem to be having a problem, I have this query that pulls back correct results

Help with Code Tags

(Toggle Plain Text)

SELECT hourStamp, SUM(CASE WHEN datestamp < GETDATE() THEN 1 ELSE 0 END) / @numberofdays as HourAverage, SUM(CASE WHEN dateStamp BETWEEN DATEADD(d,DATEDIFF(d,0,GETDATE()),0) AND GETDATE() THEN 1 ELSE 0 END) as HourToday

FROM webstats

GROUP BY hourStamp
ORDER BY hourStamp

I also have this query that pulls back correct results

Help with Code Tags

(Toggle Plain Text)

SELECT hourstamp, COUNT(id) AS ActualVisits
FROM webStats
WHERE id IN                            
(SELECT ws.id
FROM webStats AS ws INNER JOIN webStatsTrace AS wst ON ws.id = wst.statsid
WHERE (ws.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE())
GROUP BY ws.id HAVING (COUNT(wst.statsid) > 1))
GROUP BY hourstamp
ORDER BY hourstamp

But i am unable to join the two querys, or rewrite them so i run one query and get all the results in one... could anyone please suggest a way?

Thanks
Bart

•

Join Date: Apr 2008

Location: Tulsa

Posts: 52

Reputation: khess is an unknown quantity at this point

Rep Power: 1

Solved Threads: 4

khess

Offline

Junior Poster in Training

Re: Problems Joining Query

Jul 11th, 2008

You can easily join two queries with the UNION keyword between the two individual SELECT queries:

SELECT xxxxxx UNION SELECT yyyyyy

Ken Hess
Linux Blogger/Columnist

•

Join Date: Jul 2008

Posts: 5

Reputation: bartbauldry is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

bartbauldry bartbauldry is offline

Offline

Newbie Poster

Re: Problems Joining Query

Jul 11th, 2008

Thanks, I have tried a UNION but it complains that there they do not have an equal number of expressions on their target list.

I have tried restructuing the query to no avail, the problem is that the fisrt query returns:

Help with Code Tags

(Toggle Plain Text)

hourStamp   HourAverage HourToday   
----------- ----------- ----------- 
0           50          52          
1           46          34          
2           40          39          
3           41          52          
4           39          46          
5           44          33          
6           43          51          
7           42          43          
8           53          49          
9           62          66          
10          69          56          
11          76          64          
12          66          55          
13          73          65          
14          73          16          
15          81          0           
16          76          0           
17          64          0           
18          70          0           
19          69          0           
20          67          0           
21          69          0           
22          58          0           
23          50          0

And the Latter returns:

Help with Code Tags

(Toggle Plain Text)

hourstamp   ActualVisits 
----------- ------------ 
0           5            
1           3            
2           2            
3           3            
4           1            
5           6            
6           3            
7           2            
8           6            
9           14           
10          14           
11          24           
12          13           
13          15           
14          4

The latter will add rows as the hours pass....
Any Ideas?

Thanks

•

Join Date: Feb 2008

Posts: 70

Reputation: cmhampton is an unknown quantity at this point

Rep Power: 1

Solved Threads: 10

cmhampton

Offline

Junior Poster in Training

Re: Problems Joining Query

Jul 11th, 2008

Try this:

 Help with Code Tags 
 SQL Syntax (Toggle Plain Text) 
SELECT 
		hourStamp,
		NULL AS ActualVisits 
		SUM(CASE WHEN datestamp < GETDATE() THEN 1 ELSE 0 END) / @numberofdays AS HourAverage, 
		SUM(CASE WHEN dateStamp BETWEEN DATEADD(d,DATEDIFF(d,0,GETDATE()),0) AND GETDATE() THEN 1 ELSE 0 END) AS HourToday
FROM 
		webstats
GROUP BY 
		hourStamp
ORDER BY 
		hourStamp
 
UNION
 
SELECT 
		hourstamp, 
		COUNT(id) AS ActualVisits,
		NULL AS HourAverage,
		NULL AS HourToday
FROM 
		webStats
WHERE 
		id IN                            
		(SELECT ws.id
		FROM webStats AS ws INNER JOIN webStatsTrace AS wst ON ws.id = wst.statsid
		WHERE (ws.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE())
		GROUP BY ws.id HAVING (COUNT(wst.statsid) > 1))
GROUP BY 
		hourstamp
ORDER BY 
		hourstamp
SELECT 
		hourStamp,
		NULL AS ActualVisits 
		SUM(CASE WHEN datestamp < GETDATE() THEN 1 ELSE 0 END) / @numberofdays as HourAverage, 
		SUM(CASE WHEN dateStamp BETWEEN DATEADD(d,DATEDIFF(d,0,GETDATE()),0) AND GETDATE() THEN 1 ELSE 0 END) as HourToday
FROM 
		webstats
GROUP BY 
		hourStamp
ORDER BY 
		hourStamp

UNION

SELECT 
		hourstamp, 
		COUNT(id) AS ActualVisits,
		NULL AS HourAverage,
		NULL AS HourToday
FROM 
		webStats
WHERE 
		id IN                            
		(SELECT ws.id
		FROM webStats AS ws INNER JOIN webStatsTrace AS wst ON ws.id = wst.statsid
		WHERE (ws.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE())
		GROUP BY ws.id HAVING (COUNT(wst.statsid) > 1))
GROUP BY 
		hourstamp
ORDER BY 
		hourstamp

Use aliased NULLs to make the SELECT clauses of the two queries match.

•

Join Date: Jul 2008

Posts: 5

Reputation: bartbauldry is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

bartbauldry bartbauldry is offline

Offline

Newbie Poster

Re: Problems Joining Query

Jul 11th, 2008

Thanks, that has helped, its now returning its a whole but it seems to be duplicating rows of the hours that have already passed (if that makes sence)

Help with Code Tags

(Toggle Plain Text)

hourStamp   ActualVisits HourAverage HourToday   
----------- ------------ ----------- ----------- 
0           <NULL>       50          52          
0           5            <NULL>      <NULL>      
1           <NULL>       46          34          
1           3            <NULL>      <NULL>      
2           <NULL>       40          39          
2           2            <NULL>      <NULL>      
3           <NULL>       41          52          
3           3            <NULL>      <NULL>      
4           <NULL>       39          46          
4           1            <NULL>      <NULL>      
5           <NULL>       44          33          
5           6            <NULL>      <NULL>      
6           <NULL>       43          51          
6           3            <NULL>      <NULL>      
7           <NULL>       42          43          
7           2            <NULL>      <NULL>      
8           <NULL>       53          49          
8           6            <NULL>      <NULL>      
9           <NULL>       62          66          
9           14           <NULL>      <NULL>      
10          <NULL>       69          56          
10          14           <NULL>      <NULL>      
11          <NULL>       76          64          
11          24           <NULL>      <NULL>      
12          <NULL>       66          55          
12          13           <NULL>      <NULL>      
13          <NULL>       73          65          
13          15           <NULL>      <NULL>      
14          <NULL>       75          51          
14          16           <NULL>      <NULL>      
15          <NULL>       84          85          
15          21           <NULL>      <NULL>      
16          <NULL>       80          78          
16          17           <NULL>      <NULL>      
17          <NULL>       67          49          
17          5            <NULL>      <NULL>      
18          <NULL>       70          0           
19          <NULL>       69          0           
20          <NULL>       67          0           
21          <NULL>       69          0           
22          <NULL>       58          0           
23          <NULL>       50          0

hourStamp   ActualVisits HourAverage HourToday   
----------- ------------ ----------- ----------- 
0           <NULL>       50          52          
0           5            <NULL>      <NULL>      
1           <NULL>       46          34          
1           3            <NULL>      <NULL>      
2           <NULL>       40          39          
2           2            <NULL>      <NULL>      
3           <NULL>       41          52          
3           3            <NULL>      <NULL>      
4           <NULL>       39          46          
4           1            <NULL>      <NULL>      
5           <NULL>       44          33          
5           6            <NULL>      <NULL>      
6           <NULL>       43          51          
6           3            <NULL>      <NULL>      
7           <NULL>       42          43          
7           2            <NULL>      <NULL>      
8           <NULL>       53          49          
8           6            <NULL>      <NULL>      
9           <NULL>       62          66          
9           14           <NULL>      <NULL>      
10          <NULL>       69          56          
10          14           <NULL>      <NULL>      
11          <NULL>       76          64          
11          24           <NULL>      <NULL>      
12          <NULL>       66          55          
12          13           <NULL>      <NULL>      
13          <NULL>       73          65          
13          15           <NULL>      <NULL>      
14          <NULL>       75          51          
14          16           <NULL>      <NULL>      
15          <NULL>       84          85          
15          21           <NULL>      <NULL>      
16          <NULL>       80          78          
16          17           <NULL>      <NULL>      
17          <NULL>       67          49          
17          5            <NULL>      <NULL>      
18          <NULL>       70          0           
19          <NULL>       69          0           
20          <NULL>       67          0           
21          <NULL>       69          0           
22          <NULL>       58          0           
23          <NULL>       50          0

Ive tried to remove the duplicate rows but seem unable to, i thought the union option wouldnt allow duplicate rows?

Thanks

•

Join Date: Feb 2008

Posts: 70

Reputation: cmhampton is an unknown quantity at this point

Rep Power: 1

Solved Threads: 10

cmhampton

Offline

Junior Poster in Training

Re: Problems Joining Query

Jul 11th, 2008

Can you give me a little more information about what this particular query is trying to do? You may not need to use UNION, but perhaps different JOINs.

•

Join Date: Jul 2008

Posts: 5

Reputation: bartbauldry is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

bartbauldry bartbauldry is offline

Offline

Newbie Poster

Re: Problems Joining Query

Jul 14th, 2008

Hello,

The results I would like from the query is 4 columns, one with an hourstamp (0-23),

the next column with an average hourly hits (i have done as count each hour and / by number of days),

the next column with today’s hits so far,

and final column as the number of hits who have visited more than 1 page

If you would like me to post some sample data i can,

Thanks for your help
Bart

Last edited by bartbauldry : Jul 14th, 2008 at 3:51 am.

•

Join Date: Mar 2007

Posts: 18

Reputation: wujtehacjusz is an unknown quantity at this point

Rep Power: 2

Solved Threads: 7

wujtehacjusz wujtehacjusz is offline

Offline

Newbie Poster

Re: Problems Joining Query

Jul 14th, 2008

I think that you need to join query one with query two... try:

Help with Code Tags

(Toggle Plain Text)

SELECT t1.*, t2.actualVisits
FROM (SELECT hourStamp, 
SUM(CASE WHEN datestamp < GETDATE() THEN 1 ELSE 0 END) / @numberofdays as HourAverage, 
SUM(CASE WHEN dateStamp BETWEEN DATEADD(d,DATEDIFF(d,0,GETDATE()),0) AND GETDATE() THEN 1 ELSE 0 END) as HourToday

FROM webstats

GROUP BY hourStamp) t1

LEFT OUTER JOIN 

(SELECT hourstamp, COUNT(id) AS ActualVisits
FROM webStats
WHERE id IN                            
(SELECT ws.id
FROM webStats AS ws INNER JOIN webStatsTrace AS wst ON ws.id = wst.statsid
WHERE (ws.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE())
GROUP BY ws.id HAVING (COUNT(wst.statsid) > 1))
GROUP BY hourstamp) t2 ON t1.hourStamp=t2.hourStamp

ORDER BY t1.hourStamp