Searching Week-wise/Month-wise record counts(number) and the StartDate+EndDate(datetime) of the Week
- by Muzaffar Ali Rana
I have a question in connection to this question earlier posted by me:-
http://stackoverflow.com/questions/2452984/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.
----- *** TABLE CREATION OF #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
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.
