通过 StoredProcedure 搜索每日/每周/每月记录计数 [英] Daily/Weekly/Monthly Record Count Search via StoredProcedure

问题描述

使用 MS SQL Server.我制作了一个名为 SP_Get_CallsLogged 的存储过程.

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

我有一个名为 TRN_Call 的表，它有一个名为 CallTime 的列，它是一个 DateTime.

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. 开始日期(日期时间)

结束日期(日期时间)

周期(每日/每周/每月)(varchar)(来自 DropDownList)

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.

例如

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

并将这些计数发送到 Web 应用程序，以便这些计数可以在 Crystal Reports 中列出.

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

推荐答案

我将编辑您的存储过程以传递在您的网页中选择的开始日期和结束日期，以便您可以将它们包含在 WHERE子句.

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

每周可能非常复杂.

如果您检查此 link 你会看到一个函数(如下)，你可以使用它来帮助你获得每周结果

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.还没来得及做一年)

(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  

这篇关于通过 StoredProcedure 搜索每日/每周/每月记录计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！