Daily/Weekly/Monthly Record Count Search via SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Daily/Weekly/Monthly Record Count Search via SP

Author

Topic

muzaffaralirana
Starting Member

5 Posts

Posted - 2010-03-16 : 05:10:21

Using MS SQL Server.I have made a Stored Procedure naming "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.

Sincerely,
Muzaffar Ali Rana.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 05:29:18

best thing is to bring data as


CREATE PROC SP_Get_CallsLogged
@StartDate datetime,
@EndDate datetime,
@Period varchar(10)
AS
SELECT YEAR(Date) AS Yr,MONTH(Date) AS Mnth,DATEPART(wk,Date) AS Week,COUNT(*) AS Count
FROM YourTable
GROUP BY YEAR(Date),MONTH(Date),DATEPART(wk,Date)
GO

then do dynamic grouping on year/month/week based on passed value of period in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

muzaffaralirana
Starting Member

5 Posts

Posted - 2010-03-16 : 10:43:58

Thanks a lot visakh16.

Issue resolved.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:23:48

gr8
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

muzaffaralirana
Starting Member

5 Posts

Posted - 2010-03-26 : 09:43:59

I have a question in connection to this question earlier posted by me:-
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

My requirement is not just to search the Weekly or Monthly Count.I have to show the Start-Date and End-Date(including the Count) for a specific range.

Weekly:-
Start-Date End-Date Count
15 Jan 2010 22 Jan 2010 21
22 Jan 2010 29 Jan 2010 73

These above results are coming in separate results, I want to combine them and put in single result. I mean, by my query these results are coming 1-by-1 in different result windows.

Monthly:-
Start-Date End-Date Count
01 Jan 2010 01 Feb 2010 118
01 Feb 2010 01 Mar 2010 84

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 03:40:18

for that you need to bring data as it is from table without applying any group and in report group by appropriate level (year,month,week,...) and then take Min(Fields!date.value),Max(Fields!date.value),Count(Fields!YourCountField.value) in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources