Transact SQL :: How To Display Two Groups Of Data Across The Top

Nov 10, 2015

I have a Cost table with the following field:

Year, Quarter, Cost1, Cost2

I would like to write a SQL so that it will give me something like that, so that I will have 2 years across the top with the same same columns.

2016 
 
2015

Quarter
Cost1
Cost2
 
Cost1
Cost2

1
100
200

[code]....

View 3 Replies


ADVERTISEMENT

Transact SQL :: Display Data In Multiple Row

Aug 5, 2015

I have a table like dependent eg. 

Dependent 
emp id   Dept ID  Child Name  
1             11          C1
1             12          C2
1             13          C3
1             14          C4
1             15          C5

These is input table and i want output like 

Child1   Child2  Child3
C1          C2        C3
C4         C5          null

View 8 Replies View Related

Transact SQL :: Display Different Table Having Same Column With Different Data

Sep 25, 2015

I am having two table i.e( tbl_oldEmployee , tbl_NewEmployee ),which is having Column name Employee Name and City same in both table but inside column data is different in different table.but i want to view the Employee name and City from tbl_NewEmployee to tbl_oldEmployee which is having EmployeeId common with tbl_oldEmployee extra record also required (i.e  tbl_NewEmployee having 6 record  and tbl_oldEmployee having 10 record,so i need to display data from  tbl_oldEmployee but first 6 record which id match with tbl_NewEmployee id should be replaced and extra data from tbl_oldEmployee also display).

View 3 Replies View Related

Transact SQL :: Display Child Data Under Parent Row

Jun 25, 2015

I would like to display child row right after parent row with ORDER BY DataDate for below set of data.

DECLARE @DATA TABLE (DataUID INT PRIMARY KEY IDENTITY(1,1), DataId INT, DataName NVARCHAR(20), DataDate DATE, ParentDataName NVARCHAR(20))
INSERT INTO @DATA (DataId, DataName, DataDate, ParentDataName)
VALUES (1, 'child plan 1', '2015-06-09', 'Plan'), (1, 'child plan 2', '2015-06-08', 'Plan'),
(1, 'Design', '2015-06-01', NULL), (1, 'Implement', '2015-06-01', NULL),
(1, 'child Implement 1', '2015-06-09', 'Implement'), (1, 'child Implement 2', '2015-06-10', 'Implement'),
(1, 'Plan', '2015-06-01', NULL), (1, 'Operate', '2015-06-01', NULL)
select * from @DATA

1. as a example the child row 'child implement 1' & 'child implement 1' show correctly under parent 'Implement' with Order BY DataDate.

2. I'm looking for a SELECT query which should display 'child plan 1' & 'child plan 2' under parent 'Plan' with Order BY DataDate clause.

Below is the expected output I'm looking for,

View 6 Replies View Related

Display All Look Up Value Groups Even With Zeros

Nov 4, 2004

I have these values in a table:

dateserial_idauth_id
10/1/0410002
10/2/0410012
10/2/0410024
10/3/0410033
10/3/0410043
10/4/0410054
10/4/0410065
10/4/0410075

Then there is another table, which has the description of auth_ids

auth_idauth_desc
1In queue
2Viewing
3Working
4Checking I
5Processing
6Checking II
7Ordered

I would like to count the serial_id for each dates for each auth_id with desc and also DISPLAY zeros for auth_ids that are not present.

Is this possible. If so, can someone please tell me how I can accomplish this.

Thanks,
Saj

View 4 Replies View Related

Problem With Matrix (in Subreport, Multiple Groups), Groups Repeating First Row Data

Jan 25, 2008

I have a new SQL 2005 (SP2) Reporting Services server to which I've just upgraded and deployed some SSRS 2000 reports.

I have a subreport that contains a matrix with two groups. The report data seems to be inexplicably repeating the data for the first row in the group for all rows in the group. Example:









ID1
ID2
DisplayData

1
1
A

1
2
B

1
3
C

2
1
A

2
2
B

2
3
C

