All geek questions in one place

Daily / Weekly / Monthly counting the number of entries through StoredProcedure

Using MS SQL Server . I created a stored procedure called SP_Get_CallsLogged .

I have a table named TRN_Call , and it has one column called CallTime , which is DateTime.

I have a web page in my application where the user enters: -

  • StartDate (DateTime)

  • EndDate (DateTime)

  • Period (daily / week / month) (varchar) (from DropDownList)

I want to get the number of records of these calls in my TRN_Call table based on the specified period (Daily / Weekly / Monthly) selected by the user in DropDownList.

eg.

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

Thus, the number of records between these aforementioned dates (StartDate + EndDate) must be received in such a way that I can refer to these accounts separately, i.e. 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 these counts to a web application so that these counts can be listed in Crystal Reports.

+4
source share
1 answer

I would edit your stored procedure to pass the start date and end date selected on your web page so that you can include them in the WHERE .

 -- Daily SELECT CallTime, COUNT(*) AS 'Records Found' FROM TRN_Call WHERE CallTime BETWEEN @StartDate AND @EndDate GROUP BY CallTime

Weekly can be quite challenging.

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

eg. So create a 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 can run the code below, which should be grouped by week. To choose between whether you want a day, a week, or a year, you must also go through the period selected by the user into the stored procedure.

(ps. Havent got to the year)

 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
+2
source

Source: https://habr.com/ru/post/1304236/

More articles:


All Articles