June 7, 2012 at 9:09 am
Hi,
I have the below data:
HOSTNAME SERVER CHANNEL DATE COUNTER_NM COUNTER_VAL TIME_CREATED
CGRTFVDZE App LMA 3/30/2012 0:00 I/O 16.9 3/21/2012 31:29.3
CRPR11E3E App LMA 4/15/2012 0:00 Paging 0.22 4/22/2012 2:23:3
CbnjuhjZE Web DSN 4/30/2012 0:00 MEMORY 453.00 4/22/2012 2:23:3
CRPKILO1DZE SQL LMA 5/1/2012 0:00 BUILT 0.04264 4/22/2012 2:23:3
CRRTE511B DB LMA 5/25/2012 0:00 CPUUtilz 31:29 4/22/2012 2:23:3
CRPLOKIPME App EMN 6/1/2012 0:00 DISKSPACE 164.8 6/1/2012 31:29.3
I need to create a stored procedure with gives the following output:
hostname, server, channel, date, counter_nm, counter_val
WHERE COUNTER_NM IN 'MEMORY,BUILT,DISKSPACE'
The results should be dispalyed for last two months (Date column to be considered for this) Column name should be week ending date. (4/6/2012 4/13/2012 4/13/2012 4/27 5/4 5/11.........)
It should be like this:
HOSTNAME SERVER CHANNEL counter_nm 4/6 4/13 .......
The data needs to be selected on weekly basis. like one week data should be displayed in one row. It will calculate max of counter_val. Pivoting may be required to display the results per week in a row.
Thanks in advance !!
June 7, 2012 at 9:35 am
What you are describing is certainly possible. However from what you posted the details are pretty elusive. Can you post some ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. Take a look at the first link in my signature for best practices when posting questions.
I suspect you will want to use a calendar table to get your results the way you want them. http://qa.sqlservercentral.com/articles/T-SQL/70482/[/url]
_______________________________________________________________
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 7, 2012 at 9:39 am
create table st:
CREATE TABLE [dbo].[STG_CAP_HLI](
[HOST_NAME] [varchar](255) NOT NULL,
[SERVER_ROLE] [varchar](255) NULL,
[CHANNEL] [varchar](255) NULL,
[DATE] [datetime] NOT NULL,
[COUNTER_NAME] [varchar](255) NOT NULL,
[COUNTER_VALUE] [float] NULL,
[DATE_TIME_CREATED] [datetime] NULL
) ON [EMDB_FG01]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[STG_CAP_HLI] ADD CONSTRAINT [DF_STG_CAP_HLI_DATE_TIME_CREATED] DEFAULT (getutcdate()) FOR [DATE_TIME_CREATED]
GO
June 7, 2012 at 9:45 am
These are the csv results:
CHJUSDDN,App,LMA,2012-03-30 00:00:00.000,I/O,16.9,2012-05-25 18:31:29.323
CDGDFGD545E,App,LMA,2012-04-03 00:00:00.000,MEMORY,0.22,2012-05-25 18:31:29.323
CRFGFG12ZE,WEB,LMA,2012-04-30 00:00:00.000,ScanRate,0,2012-05-25 18:31:29.323
CDFGDRFDGD,App,ESN,2012-05-12 00:00:00.000,RUNTIME,0,2012-05-25 18:31:29.323
BGFGHJ676RF,SQL,LMA,2012-05-31 00:00:00.000,CPU,0.0426399999999989,2012-05-25 18:31:29.323
HHJUI878DFE,DB,POR,2012-06-01 00:00:00.000,DiskSPACE,164.8,2012-05-25 18:31:29.323
i NEED COUNTER_VAL IN 'MEMORY, I/O, CPU'
INSERT DDL:
INSERT INTO [EMDB].[dbo].[STG_CAP_HLI]
([HOST_NAME]
,[SERVER_ROLE]
,[CHANNEL]
,[DATE]
,[COUNTER_NAME]
,[COUNTER_VALUE]
,[DATE_TIME_CREATED])
VALUES
(<HOST_NAME, varchar(255),>
,<SERVER_ROLE, varchar(255),>
,<CHANNEL, varchar(255),>
,<DATE, datetime,>
,<COUNTER_NAME, varchar(255),>
,<COUNTER_VALUE, float,>
,<DATE_TIME_CREATED, datetime,>)
GO
June 7, 2012 at 9:52 am
Nidhi G (6/7/2012)
These are the csv results:CHJUSDDN,App,LMA,2012-03-30 00:00:00.000,I/O,16.9,2012-05-25 18:31:29.323
CDGDFGD545E,App,LMA,2012-04-03 00:00:00.000,MEMORY,0.22,2012-05-25 18:31:29.323
CRFGFG12ZE,WEB,LMA,2012-04-30 00:00:00.000,ScanRate,0,2012-05-25 18:31:29.323
CDFGDRFDGD,App,ESN,2012-05-12 00:00:00.000,RUNTIME,0,2012-05-25 18:31:29.323
BGFGHJ676RF,SQL,LMA,2012-05-31 00:00:00.000,CPU,0.0426399999999989,2012-05-25 18:31:29.323
HHJUI878DFE,DB,POR,2012-06-01 00:00:00.000,DiskSPACE,164.8,2012-05-25 18:31:29.323
i NEED COUNTER_VAL IN 'MEMORY, I/O, CPU'
INSERT DDL:
INSERT INTO [EMDB].[dbo].[STG_CAP_HLI]
([HOST_NAME]
,[SERVER_ROLE]
,[CHANNEL]
,[DATE]
,[COUNTER_NAME]
,[COUNTER_VALUE]
,[DATE_TIME_CREATED])
VALUES
(<HOST_NAME, varchar(255),>
,<SERVER_ROLE, varchar(255),>
,<CHANNEL, varchar(255),>
,<DATE, datetime,>
,<COUNTER_NAME, varchar(255),>
,<COUNTER_VALUE, float,>
,<DATE_TIME_CREATED, datetime,>)
GO
Here is a question, can you copy and paste the above and use it directly in SSMS to populate your table?
June 7, 2012 at 10:08 am
What Lynn is saying is that what you posted is not readily consumable (and it is also not insert statements).
The format for this should be something like:
INSERT STG_CAP_HLI
select 'CHJUSDDN', 'App', 'LMA', '2012-03-30 00:00:00.000', 'I/O', '16.9', '2012-05-25 18:31:29.323' union all
select 'CDGDFGD545E', 'App', 'LMA', '2012-04-03 00:00:00.000', 'MEMORY', '0.22', '2012-05-25 18:31:29.323' union all
select 'CRFGFG12ZE', 'WEB', 'LMA', '2012-04-30 00:00:00.000', 'ScanRate', '0', '2012-05-25 18:31:29.323' union all
select 'CDFGDRFDGD', 'App', 'ESN', '2012-05-12 00:00:00.000', 'RUNTIME', '0', '2012-05-25 18:31:29.323' union all
select 'BGFGHJ676RF', 'SQL', 'LMA', '2012-05-31 00:00:00.000', 'CPU', '0.0426399999999989', '2012-05-25 18:31:29.323' union all
select 'HHJUI878DFE', 'DB', 'POR', '2012-06-01 00:00:00.000', 'DiskSPACE', '164.8', '2012-05-25 18:31:29.323'
So now we have a table and some data.
What should the output look like. You can try to explain it or even better just create a temp table with the columns you want and generate insert statements to that temp table. The advantage to you is that we can very clearly understand what you want for output and it gives us a template to see if the query we create will match.
_______________________________________________________________
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 7, 2012 at 11:25 am
I have a similar kind of proc that produces the results for weekly instead of monthly. Can I share that? DO u think it would be helpful?
Below is the result format:
These are the column names:
Host_name
server_role
CHANNEL
COUNTER_NAME
4/6/2012
4/13/2012
4/20/2012
4/27/2012
W1MAX
5/4/2012
5/11/2012
5/18/2012
5/25/2012
6/1/2012
W2_MAX
The max calculates the max counter value from each week. The dates represents friday of each week in past two months. If we have a new month starting on friday u can include that particular day.
June 8, 2012 at 7:54 am
hi,
can anyone tell me how to do this?
June 8, 2012 at 8:00 am
Nidhi G (6/8/2012)
hi,can anyone tell me how to do this?
Look at what you have posted and ask yourself if you think you provided anywhere near enough information. We are volunteers around here so making your request clear to understand and easy for us to work on will go a long way towards getting you an answer. Just posting stuff like column names and a vague idea of what you want is not going to get you any results.
Read the article I suggested (it is the first link in my signature).
If you post enough information this is very quick and easy to solve. I can't help today because I have an all day meeting but maybe somebody else will jump in and help IF you provide details.
_______________________________________________________________
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 8, 2012 at 9:17 am
Sorry If I was not able to provide the correct details. I read the article. and will take care of this in future.
but coming back to my original solution, can u tell me clearly what can I provide u that can help u..
June 8, 2012 at 9:22 am
Nidhi,
What have you tried so far? If you have a procedure for us to look at and fix, it will be a whole lot easier than creating the procedure from the scratch.
June 8, 2012 at 9:24 am
Nidhi G (6/8/2012)
Sorry If I was not able to provide the correct details. I read the article. and will take care of this in future.but coming back to my original solution, can u tell me clearly what can I provide u that can help u..
Here's a third voice to add to Lynn and Sean: if you can provide easily-consumable data in the form of CREATE TABLE followed by a series of INSERTS, which can be run in SSMS with no further editing or mucking about, then you will be rewarded with the assistance you require.
You cannot expect Lynn or Sean to prepare your sample data for you AND come up with the solution as well.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 8, 2012 at 9:28 am
ChrisM@Work (6/8/2012)
Nidhi G (6/8/2012)
Sorry If I was not able to provide the correct details. I read the article. and will take care of this in future.but coming back to my original solution, can u tell me clearly what can I provide u that can help u..
Here's a third voice to add to Lynn and Sean: if you can provide easily-consumable data in the form of CREATE TABLE followed by a series of INSERTS, which can be run in SSMS with no further editing or mucking about, then you will be rewarded with the assistance you require.
You cannot expect Lynn or Sean to prepare your sample data for you AND come up with the solution as well.
And to add to this a bit, create an empty sandbox database and test that the scripts you provide actually work and setup everything correctly. We also won't take the time to fix errors in the code you provide to setup the test environment.
June 8, 2012 at 9:31 am
+1 Lynn and ChrisM
June 8, 2012 at 12:03 pm
I have created a proc which gives the results on daily basis instead of weekly basis.. Will that be helpful??
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply