Filtering By Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Filtering By Date

Author

Topic

obinna
Starting Member

26 Posts

Posted - 2007-12-12 : 10:34:24

I have a View with a structure like this

VisitTimes Company
2007-07-10 14:24:38.000 Microsoft
2007-03-10 11:14:38.000 Microsoft
2007-12-01 13:04:56.000 SQLTeam
2007-12-13 12:54:52.000 GoldMan Sac
2007-08-11 02:15:38.000 Oracle
2007-02-11 12:45:04.000 SAP Ltd

I am asked to write a stored procedure that get a count of each count of visit on a START and END date
I wrote the below SP but am not getting the right result
I think the **where VisitTime >= @Start AND VisitTime <= @End)** is not being evaluated. Help pls

CREATE procedure dbo.GetVisits
@Start varchar(50),
@End varchar(50)
as
SELECT TOP 100 PERCENT COUNT(company) AS VisitCount, company
FROM visits.dbo.IViewVisits where EXISTS
(SELECT * FROM VISITS.dbo.IViewVisits where VisitTime >= @Start AND VisitTime <= @End)
GROUP BY company ORDER BY COMPANY ASC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 10:35:16

Because there is no relation between exist and outer query.

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-12 : 10:35:56

change to

VisitTime < dateadd(day, 1, @End)

make sure your @start and @end does not contain time

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 10:37:06

Also, the EXISTS is absolutely unnecessary!

CREATE PROCEDURE dbo.GetVisits
(
	@Start VARCHAR(50),
	@End VARCHAR(50)
)
AS

SET NOCOUNT ON

SELECT		COUNT(*) AS VisitCount,
		Company
FROM		Visits.dbo.IViewVisits
WHERE		VisitTime >= @Start
		AND VisitTime <= @End
GROUP BY	Company
ORDER BY	Company

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 10:41:30