Transact-SQL Count of records returned by query

[glenellis]

Have records in SQL table, many records with same date.

Example
Rcd1 04/01/02
Rcd2 04/01/02
Rcd3 04/02/02
Rcd4 04/02/02

Need to perform stored procedure which will pass back the number of unique dates (in this case 2) represented within a date range that I ask for so I can determine how many columns to put in MSFlexGrid in Visual Basic.
I do not care about returning the Recordset, just the variable representing the # of unique dates.
I tried using the COUNT function but can't figure out how to attach a variable to COUNT?

Thanks in advance for your help.

 

[bperry]

You could try this (untested):
I am assuming you are going to pass in the from/to dates that you are interested in.
Let us know how it works.
-----------------------------
CREATE PROCEDURE MyProc (
@parmFrom datetime,
@parmTo datetime
)
AS
declare @myCount int
set @myCount =
Select COUNT(Date) as TheCount
from MyTable m
where m.date between @parmFrom AND @parmTo
return @myCount
GO
------------------------------
bperry

 

[tlbroadbent]

I'd slightly modify bperry's SP to count unique dates by utilizing a sub-query. You should use an Output parameter rather than the Return value. I've also coded the SP to work if the input parameters are null.
[tt]
CREATE PROCEDURE MyProc
@parmFrom datetime=null,
@parmTo datetime=null,
@myCount int output
AS

Select @myCount = Select COUNT(*) From
(Select Distinct MyDate From MyTable m
Where (@parmFrom Is Null Or m.MyDate >= @parmFrom)
And (@parmTo Is Null Or m.MyDate <= @parmTo) q

GO[/tt] Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[bperry]

I agree with the comment re: output parameters. The only issue is that it's a little more trouble to set up in the client, and hopefully the poster (glenellis in this case) will know how to do that.

 

[bperry]

Hi glenellis:
I did a crappy job for you on my first response. I missed the fact that you wanted a count of unique dates, plus there were a few syntax errors in what I proposed.

I agree with tbroadbent re: output parameters (although you will have to make sure your client code sets up to receive this parameter). His suggestion re: null dates is not a bad idea. So I now propose this stored procedure; it's a variation of tbroadbent's.
------------------------------
CREATE PROCEDURE MyProcNewProc
@parmFrom datetime=null,
@parmTo datetime=null,
@myCount int output
AS

Select @myCount = count(DISTINCT Date) From YourTable m
Where (@parmFrom Is Null Or m.Date >= @parmFrom)
And (@parmTo Is Null Or m.Date <= @parmTo)
GO
----------------------------------------
I hope this does it, and my apologies for my earlier errorz.
----------------------------------------
tbroadbent:
Thanku for improving my crap (it needed it).
Minor typo: I think you needed one more closing parenthesis just before the name of your derived table q.

Question: You'll notice I am now suggesting
count(DISTINCT Date), which I think eliminates the need for the derived table. Agree? Was there perhaps another reason for using q that I am overlooking?

rgrds,
bp

 

[tlbroadbent]

I agree that Count (Distinct Date) is better. Sometimes, I simply forget simple things. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.