Parent group is on ID1, child group is on ID2, report would show:








1
1
A

2
A

3
A

2
1
A

2
A

3
A


Is this a matrix bug in 2005 SP2, or do I need to do something differently? I can no longer pull a comparison version from an SSRS 2000 server to verify, but I believe it was working as expected before...

View 2 Replies View Related

Transact SQL :: How To Display Data Party Name And Time Interval Wise In Server

Sep 9, 2015

i want to show data Party Name and Time interval wise. here is my table from where i will fetch data. so pasting table data here.

Call start Call duration Ring duration Direction Is_Internal Continuation Party1Name Park_Time
------------------------- ---------------- ------------- --------- ----------- ------------ --------------- -----------
2015/06/08 08:06:08 00:02:28 2 I 0 0 Emily 0
2015/06/08 08:16:38 00:00:21 0 I 0 1 Line 2.0 0
2015/06/08 08:16:38 00:04:13 5 I 0 0 Jen 0

[code]...

now i am not being able to cross join this CTE with my table to get data party name wise and time interval wise. say for if no data exist for a specific time interval then it will show 0 but each party name should repeat for time interval 9:00:00 - 9:30:00 upto 17:30:00. i like to add what filter need to apply to get data for incoming, outgoing, call transfer and miss call.

For Incoming data calculation
where direction='I' and
Is_Internal=0 and continuation=0 and
RIGHT(convert(varchar,[call duration]),8)<> '00:00:00'
For outgoing data calculation

[code]...

View 3 Replies View Related

Reporting Services :: How To Display Count Of Column Groups

Oct 14, 2015

How to count the column group values and how to print the counts on every column for column group.

I used below expression to get the total column group count.

=CountDistinct(Fields!MMU.Value, "DataSet3")

View 8 Replies View Related

Transact SQL :: How To Sum Sales Value Between Groups Of Values

Jul 3, 2015

I have a table 

Acct No         Sales
1                    90
2                   129
3                  1200
4                 270

The table in real life has much more data and more columns. I want to be able to select Acct No and sum of sales for each band

Bands: <250  //  250-500  // 500-1000 // 1000+

I want to be able to get a table as below:

Account No //  <250  //  250-500  // 500-1000 // 1000+
 1              //    90     //         0     //          0      //      0
 2              //    129   //         0    / /           0     //       0
 2              //    0      //         0    / /           0     //       1200
 2              //    0     //         270    / /           0     //       0

Is there any easy way of doing this?

View 4 Replies View Related

Transact SQL :: List All File-groups For All Databases?

May 27, 2009

I would like to SELECT all filegroup on an SQL server instance, is that possible?Or only per database?

View 21 Replies View Related

Transact SQL :: Find The Number Which Exists In Different Groups

May 25, 2015

I have a table which has 2 columns and the data is like below

API_Number        Group_Name

1234                     Group A
3241                     Group A
1234                     Group B
4567                     Group C
7896                     Group D
3241                     Group E

 I wanted to find the API numbers which are repeating in different groups. In the output I want

 API_Number           Group_Name

1234                       Group A,Group B
3241                       Group A,Group E

View 5 Replies View Related

Transact SQL :: Backup Primary File Groups With No Procedures?

Aug 20, 2015

I would have to handover my DB with only Primary tables to client as part of SLA. 

I am planning to keep these primary tables on a secondary file-group but how-ever, I will still have my procedures on primary file-group. 

How can I accomplish this with client having no exposure to my stored procedures. 

View 2 Replies View Related

Transact SQL :: Optimize Count Distinct For Multiple Groups Of Records?

May 21, 2015

I have a CTE returning a recordset which contains a column SRC.  SRC is a number which I use later to get counts and sums for the records in a distinct list. 

declare@startdate date = '2014-04-01'
declare@enddate date = '2014-05-01'
; with SM as
(
SELECT --ROW_NUMBER() OVER (PARTITION BY u.SRC ORDER BY u.SRC) As Row,
u.SRC,

[Code] ....

-- If Referral start date is between our requested dates

ref.Referral_Start_Date between @startdate and @enddate

OR

-- Include referrals which started before our requested date, but are still active during our date range.

(ref.Referral_Start_Date < @startdate and (ref.Referral_End_Date > @startdate OR ref.Referral_End_Date IS NULL ))
)
INNER JOIN c_sdt s on s.Service_Delivery_Type_Id = u.Service_Delivery_Type_Id
AND s.Service_Delivery_Unit_Id = 200
)
SELECT
count(distinct (case SRC when 91 then client_number else 0 end)) As Eligable_91,

[code]....

View 5 Replies View Related

Page Header And Table Header Display But Groups And Rows Do Not

Nov 6, 2007

I have a report that I created and the report was working until I added some fields to a group footer row in a table.

My table has 5 group levels. I had information displaying in the 5th level header group and detail. It was working fine. Then I added some fields to the 4th level group footer. Now it displays only the Page header, Table header, and the 4th level group footer data.

What happened to the rest of the data?

All the cells and rows I want to display have the Visibility Hidden set to false. I tried removing the objects I added (to the 4th level group footer) and it still does not work. Is this a bug or did I set something that is hiding the data.

Thanks,

Fred

View 1 Replies View Related

Reporting Services :: Display Columns When There Is No Data To Display

Apr 30, 2015

I would like to display a portion of report where there is data or no data

There is data subreport  display   

     Product Name Latex Gloves  
     Product ID      
xxxx5678

 There NO data in the subReport
 
  Product Name                          
   Product ID    

View 3 Replies View Related

Transact SQL :: How To Display Top Row Value Instead Of Null Value

Oct 31, 2015

I am using sql server 2012. suppose i have a table called cte which contains id and name columns . in name column there are null value . i want to display top row value instead of null value as like attached image. Here is query :

;with CTE As (

Select 1 as Id , 'Advance' as Name
union all
Select 2 as Id , NULL as Name
union all
Select 3 as Id , NULL as Name

[Code] ...

Expected Result :

I want to write normal select Query. i am not interest to using loop or cursor.

View 5 Replies View Related

Transact SQL :: Concatenate Display Name Using XML PATH

Oct 29, 2015

I need to concatenate the DisplayName column with distinct UserName and SysName using XML path

 SELECT [sysName], [User_Name],[DisplayName] FROM
 (SELECT v_Add_Remove_Programs.DisplayName0 AS [DisplayName], v_Add_Remove_Programs.Publisher0 AS [Publisher]
, v_R_System_Valid.Netbios_Name0 AS[sysName],v_R_System_Valid.User_Name0 AS [User_Name],v_Add_Remove_Programs.Version0 as [Version]
FROM [Offline].[dbo].v_Add_Remove_Programs
JOIN  [Offline].[dbo].v_R_System_Valid ON [Offline].[dbo].v_Add_Remove_Programs.ResourceID = [Offline].[dbo].v_R_System_Valid.ResourceID)

[code]....

View 3 Replies View Related

Transact SQL :: Display 0 With COUNT And GROUP BY

Aug 25, 2015

How can I display 0 when using COUNT and GROUP BY?I'm using SELECT CASE in my query. I was trying to use COALESCE but no result, COUNT result = 1. (there should be 0).

COALESCE((COUNT(DISTINCT (CAST((CASE
WHEN CurrStat = @Stat AND LogDate = @LogDate THEN Enumber ELSE 0 END) AS int)))), 0) AS InTrack,

View 5 Replies View Related

Transact SQL :: Display Records According To Date

Oct 2, 2015

I have a table that have student names and their birth dates just like below

a) tblStudentInfo

which shows records like below (Birth Dates are shown in Year-Month-Day format)

ID   StudentName        BirthDate   
1     ABC                       2002-12-25 
2     DEF                        2002-09-10  
3     GHI                        2002-09-19 
4     JKL                        2002-06-10 

[code]...

I want to perform a query that should display the upcoming birthday of all students according to today.Lets say, today is 2015-10-02, so the query should display the result according to today's date just like below

