June 11, 2012 at 1:06 pm
I have below table:
CREATE TABLE [dbo].[test_table](
[NAME] [varchar](255) NOT NULL,
[SERVICE] [varchar](255) NULL,
[PORT_NAME] [varchar](255) NULL,
[DATE] [datetime] NOT NULL,
[AGENT_NAME] [varchar](255) NOT NULL,
[AGENT_VALUE] [float] NULL,
)
GO
INSERT INTO test_table VALUES
('Airtel','web','state','2012-04-05','tulsa','129.09')
INSERT INTO test_table VALUES
('Airtel','app','state','2012-04-06','newyork','7676.99')
INSERT INTO test_table VALUES
('Airtel','html','county','2012-04-11','dallas', '706.99')
INSERT INTO test_table VALUES
('Airtel','app','city','2012-04-13','washington','324.09')
INSERT INTO test_table VALUES
('Airtel','html','country','2012-04-19','seattle','12.99')
INSERT INTO test_table VALUES
('Att','web','state','2012-04-20','dallas','45.67')
INSERT INTO test_table VALUES
('Att','web','country','2012-04-23','tulsa','565.00')
INSERT INTO test_table VALUES
('Att','html','city','2012-04-27','dallas','87.98')
INSERT INTO test_table VALUES ('Att','web','county','2012-05-01','seattle','123.98')
INSERT INTO test_table VALUES ('reliance','app','city','2012-05-03','newyork','980.11')
INSERT INTO test_table VALUES ('reliance','html','state','2012-05-04','newyork','11.23')
INSERT INTO test_table VALUES ('reliance','app','city','2012-05-10','dallas','23.12')
INSERT INTO test_table VALUES
('verizon','html','county','2012-05-11','dallas','43.89')
INSERT INTO test_table VALUES
('verizon','html','country','2012-05-15','dallas','11.09')
INSERT INTO test_table VALUES
('verizon','app','country','2012-05-16','dallas','435.90')
INSERT INTO test_table VALUES
('verizon','app','state','2012-05-18','washington','876.98')
INSERT INTO test_table VALUES
('vongge','html','state','2012-05-23','washington','309.09')
INSERT INTO test_table VALUES
('vongge','web','country','2012-05-24','seattle','120.90')
INSERT INTO test_table VALUES
('vongge','app','city','2012-05-25','seattle','102.8')
INSERT INTO test_table VALUES
('vongge','app','county','2012-05-28','tulsa','209.88')
INSERT INTO test_table VALUES
('vongge','web','state','2012-05-30','washington','111.21')
INSERT INTO test_table VALUES
('Att','web','county','2012-05-31','seattle','123.70')
INSERT INTO test_table VALUES
('vonage','app','city','2012-06-01','newyork','900.11')
INSERT INTO test_table VALUES
('Att','web','county','2012-06-04','seattle','123.08')
INSERT INTO test_table VALUES
('reliance','app','city','2012-06-07','newyork','980.10')
INSERT INTO test_table VALUES
('Att','web','county','2012-06-08','seattle','133.98')
INSERT INTO test_table VALUES
('vonage','app','city','2012-06-11','newyork','940.11')
Need a stored proc which gives results according to the following conditions:
Condition 1: Agent_name in ‘dallas’,’seattle’,’newyork’
Condition 1: should take a input from user for date. If the user doesn’t provide any input it should take getdate().
First I need to select the data for past 8 weeks. Columns required in this selection are :
1.Narrow the result set with below query for the desired cols
(select Port_name, date, agent_value, agent_name from test_table where agent_name in ‘dallas’,’seattle’,’newyork’)
2.Select the rows from the above set where date column occurs in past 8 weeks.
3.Divide this result set into 8 rows with one row per week. For dividing the result set I need to calculate the max(agent_value) and group by port_name.For example:
1st week has 3 rows, calculate the max agent_value for this week and then display the row with max value.
Similarly do for all 8 weeks.
4.Now pivot the result in order to show in columns. The column names shoul display like below:
Port_name agent_value fri_w1 fri_w2 fri_w3 fri_w4 fri_w5 fri_w6 fri_w7 fri_w7
Fri_w* means I need to display the date of Friday for each week.
5.Now add two columns with Max1 and max2
6.Max1 calculates max agent_value for first four weeks and max2 calculates agent_value for net four weeks.
June 11, 2012 at 3:00 pm
This sounds a lot like homework. What have you tried so far?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2012 at 8:23 am
I am not able to narrow the result for last 8 weeks using current date..
June 12, 2012 at 8:26 am
Lynn has a great blog post with lots of common datetime routines. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
See if that helps.
I am willing to help but since this sounds like homework the best I will do is point you in the right direction.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2012 at 9:50 am
Sean Lange (6/12/2012)
Lynn has a great blog post with lots of common datetime routines. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]See if that helps.
I am willing to help but since this sounds like homework the best I will do is point you in the right direction.
I'm willing to help as well, but I would need to see what you have tried so I can help guide you along as well.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply