Solved

sql query to count # of visits

Posted on 2010-11-30
3
383 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 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