ID   StudentName        BirthDate   
1     ABC                       2002-12-25 
2     VWX                      2002-01-01
3     STU                        2002-02-03 
4     PQR                     2002-03-05 

[code]...

View 3 Replies View Related

Transact-sql Code To Display Preformatted Tables

Mar 3, 2006

altimebest1 writes "Please show sql codes to creating a table showing Value and quantity under a heading, the month the data was collected or the value and quantity in the 1st, 2nd, 3rd and 4th quarter values under a heading Year.
My source table is:
comCode char 7
monthCode char 2
year char 4
countryCode char 3
quantity int
value money
insurance money
freight money
i also have a lookup table for:


Commodities Countries
comCode char 7 countryCode char 3
comDescriptions varchar 255 countryName varchar 255


the format of the table i wish to create

Com Commodity Description Jan
Code Country of Destination ValueQuantity


Thank you for helping me and I been a constant visitor to your site and it's wonderful and gives great help to sql enthusiasts."

View 1 Replies View Related

Transact SQL :: Display Total Of Company Expenses

Oct 6, 2015

In the below query  want to display total of company expenses. I am unable to get the sum of weeklycomexpenses

SELECT DISTINCT e.EmpID, e.EmpName, e.StartDate, e.EndDate, ee.WeeklyComTotalExpenses FROM
EMPArgentTimeSheet AS e LEFT OUTER JOIN (SELECT CExpID, WeeklyComTotalExpenses FROM
CompanyArgentExpenses GROUP BY CExpID, WeeklyComTotalExpenses) AS ee ON ee.CExpID = e.EmpID where EmpName = 'Eberhard Neumann'
and (e.StartDate >='8/1/2015')AND (e.EndDate <= '8/31/2015') order by EmpID desc

output:

empid empname startdate enddate WeeklyComTotalExpenses
397Eberhard Neumann2015-08-23 00:00:00.0002015-08-29 00:00:00.00019.20
393Eberhard Neumann2015-08-16 00:00:00.0002015-08-22 00:00:00.000NULL
387Eberhard Neumann2015-08-09 00:00:00.0002015-08-15 00:00:00.00078.00
382Eberhard Neumann2015-08-02 00:00:00.0002015-08-08 00:00:00.00081.99

Total 8/1/2015 8/31/2015 179.19 //want to display this total info

View 9 Replies View Related

Transact SQL :: Display All Days Of A Given Month And Year

Oct 17, 2015

I need a simple query to display all the days of a given month and year

View 2 Replies View Related

Transact SQL :: Display Normal General Date Format

Jul 30, 2015

I've come accross this code and need to alter it to display a normal General Date format.

CASE WHEN p.BIRTHDTTM IS NULL THEN ''
ELSE RIGHT('0' + LTRIM(CONVERT(VARCHAR(20), BIRTHDTTM, 113)) , 20)
END AS BIRTHDTTM

The original data (which is a DOB field) looks like this

1936-08-14 00:00:00.000

And the code above is turning it into this which is good but means I cannot then format in SSRS

14 Aug 1936 00:00:00

So ideally I'd like the code above to be altered so that it gives me

14/08/1936

View 14 Replies View Related

Transact SQL :: Temp Table - Display Duplicate IDs In Multiple Rows

Jun 30, 2015

I have a temp table with the following columns and data 

drop table #temp
create table #temp (id int,DLR_ID int,KPI_ID int,Brnd_ID int)
insert into #temp values (1,2343,34,2)
insert into #temp values (2,2343,34,2)
insert into #temp values (3,2343,34,2)

[Code]....

I use the rank function on that table and get the following results

select rank() over (order by DLR_ID,KPI_ID,BRND_ID  ) Rown,* from #temp

I am interested only in Rown and Id columns. For each Rown number, I need to get the min(ID) in the second column and the duplicate ID should be in 3rd column as shown below.If i have 3 duplicate IDs , I should have 3 rows with 2nd column being the min(id) and 3rd column having one of the duplicate ids in ascending order(as shown in Rown=6)

