Need help in writing a SQl Server procedure with date parameter

pinkuray
pinkuray used Ask the Experts™
on
Hello Experts,

I have data file where i want to load the data into Sql Server 2005 and to write a procedure with date parameter so that I should be able to get the report like below:
Report output should be like as below:

Team,
Rep,
Total Calls,
Total Duration,
[ Week Agerage Call ],
[Week Average Duration]

And similarly I should be doing this for month and year where all togather I can get the report of weekly , monthly and yearly stats.

Attached is the data that I am having.




Test-data.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
It should be sort by Name and another one would belike based on Team's average Weekly , Monthly and Yearly too.
.NET Expert
Commented:
Go through below code

Just enable the adhoc query

It gives output as per your requirement

Please change the your excel file path in the query.

/*
--Neccessary step
--STEP 1
sp_configure 'show advanced options', 1
reconfigure
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
*/
;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 OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=C:\Users\vipul\Downloads\test-data.xls;Extended Properties=Excel 8.0')...PhoneData$
)
,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

Open in new window

Author

Commented:
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;
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Author

Commented:
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

Open in new window

Author

Commented:
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

Author

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

Author

Commented:
Closing this thanks

Author

Commented:
thanks for your code....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial