Daily/Weekly/Monthly Record Count Search via StoredProcedure

Question

Using MS SQL Server.I have made a Stored Procedure named SP_Get_CallsLogged.

I have a table named TRN_Call, and it has one column named CallTime which is a DateTime.

I have a web-page in my application where the User enters:-

1. StartDate (DateTime)

2. EndDate (DateTime)

3. Period (Daily/Weekly/Monthly) (varchar) (from DropDownList)

I want to get the Record Count of those calls in my table TRN_Call on the basis of the specified Period(Daily/Weekly/Monthly) selected by user in the DropDownList.

e.g.

StartDate ='1/18/2010 11:10:46 AM'
EndDate   ='1/25/2010 01:10:46 AM'
Period    =Daily

So the record count between these above mentioned dates(StartDate+EndDate) should come in a manner so that I can refer to those counts separately i.e. the following:-

Date 1/18/2010 Records Found 5
Date 1/19/2010 Records Found 50
Date 1/20/2010 Records Found 15
Date 1/21/2010 Records Found 32
Date 1/22/2010 Records Found 12
Date 1/23/2010 Records Found 15
Date 1/24/2010 Records Found 17
Date 1/25/2010 Records Found 32

and send those counts to the Web Application so that these counts could be listed then in the Crystal Reports.

Answer 1

I would edit your stored procedure to pass the start date and end date selected in your web page so that you can include them in the WHERE clause.

-- Daily
SELECT CallTime, COUNT(*) AS 'Records Found'
FROM TRN_Call
WHERE CallTime BETWEEN @StartDate AND @EndDate
GROUP BY CallTime

Weekly can be quite complex.

If you check this link you will see a function (below) which you can use to help you get the weekly results

e.g. So create the function below

create function FiscalWeek (@startMonth varchar(2), @myDate datetime)  
returns int  
as  
begin  
declare @firstWeek datetime  
declare @weekNum int  
declare @year int  
set @year = datepart(year, @myDate)+1  
--Get 4th day of month of next year, this will always be in week 1  
set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102)  
--Retreat to beginning of week  
set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)  
while @myDate < @firstWeek --Repeat the above steps but for previous year  
 begin  
  set @year = @year - 1  
  set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102)  
  set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)  
 end  
set @weekNum = (@year*100)+((datediff(day, @firstweek, @myDate)/7)+1)  
return @weekNum  
end  

Now, you should be able to run the code below which should group up by weeks. To choose between if you want Day, Week or Year you will also have to pass period selected by the user to your stored procedure.

(ps. Havent got round to doing year yet)

SELECT dbo.FiscalWeek('04', CallTime), dbo.FiscalWeek('04', CallTime)%100 ,  COUNT(*) AS 'Records Found - Weekly'
FROM TRN_Call
WHERE CallTime BETWEEN @StartDate AND @EndDate
GROUP BY dbo.FiscalWeek('04', CallTime), dbo.FiscalWeek('04', CallTime)%100