Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql query to count # of visits

Posted on 2010-11-30
3
Medium Priority
?
409 Views
Last Modified: 2012-05-10
I would like to get the name, ID and count of the # of visits for a certain ID within a time frame.  I'm not sure how to select it so that I get all the names but only a count of the visits in a time period.

thanks
SELECT DISTINCT tblLUMHCData.lngDataID, tblLUMHCData.strData,
                          (SELECT     COUNT(lngIntakeID)
                            FROM          qselMHReferral
                            WHERE      dtmintake BETWEEN '1/1/2010' AND '12/31/2010') AS amount
FROM         qselMHReferral INNER JOIN
                      tblLUMHCData ON qselMHReferral.lngDataID = tblLUMHCData.lngDataID
WHERE     (qselMHReferral.strType = 'Refer_T')
GROUP BY tblLUMHCData.lngDataID, tblLUMHCData.strData
ORDER BY tblLUMHCData.strData

Open in new window

0
Comment
Question by:running32
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 34240905
what about this:

SELECT d.lngDataID, d.strData
 , (SELECT     COUNT(r.lngIntakeID)
    FROM          qselMHReferral r
     WHERE r.lngDataID = d.lngDataID
      AND r.strType = 'Refer_T'
     AND r.dtmintake BETWEEN '1/1/2010' AND '12/31/2010'
         ) AS amount
FROM   tblLUMHCData d
ORDER BY d.strData 

Open in new window

0
 
LVL 3

Expert Comment

by:GSGDBA
ID: 34240964
Query is as below.

select p.strData as name,   COUNT(lngIntakeID)  as Count_hit
from
 tblLUMHCData p, qselMHReferral c
where
c.dtmintake BETWEEN '1/1/2010' AND '12/31/2010' and
p.lngDataID=c.lngDataID and p.strdata='John'
0
 
LVL 7

Expert Comment

by:Cboudroz
ID: 34241572
This will return all name in table tblLUMHCData, whit or whitout visit in the period.

if you only want the one whit a visit in the period change the LEFT JOIN for a INNER JOIN

SELECT  
	tblLUMHCData.lngDataID
	, tblLUMHCData.strData
	, COUNT(*)
FROM         
	tblLUMHCData 
	LEFT JOIN qselMHReferral 
		ON qselMHReferral.lngDataID = tblLUMHCData.lngDataID
			AND dtmintake BETWEEN '1/1/2010' AND '12/31/2010'
			AND qselMHReferral.strType = 'Refer_T'
GROUP BY 
	tblLUMHCData.lngDataID
	, tblLUMHCData.strData
ORDER BY 
	tblLUMHCData.strData

Open in new window

0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question