[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how do I create a sql 2005 server store procedure to return a summary count for certain fields data based on date range

Posted on 2009-04-16
2
Medium Priority
?
208 Views
Last Modified: 2012-05-06
expert -  The user will enter a time range say period 04/01/2009 to 04/15/2009. Based on that period I need to count up and list all existing "tom" "dick" and "harry" records with dates that fall in that  time range? I am curious how expert would do this. Could you please provide an SQL coded example for me to follow.
0
Comment
Question by:tcmmaxt
2 Comments
 
LVL 12

Assisted Solution

by:Nathan Riley
Nathan Riley earned 400 total points
ID: 24159882
select Namefield, count(*)
from tablename
where datefield >= cast('04-01-2009' as datetime)
and datefield <= cast('04-15-2009' as datetime)
group by Namefield
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1600 total points
ID: 24160054
To make Gallitin's code a stored procedure as requested.
create procedure up_GetData 
   @StartDate datetime
  ,@endDate datetime
as
set nocount on 
--This line will turn <= April 15 2009, which will only work if all datetimes have NO TIME (ie. midnight)
--  into the following day and uses a < operator instead of <=.  It is proper for inclusion of all
--  records on april 15.
set @enddate = dateadd(d, datediff(d, 0, @enddate) 1,0) 
select Namefield, count(*) cnt
from tablename
where datefield >= @startdate
and datefield < @enddate
group by Namefield

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

830 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