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

问题描述

使用 MS SQL Server 。我已创建一个名为 SP_Get_CallsLogged 的存储过程。

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

我在用户输入的应用程序中有一个网页： -

1. （日期时间）

StartDate


2. 期间（每日/每周/每月）（varchar）（来自DropDownList）

我想在指定期间的基础上获取我的表格中 TRN_Call 的调用的记录计数

例如

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

因此，上述日期（StartDate + EndDate）之间的记录计数应该是这样的，分别计数，即以下： -

 日期1/18/2010记录找到5 
日期1/19/2010找到的记录50 
日期1/20/2010找到的记录15 
日期1/21/2010找到的记录32 
日期1/22/2010找到的记录12 
日期1/23 / 2010发现记录15 
日期1/24/2010记录找到17 
日期1/25/2010找到的记录32 
  

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

解决方案

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

   - 每日
 SELECT CallTime，COUNT（*）AS'Records Found'
 FROM TRN_Call 
 WHERE CallTime BETWEEN @StartDate AND @EndDate 
 GROUP BY CallTime 
  

如果您选中此链接，您将看到一个功能（下面），您可以用它来帮助您获得每周结果

例如所以创建下面的函数

  create function FiscalWeek（@startMonth varchar（2），@myDate datetime）
返回int 
 as 
 begin 
 declare @firstWeek datetime 
 declare @weekNum int 
 declare @year int 
 set @year = datepart（year，@myDate）+ 1 
  - 获取明年的第4天，这将总是在第1周
设置@firstWeek = convert（datetime，str（@year）+ @ startMonth + '04'，102）
  - 回到周初
 set @firstWeek = dateadd（day，（1-datepart（dw，@firstWeek）），@firstWeek）
 while @myDate< @firstWeek  - 重复上面的步骤，但是上一年
 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 
  

现在，您应该可以运行下面的代码，应该按星期分组。要选择日期，周或年份，您还需要将用户选择的时间段传递到存储过程。

（ps。Havent年份）

  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 
  

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.

解决方案

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  

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