Problems Joining Query

Please support our MS SQL advertiser: Intel Parallel Studio Home
Thread Solved

•
•
Join Date: Jul 2008
Posts: 5
Reputation: bartbauldry is an unknown quantity at this point 
Solved Threads: 0
bartbauldry bartbauldry is offline Offline
Newbie Poster

Problems Joining Query

 
0
  #1
Jul 11th, 2008
Hello I seem to be having a problem, I have this query that pulls back correct results

MS SQL Syntax (Toggle Plain Text) 
  1. 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
  2.  
  3. FROM webstats
  4.  
  5. GROUP BY hourStamp
  6. ORDER BY hourStamp
I also have this query that pulls back correct results
MS SQL Syntax (Toggle Plain Text) 
  1. SELECT hourstamp, COUNT(id) AS ActualVisits
  2. FROM webStats
  3. WHERE id IN                            
  4. (SELECT ws.id
  5. FROM webStats AS ws INNER JOIN webStatsTrace AS wst ON ws.id = wst.statsid
  6. WHERE (ws.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE())
  7. GROUP BY ws.id HAVING (COUNT(wst.statsid) > 1))
  8. GROUP BY hourstamp
  9. 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
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2008
Posts: 477
Reputation: khess is on a distinguished road 
Solved Threads: 8
Staff Writer
khess's Avatar
khess khess is offline Offline
Staff Writer

Re: Problems Joining Query

 
0
  #2
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 DaniWeb Staff Writer/Columnist/Host
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 5
Reputation: bartbauldry is an unknown quantity at this point 
Solved Threads: 0
bartbauldry bartbauldry is offline Offline
Newbie Poster

Re: Problems Joining Query

 
0
  #3
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:
MS SQL Syntax (Toggle Plain Text) 
  1. hourStamp   HourAverage HourToday   
  2. ----------- ----------- ----------- 
  3. 0           50          52          
  4. 1           46          34          
  5. 2           40          39          
  6. 3           41          52          
  7. 4           39          46          
  8. 5           44          33          
  9. 6           43          51          
  10. 7           42          43          
  11. 8           53          49          
  12. 9           62          66          
  13. 10          69          56          
  14. 11          76          64          
  15. 12          66          55          
  16. 13          73          65          
  17. 14          73          16          
  18. 15          81          0           
  19. 16          76          0           
  20. 17          64          0           
  21. 18          70          0           
  22. 19          69          0           
  23. 20          67          0           
  24. 21          69          0           
  25. 22          58          0           
  26. 23          50          0

And the Latter returns:
MS SQL Syntax (Toggle Plain Text) 
  1. hourstamp   ActualVisits 
  2. ----------- ------------ 
  3. 0           5            
  4. 1           3            
  5. 2           2            
  6. 3           3            
  7. 4           1            
  8. 5           6            
  9. 6           3            
  10. 7           2            
  11. 8           6            
  12. 9           14           
  13. 10          14           
  14. 11          24           
  15. 12          13           
  16. 13          15           
  17. 14          4
The latter will add rows as the hours pass....
Any Ideas?

Thanks
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 72
Reputation: cmhampton is an unknown quantity at this point 
Solved Threads: 10
cmhampton's Avatar
cmhampton cmhampton is offline Offline
Junior Poster in Training

Re: Problems Joining Query

 
0
  #4
Jul 11th, 2008
Try this:

SQL Syntax (Toggle Plain Text) 
  1. SELECT 
  2. 		hourStamp,
  3. 		NULL AS ActualVisits,
  4. 		SUM(CASE WHEN datestamp < GETDATE() THEN 1 ELSE 0 END) / @numberofdays as HourAverage, 
  5. 		SUM(CASE WHEN dateStamp BETWEEN DATEADD(d,DATEDIFF(d,0,GETDATE()),0) AND GETDATE() THEN 1 ELSE 0 END) as HourToday
  6. FROM 
  7. 		webstats
  8. GROUP BY 
  9. 		hourStamp
  10.  
  11. UNION
  12.  
  13. SELECT 
  14. 		hourstamp, 
  15. 		COUNT(id) AS ActualVisits,
  16. 		NULL AS HourAverage,
  17. 		NULL AS HourToday
  18. FROM 
  19. 		webStats
  20. WHERE 
  21. 		id IN                            
  22. 		(SELECT ws.id
  23. 		FROM webStats AS ws INNER JOIN webStatsTrace AS wst ON ws.id = wst.statsid
  24. 		WHERE (ws.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE())
  25. 		GROUP BY ws.id HAVING (COUNT(wst.statsid) > 1))
  26. GROUP BY 
  27. 		hourstamp
  28. ORDER BY 
  29. 		hourstamp