View 7 Replies View Related

Transact SQL :: Get Query By Selecting Month From Dropdown List And Display Records?

Oct 8, 2015

Im trying to get query by selecting the month from dropdownlist and display the records .by using the below query I need to enter the date in tecxtboc then it will show the output

select Standard, Total, MonthName
from (SELECT Standard, COUNT(Standard) AS Total,
datename(month, ReportDate) as [MonthName]
FROM CPTable where
ReportDate >= @ReportDate

[Code] .....

View 5 Replies View Related

Show Groups With Empty Data

May 8, 2007

Hello everybody,

I have a matric that looks like this :

SALES RETURNS
Client1 100 50
Client2 40 0

But when there is no returns in the month, the column returns isn't displayed. So I get this:

SALES
Client1 100
Client2 40

How can I show this column even with empty data?

Thanks in advance for your help.
Zoz

View 5 Replies View Related

Link Specific Groups Of Data To A Recordset?

Nov 4, 2007

I am new to this forum so I am not sure if this is posted in the appropriate group.

Okay - this may be a silly question - what is a crosslink table? Is this easily achieved in the MySQL query browser? Here is some more details on what I am trying to achieve. I have seen it done but not sure where to start....

The products correspond as follows:

- To be eligible for 'product a' the buyer must meet two qualifications:

1. Specify their 'model of their engine.'
2. Specify the 'year' of their engine.

Is there a way to structure my tables so that if a person specifies 'a specific year' and 'specific model' the correct product A, B, or C will pull up for them?

Do I create separate tables named 'Engine Model' and 'Engine Year' -- and then link to the product table with foreign keys? If so, how do I specify more than one grouping. For example product A is compatible with 2004-2007 engines and 10 different engine models.

I am new to mySql -- but a quick study -- hopefully this is easy to implement.

thanks.

View 2 Replies View Related

SQL 2012 :: Rebalancing Data In File Groups

Apr 28, 2014

I am looking for the easiest way of rebalancing data across multiple files.

Instead of creating a secondary filegroup and then dropping and recreating all indexes in the database which is going to take ages (we have a lot of tables and indexes), I am trying to just add more files to the primary file group and then rebalance data evenly between these.

I guessed that adding the new files to the primary file group and then rebuilding all indexes on a table should redistribute the table over these multiple file groups evenly. This is not the case though. It does rebalance data a bit but I still end up with the majority on the first file that existed.

I have attached the script I am running, maybe it is something in the create database/file statements that is the issue.

Basically what I am seeing is to start off with the table is 160MB. I then add the file groups and rebuild all indexes on the table. The first file is then about 100MB and each of the three other files are about 20MB. I would expect them all to be the same size.

View 4 Replies View Related

SQL 2012 :: Data Collection Fails For 1 Of 4 AlwaysOn AG Groups

Jun 5, 2014

I have a two node SQL 2012 AlwaysOn HADR cluster (v11.0.3412) with 4 availability groups configured. The AG groups are set to synchronous mode and the secondary is not readable (we do not want the synchronous replica readable so we do not risk any reads causing contention so we maintain fast performance).

On the secondary we are getting a persistent failure with the Data Collector job called Collection_Set_3_Upload. The failure occurs within the second job step. That job step is executing the following command:

dcexec -u -s 3 -i "$(ESCAPE_DQUOTE(MACH))$(ESCAPE_DQUOTE(INST))"
The error message is as follows:

Log Job History (collection_set_3_upload) Step ID 2 Server CLUSTERNODE2
Job Name collection_set_3_upload
Step Name collection_set_3_upload_upload
Duration 00:00:07

[Code] ....

I know I can prevent this error message by enabling readable secondaries, but we do not want this.

I have tried stopping the data collection jobs and purging the cache directory but to no avail. It will succeed the first time then persistently fail again with the same message every time after that.

In addition, if I set the one failing AG group to readable secondary the job succeeds. So that means that 3/4 work fine, only this one is having an issue.

View 0 Replies View Related

Design Data Storage For Feature Similar To Facebook Groups

Mar 13, 2008

Ok so facebook groups have 100,000's of members. Members can be part of an unlimited number of groups, and a group can have an unlimited number of members.

Comma Deliniated String seems absurd. Many-2-Many Database relationship seems like it won't scale well t the 10's of thousands and 100's of thousands of members (especially if you have 1000-5000 groups). A table for each group would work but thats a bit over the top in my opinion. XML file doesn't seem to be any better than the above options.

I am no database guru, but I can't figure out a scalable method of doing this, be it with or without a database. I need something that can support 10 groups that have 20 members each OR 1000 groups with 100,000 members each.

Any help, suggestions, or kicked in the right direction would be most appreciated.

View 3 Replies View Related

T-SQL (SS2K8) :: Distribute Data Into Groups Based On Existing Numbers?

Aug 11, 2014

i've been looking at moving one of our processed from excel (+vba) into t-sql to make life easier but am stuck.

We have lots of tasks that are assigned to work groups which we want to distribute evenly across the work groups. This is a simple task for ntile.. However when these tasks are no longer required they are removed which leaves the groups uneven. When new tasks are added we want to still try to keep these groups balanced.

EG Existing groups :

GroupName - Task Count
Group1 - 1000
Group2 - 999
Group3 - 998

If we were to add 6 new tasks they would have more assigned to Group 2 & 3 as they have less than group 1.

Task 1 - Group3
Task 2 - Group3
Task 3 -Group2
Task 4 - Group1
Task 5 - Group2
Task 6 - Group3
Sample tables
Create table GroupTable
(GroupID int, Name varchar(200) )
Insert into GroupTable values (1,'Group1')
Insert into GroupTable values (2,'Group2')
Insert into GroupTable values (3,'Group3')

Create table Jobs(jobid int identity(1,1), name varchar(100),Groupid int)

--Existing tasks

Insert into Jobs(name,Groupid) values ('Task1',1)
Insert into Jobs(name,Groupid) values ('Task2',1)
Insert into Jobs(name,Groupid) values ('Task3',1)
Insert into Jobs(name,Groupid) values ('Task4',1)
Insert into Jobs(name,Groupid) values ('Task5',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task7',3)

-- New tasks

Insert into Jobs(name) values ('TaskA')
Insert into Jobs(name) values ('TaskB')
Insert into Jobs(name) values ('TaskC')
Insert into Jobs(name) values ('TaskD')
Insert into Jobs(name) values ('TaskE')
Insert into Jobs(name) values ('TaskF')

This gives us 6 unassigned tasks and a uneven group assignment

GROUPNAME TASK_COUNT
<none> 6
Group1 4
Group2 3
Group3 2

This means the new tasks will be assigned like this

TaskA - Group3
TaskB - Group3
TaskC - Group2
TaskD - Group1
TaskE - Group2
TaskF - Group3

View 5 Replies View Related

SQL 2012 :: 500GB Data Warehouse - How To Split It Into File Groups

Aug 21, 2014

OK, so I have:

- 500 GB DW
- 5 GB in smaller DBs
- 220 GB TempDB
- 350 GB in Log files.

My machine is Fujitsu Primergy 64 cores (with HT) and 192 GB RAM.

I have several IO locations:

- 540 GB in-server HDD 15k RAID10
- 1 TB HDD 15k RAID10 on SAN (separete controller)
- 2 TB HDD 15k RAID10 on SAN (same controlller as below)
- 800GB SSD RAID10 on SAN (same controller as above)

Data warehouse has 2 fact tables that are absolutely crucial and quite large.

Now i want to organize DB into several Filegroups and put them on different drives. Filegroups I'm thinking of:

- FILEGROUP1: for 1st crucial Fact Table
- FILEGROUP2: for 2nd crucial Fact Table
- FILEGROUP3: for tempDB
- FILEGROUP4: for dimensions data
- FILEGROUP5: for the rest of facts data
- FILEGROUP6: for dimensions indexes
- FILEGROUP7: for the rest of facts indexes
- FILEGROUP8: for 1 log file of one smaller DB (its in full-recovery and its quite large)
- FILEGROUP9: for the rest of log files
- FILEGROUP10: others

How should I organize them across available drives? I was thinking about sth like:

800 GB SSD: FILEGROUPS 1-3
2 TB RAID10: FILEGROUPS 5+7+8
1 TB RAID10: FILEGROUPS 4+6+10
540 GB in-server: FILEGROUP 9

I know that having multiple filegroups on the same drive is pointless regarding performance, but in future i could actually add some more drives, so i want to separate them now.

Also - how much files per filegroups should i create? Considering 1 or 2. Except TempDB where I am going for 4.

View 2 Replies View Related

Collect Missing Index Data Into Central Repository From Sys.dm_db_missing_index_group_stats, Groups, Details

Nov 2, 2007



Greetings:
I am trying to gather into a central location the missing index data from the sys DMV's for dynamic index creation in the next step. In trying to use a cursor, I get the following errors:

Msg 154, Level 15, State 3, Line 20

variable assignment is not allowed in a cursor declaration.

Msg 102, Level 15, State 1, Line 94

Incorrect syntax near 'Get_Data'.

Msg 16916, Level 16, State 1, Line 2

A cursor with the name 'Get_Server' does not exist.

Msg 16916, Level 16, State 1, Line 3

A cursor with the name 'Get_Server' does not exist.


Here is the SQL:


--CREATE PROCEDURE usp_Get_Missing_Index_Data

--AS

--Declare @Sql2 nvarchar(4000)

Declare @Sql nvarchar(4000)

DECLARE Get_Server Cursor -- gets a server name from a list of servers

for

Select MachineName from rsqlaudit1.DBStatistics.dbo.servers

Open Get_Server

Declare @Server nchar(20)

Fetch Next from Get_Server Into

@Server

While (@@FETCH_STATUS = 0) --and (@@FETCH_STATUS <> -2)

BEGIN



DECLARE Get_Data Cursor

FOR

select @sql= 'select distinct id.*

, gs.avg_total_user_cost

, gs.avg_user_impact

, gs.last_user_seek

,gs.unique_compiles

from '+@Server+'.master.sys.dm_db_missing_index_group_stats gs

,'+@Server+'.master.sys.dm_db_missing_index_groups g

,'+@Server+'.master.sys.dm_db_missing_index_details id

where gs.group_handle = g.index_group_handle

and id.index_handle = g.index_handle

order by gs.avg_user_impact desc'

exec (@Sql)



Open Get_Data

DECLARE @Handle int,

@database smallint,

@object int,

@equality nvarchar(4000),

@inequality nvarchar(4000),

@Included nvarchar(4000),

@statement nvarchar(4000),

@avg_user_cost float,

@avg_user_impact float,

@last_seek datetime,

@compiles bigint

Fetch NEXT FROM Get_Data INTO

@Handle,

@database,

@object,

@equality,

@inequality,

@Included,

@statement,

@avg_user_cost,

@avg_user_impact,

@last_seek,

@compiles

While (@@FETCH_STATUS = 0) --and (@@FETCH_STATUS <> -2)

BEGIN

insert into rsqlaudit1.DBStatistics.dbo.Missing_Index_data

values (@Server,

@Handle,

@database,

@object,

@equality,

@inequality,

@Included,

@statement,

@avg_user_cost,

@avg_user_impact,

@last_seek,

@compiles)

FETCH NEXT FROM Get_Data into

@Server,

@Handle,

@database,

@object,

@equality,

@inequality,

@Included,

@statement,

@avg_user_cost,

@avg_user_impact,

@last_seek,

@compiles

Fetch Next from Get_Server Into

@Server

END

CLOSE Get_Data

DEALLOCATE Get_Data

GO

CLOSE Get_Server

DEALLOCATE Get_Server

GO

Any suggestions are appreciated.

Thanks,
Derek

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved