搜索周/月记录计数(数字)和周/月的开始日期和结束日期(日期时间),并在两个日期之间搜索
我有一个与我之前发布的问题相关的问题:-
每日/通过 StoredProcedure 进行每周/每月记录计数搜索
我希望解决每周和每月的调用计数、每日的问题。
问题编号@1:每周的呼叫计数以及一周的开始日期和结束日期
我已经搜索了一周的开始日期和结束日期,包括它们各自的呼叫计数以及下面提到的查询。但问题是,尽管我使用了临时表(#TempTable+#TempTable2),但我无法在一张表中获得结果。请在这方面帮助我。
注意:表创建评论为多次执行。
--CREATE TABLE #TempTable(StartDate datetime,EndDate datetime,CallCount numeric(18,5))
--CREATE TABLE #TempTable2(StartDate datetime,EndDate datetime,CallCount numeric(18,5))
DECLARE @StartDate datetime,@EndDate datetime,@StartDateTemp1 datetime,@StartDateTemp2 datetime,@EndDateTemp datetime,@Period varchar(50);
SET @StartDate='1/1/2010'; SET @EndDate='2/28/2010';
SET @StartDateTemp1=@StartDate; SET @StartDateTemp2=DATEADD(dd, 7, @StartDate );
SET @Period='Weekly';
IF (@Period = 'Weekly')
BEGIN
WHILE ((@StartDate <= @StartDateTemp1) AND (@StartDateTemp2 <= @EndDate))
BEGIN
IF((@StartDateTemp1 < @StartDateTemp2 ) AND (@StartDateTemp1 != @StartDateTemp2) )
BEGIN
SELECT
convert(varchar, @StartDateTemp1, 106) AS 'Start Date',
convert(varchar, @StartDateTemp2, 106) AS 'End Date',
COUNT(*) AS 'Call Count'
FROM TRN_Call
WHERE (CallTime >= @StartDateTemp1 AND CallTime <= @StartDateTemp2 );
END
SET @StartDateTemp1 = DATEADD(dd, 7, @StartDateTemp1);
SET @StartDateTemp2 = DATEADD(dd, 7, @StartDateTemp2);
END
END
问题编号@2:每月的呼叫计数以及每周的开始日期和结束日期 在本例中,我进行相同的搜索,但必须搜索呼叫计数以及当月的开始日期和结束日期。请在这方面帮助我。
DECLARE @StartDate datetime,@EndDate datetime,@StartDateTemp1 datetime,@StartDateTemp2 datetime,@EndDateTemp datetime,@Period varchar(50);
SET @StartDate='1/1/2010'; SET @EndDate='4/1/2010'; SET @StartDateTemp1=@StartDate;
--SET @StartDateTemp2=@StartDate;
SET @StartDateTemp2=DATEADD(mm, 1, @StartDate );
SET @Period='Monthly';
IF (@Period = 'Monthly')
BEGIN
WHILE ((@StartDate <= @StartDateTemp1) AND (@StartDateTemp2 <= @EndDate))
BEGIN
IF((@StartDateTemp1 < @StartDateTemp2 ) AND (@StartDateTemp1 != @StartDateTemp2) )
BEGIN
SELECT
convert(varchar, @StartDateTemp1, 106) AS 'Start Date',
convert(varchar, @StartDateTemp2, 106) AS 'End Date',
COUNT(*) AS 'Call Count'
FROM TRN_Call
WHERE (CallTime >= @StartDateTemp1 AND CallTime <= @StartDateTemp2 );
END
SET @StartDateTemp1 = DATEADD(mm, 1, @StartDateTemp1);
SET @StartDateTemp2 = DATEADD(mm, 1, @StartDateTemp2);
END
END
I have a question in connection to this question earlier posted by me:-
Daily/Weekly/Monthly Record Count Search via StoredProcedure
I want to have the Count of Calls on Weekly-basis and Monthly-basis, Daily-basis issue is resolved.
ISSUE NUMBER @1:Weekly-basis Count of Calls and Start-Date and End-Date of Week
I have searched the Start-Date and End-Date of Week including their individual Count of Calls as well in the below-mentioned query. But the problem is that I could not get the result in one single table, although I have used the Temporary Tables(#TempTable+#TempTable2). Kindly help me in this regards.
NOTE:Table Creation commented as for executing more than once.
--CREATE TABLE #TempTable(StartDate datetime,EndDate datetime,CallCount numeric(18,5))
--CREATE TABLE #TempTable2(StartDate datetime,EndDate datetime,CallCount numeric(18,5))
DECLARE @StartDate datetime,@EndDate datetime,@StartDateTemp1 datetime,@StartDateTemp2 datetime,@EndDateTemp datetime,@Period varchar(50);
SET @StartDate='1/1/2010'; SET @EndDate='2/28/2010';
SET @StartDateTemp1=@StartDate; SET @StartDateTemp2=DATEADD(dd, 7, @StartDate );
SET @Period='Weekly';
IF (@Period = 'Weekly')
BEGIN
WHILE ((@StartDate <= @StartDateTemp1) AND (@StartDateTemp2 <= @EndDate))
BEGIN
IF((@StartDateTemp1 < @StartDateTemp2 ) AND (@StartDateTemp1 != @StartDateTemp2) )
BEGIN
SELECT
convert(varchar, @StartDateTemp1, 106) AS 'Start Date',
convert(varchar, @StartDateTemp2, 106) AS 'End Date',
COUNT(*) AS 'Call Count'
FROM TRN_Call
WHERE (CallTime >= @StartDateTemp1 AND CallTime <= @StartDateTemp2 );
END
SET @StartDateTemp1 = DATEADD(dd, 7, @StartDateTemp1);
SET @StartDateTemp2 = DATEADD(dd, 7, @StartDateTemp2);
END
END
ISSUE NUMBER @2:Monthly-basis Count of Calls and Start-Date and End-Date of Week
In this case, I have the same search, but will have to search the Call Counts plus the Start-Date and End-Date of the Month. Kindly help me in this regards as well.
DECLARE @StartDate datetime,@EndDate datetime,@StartDateTemp1 datetime,@StartDateTemp2 datetime,@EndDateTemp datetime,@Period varchar(50);
SET @StartDate='1/1/2010'; SET @EndDate='4/1/2010'; SET @StartDateTemp1=@StartDate;
--SET @StartDateTemp2=@StartDate;
SET @StartDateTemp2=DATEADD(mm, 1, @StartDate );
SET @Period='Monthly';
IF (@Period = 'Monthly')
BEGIN
WHILE ((@StartDate <= @StartDateTemp1) AND (@StartDateTemp2 <= @EndDate))
BEGIN
IF((@StartDateTemp1 < @StartDateTemp2 ) AND (@StartDateTemp1 != @StartDateTemp2) )
BEGIN
SELECT
convert(varchar, @StartDateTemp1, 106) AS 'Start Date',
convert(varchar, @StartDateTemp2, 106) AS 'End Date',
COUNT(*) AS 'Call Count'
FROM TRN_Call
WHERE (CallTime >= @StartDateTemp1 AND CallTime <= @StartDateTemp2 );
END
SET @StartDateTemp1 = DATEADD(mm, 1, @StartDateTemp1);
SET @StartDateTemp2 = DATEADD(mm, 1, @StartDateTemp2);
END
END
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我相信以下三个简单的查询足以满足您的需求。
每日
每周
每月
I believe following three simple queries suffice for what you need.
Daily
Weekly
Monthly
简单查询按月查找记录......
从
sales_invoice
WHEREcompany_id
= ' .10 中选择发票号、created_at、月份(created_at) 月号、计数(发票号) sale_count 。 ' AND状态
=“已批准”和年份(created_at) = 年(curdate()) 按月份(created_at)分组在此处输入代码
Simple query for find record month wise.....
SELECT invoice_no, created_at,month(created_at) month_no ,count(invoice_no) sale_count FROM
sales_invoice
WHEREcompany_id
= ' .10 . ' ANDstatus
= "Approved" and year(created_at) = year(curdate()) group by month(created_at)enter code here