Solved

sql query to count # of visits

Posted on 2010-11-30
3
371 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
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Viewers will learn how the fundamental information of how to create a table.

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now