• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

sql query to count # of visits

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
running32
Asked:
running32
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
GSGDBACommented:
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
 
CboudrozCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now