stored procedure to total dates
-
Am trying to write a stored procedure which counts the number of rows in a table who's entry date falls in a given date range, a total is listed for each month in the given date range. ie if the specifed date range was Feb '02 - May '02 the output would be:
Feb 12
Mar 32
Apr 23
May 21
To clarify the entrydate is a date field in the table tblparticipants, I think I need something along the lines of:
declare @startdate datetime, @enddate datetime, @monthcounter datetime
while @monthcounter <> @enddate
begin
select count(*) from tblparticipants
where entrydate = month(@monthcounter)
set @monthcounter = dateadd(month, 1, @monthcounter)
end
Any help in correcting the above would be greatly appreciated.
-
Use set based queries instead:
select datename(entrydate,m) Mon, count(*) Cnt
from tblparticipants
where entrydate between @start and @end
group by datename(entrydate,m)
* Noel -
A select count(*) from x where date >= date1 and date <= date2 should work, are you just wanting the number of rows?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Hey Noeld, can I stop say that I agree everytime you post now and just do it implicitly?
-
I HATE ADMITTING MY IGNORANCE, BUT I HATE NOT KNOWING SOMETHING EVEN MORE.
Please explain why you call your query "set based" versus Steve's query? Is it because a "set" of records is returned, or is that too simplistic?
(if you answer in a bulldozer fashion, I will probably understand better. Right Remi?
)I wasn't born stupid - I had to study.
-
Farrell,
I was refering to the tendency of someone new to SQL to "loop" on every scenario. It is a natural construct for most other languages and it takes a bit get used to write queries instead of loops
but once you get the gist of it you realize the inmense power that set based solutions deliver 
Right , Remi?
* Noel -
"(if you answer in a bulldozer fashion, I will probably understand better. Right Remi? )"
Yup
.The original approach is a while loop that does selects. Noeld's solution is a single select that fetches all the data at once, hence set based. The original query is still set based, but it returns only 1 row at the time, making it look more like a cursor (unset) than a set solution.
-
Looks likes it's a no to my questions.
When are you gonna agree to my posts now???
-
I keep typing slower than you do, dam!
Sure I do agree to yours as long as you post Before me
which is usually most of the time 
* Noel -
noeld, thanks for your very informative reply, as you guessed I'm coming from a procedural background and still trying to get my head around control structures VS sets. Have revised previous to:
use DofESQL5
declare @startdate datetime, @enddate datetime,
set @startdate = '1/2/1991'
set @enddate = '1/4/1991'
select datename(m,entrydate) mon, count(*) cnt
from tblparticipants
where entrydate between @startdate and @enddate
group by datename(m,entrydate)
Almost does the trick but I'm trying to get it to display the count for each month,year consecutively from startdate to enddate ie in this case
February x
March y
April z
-
Don't be modest... you beat me at least once today
. -
Not this time though... you had a good 15 minutes to finish the job
.Here's a solution (maybe not the best but it works regardless of the language) :
Declare @startdate as datetime
declare @enddate as datetime
set @Startdate = dateadd(YY, -3, getdate())
set @endDate = dateadd(d, -100, getdate())
Select DateName(YY, CrDate) as Year, DateName(M, CrDate) as Month, count(*) as Total, MIN(crDate) as SortDate
from dbo.SysObjects
where CrDate between @startdate and @enddate
group by DateName(YY, CrDate), DateName(M, CrDate)
order by DateName(YY, CrDate), Datepart(M, MIN(crDate))
-
OOps!
I was working on something else
* Noel -
Do you have a better solution than this one?
I'd like to flush out the last column so that no useless data is sent back (I can do a derived table a select from that but I guess there are better ways to do this).
-
there are two approaches (that I know of)
one is to mix before grouping and extract after you do
the other which is simpler is to add level of wrapping
* Noel
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply