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

问题描述

使用的 MS SQL服务器。我取得了一个名为存储过程 SP_Get_CallsLogged 。

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

我有一个表名为 TRN_Call ，而且它有一个名为列 CallTime 这是一个日期时间。

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. 开始日期（DATETIME）

结束日期（DATETIME）

EndDate (DateTime)

周期（每日/每周/每月）（VARCHAR）（从DropDownList中）

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

我想获得这些调用的记录计数在我的表 TRN_Call 在指定时间内（每日/每周/每月的基础上，由用户选择）在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

每周可以说是相当复杂的。

Weekly can be quite complex.

如果您选中此<一个href=\"http://www.sqlteam.com/article/returning-a-week-number-for-any-given-date-and-starting-fiscal-month\"相对=nofollow>链接你会看到一个功能（如下图），你可以用它来帮助您获得每周的结果。

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

例如。所以下面创建

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  

现在，你应该能够低于应该将上升周运行code。如果您希望每天，每周或你年也将不得不通过由用户选择期间存储过程中进行选择。

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屋！