Need help in writing a SQl Server procedure with date parameter

It should be sort by Name and another one would belike based on Team's average Weekly , Monthly and Yearly too.

I have the table created in my database with structure:

create table phone_status
(
Team nvarchar(255),
rep nvarchar(255),
total_calls float null,
total_duration float null,
date varchar(8)
);

The source file column total_duration is been formated to text , so that I can load the file easily to my DB,
to get the proper data I am suing the below query to get the time format :

SELECT CONVERT(varchar(10),convert(datetime,total_duration,103),108)
from phone_status;


After loading the file into DB can we have stored procedure created , as evetime I cannot link the excel file to the DB due to insufficiant privelages ......

And also help me if I can have some trigger on before insert to table to convert the data into how I get the record after running the query :

SELECT CONVERT(varchar(10),convert(datetime,total_duration,103),108)
from phone_status;

This code is working for me but only one thing if you coluld help me in modifying the code in such a way that I wan to see the data for each rep monthly , yearly and weekly in a single row not in column wise .

WITH CTE AS
(
SELECT *
	,(DATEPART(HOUR,[Total_Duration])*60 * 60 + DATEPART(MINUTE,[Total_Duration])*60 + DATEPART(SECOND,[Total_Duration])) AS Seconds
	,DATEPART(week,[DATE]) AS Weekly
	,DATEPART(MONTH,[DATE]) AS Monthly
	,DATEPART(YEAR,[DATE]) AS Yearly
 FROM phone_status
)
,Weekly AS
(
	SELECT  'Week: ' + CONVERT(VARCHAR(3),Weekly) AS Category
			,Team
			,Rep
			,SUM([Total_Calls]) AS [Total Calls]
			,AVG([Total_Calls]) AS [Avg Calls]
			,SUM(Seconds) AS [Total Duration]
			,AVG(Seconds) AS [Avg Duration]
	FROM CTE
	GROUP BY Weekly,Team,Rep
)
,Monthly AS
(
	SELECT  'Month: ' + CONVERT(VARCHAR(2),Monthly) AS Category
			,Team
			,Rep
			,SUM([Total_Calls]) AS [Total Calls]
			,AVG([Total_Calls]) AS [Avg Calls]
			,SUM(Seconds) AS [Total Duration]
			,AVG(Seconds) AS [Avg Duration]
	FROM CTE
	GROUP BY Monthly,Team,Rep
)
,Yearly AS
(
	SELECT  'Year: ' + CONVERT(VARCHAR(4),Yearly) AS Category
			,Team
			,Rep
			,SUM([Total_Calls]) AS [Total Calls]
			,AVG([Total_Calls]) AS [Avg Calls]
			,SUM(Seconds) AS [Total Duration]
			,AVG(Seconds) AS [Avg Duration]
	FROM CTE
	GROUP BY Yearly,Team,Rep
)
SELECT * FROM Weekly
UNION ALL
SELECT * FROM Monthly
UNION ALL
SELECT * FROM Yearly

Select allOpen in new window

Ops this is not what I wanted as  a Procedure
I want to calculate the Week to date, month to day and year to date calculation and in one single row.

My mistake as I was not clear as 1st stage

I made 3 views and mde my work as the comment was not accurate but still helped me to doa work around.

Closing this thanks

thanks for your code....