Solved

sql query to count # of visits

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Dump exec output to table 3 23
SQL Quer 4 23
MS SQL query to show nearest date 6 44
How to use spatial data types in SQL Server Database project 2 24
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

820 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