Use aliased NULLs to make the SELECT clauses of the two queries match.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 5
Reputation: bartbauldry is an unknown quantity at this point 
Solved Threads: 0
bartbauldry bartbauldry is offline Offline
Newbie Poster

Re: Problems Joining Query

 
0
  #5
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)

MS SQL Syntax (Toggle Plain Text) 
  1. hourStamp   ActualVisits HourAverage HourToday   
  2. ----------- ------------ ----------- ----------- 
  3. 0           <NULL>       50          52          
  4. 0           5            <NULL>      <NULL>      
  5. 1           <NULL>       46          34          
  6. 1           3            <NULL>      <NULL>      
  7. 2           <NULL>       40          39          
  8. 2           2            <NULL>      <NULL>      
  9. 3           <NULL>       41          52          
  10. 3           3            <NULL>      <NULL>      
  11. 4           <NULL>       39          46          
  12. 4           1            <NULL>      <NULL>      
  13. 5           <NULL>       44          33          
  14. 5           6            <NULL>      <NULL>      
  15. 6           <NULL>       43          51          
  16. 6           3            <NULL>      <NULL>      
  17. 7           <NULL>       42          43          
  18. 7           2            <NULL>      <NULL>      
  19. 8           <NULL>       53          49          
  20. 8           6            <NULL>      <NULL>      
  21. 9           <NULL>       62          66          
  22. 9           14           <NULL>      <NULL>      
  23. 10          <NULL>       69          56          
  24. 10          14           <NULL>      <NULL>      
  25. 11          <NULL>       76          64          
  26. 11          24           <NULL>      <NULL>      
  27. 12          <NULL>       66          55          
  28. 12          13           <NULL>      <NULL>      
  29. 13          <NULL>       73          65          
  30. 13          15           <NULL>      <NULL>      
  31. 14          <NULL>       75          51          
  32. 14          16           <NULL>      <NULL>      
  33. 15          <NULL>       84          85          
  34. 15          21           <NULL>      <NULL>      
  35. 16          <NULL>       80          78          
  36. 16          17           <NULL>      <NULL>      
  37. 17          <NULL>       67          49          
  38. 17          5            <NULL>      <NULL>      
  39. 18          <NULL>       70          0           
  40. 19          <NULL>       69          0           
  41. 20          <NULL>       67          0           
  42. 21          <NULL>       69          0           
  43. 22          <NULL>       58          0           
  44. 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
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 72
Reputation: cmhampton is an unknown quantity at this point 
Solved Threads: 10
cmhampton's Avatar
cmhampton cmhampton is offline Offline
Junior Poster in Training

Re: Problems Joining Query

 
0
  #6
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 5
Reputation: bartbauldry is an unknown quantity at this point 
Solved Threads: 0
bartbauldry bartbauldry is offline Offline
Newbie Poster

Re: Problems Joining Query

 
0
  #7
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 4:51 am.
Reply With Quote Quick reply to this message  
•
•
Join Date: Mar 2007
Posts: 18
Reputation: wujtehacjusz is an unknown quantity at this point 
Solved Threads: 7
wujtehacjusz's Avatar
wujtehacjusz wujtehacjusz is offline Offline
Newbie Poster

Re: Problems Joining Query

 
0
  #8
Jul 14th, 2008
I think that you need to join query one with query two... try:

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
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 5
Reputation: bartbauldry is an unknown quantity at this point 
Solved Threads: 0
bartbauldry bartbauldry is offline Offline
Newbie Poster

Re: Problems Joining Query

 
0
  #9
Jul 14th, 2008
It works!

Thanks to everyone for their help.
Thanks again

Bart
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MS SQL Forum
Thread Tools Search this Thread



Tag cloud for MS SQL
"last autogrowth business connectingtodatabaseinuse count cursor data database dateadd datepart day" dbsize deadlock delete_trigger exploit getdate hack highperformancecomputing hpc hpcserver2008 ibm iis limit loop maximum microsoft ms mssql multiple multithreading news number password permission position query reporting result security server services sets single source sql sql-injection sqlserver sqlserver2005 subtype supercomputing supertype tables uniqueid update view weekday
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC