T-SQL (SS2K8) :: Find All Related IDs?

Aug 13, 2015

I have a Transactional table containing IDs that relate to each other. The table highlights the transactions that move one ID to another, by storing an ID and a IDNew (Basically from ID to ID).

My requirement is to create a Inline Table Valued Function that will efficiently return a single ID column to show all related IDs by looking at the ID and the IDNew fields.
I need to pass in a single ID to get all relations or a list of IDs through xml.

I have managed to achieve this by making use of two recursive cte's and then combining the results with union statements to create a distinct list of values.

IF OBJECT_ID (N'tempdb..#Trans', N'U') IS NOT NULL
TranID int identity(1,1),


View 9 Replies


What Is The Best And Most Effective Way To Find The Most Related Phrases?

Dec 10, 2007

Hi, I am looking for a sql statemnt that find the most related phrases, how to do that? For example, I want to find the related phrases bellow for the following phrase: "How to install the software"
1- "Installation does not work"
2- "installation"
3- "steps to install the software"
It's obvoius that phrase number 2 and 3 are related to that phrase, how to do that using sql?

View 1 Replies View Related

Find All Tables That A Table Is Related To

Sep 8, 2006

how can i find all the names of tables that a specific table is related to in tsql?

View 3 Replies View Related

T-SQL (SS2K8) :: Select Minimum Date From Related Table

Apr 2, 2014

I am working on a query that seems very simple, but I just cannot seem to get it correct.

I want to be able to select Only 1 MemberAddress with the MIN(MoveDate) for each of the 3 Members. The results would be this:

Joe Smith, 2000-03-10, 1034 Sturgis Road, 115, Portland, Or, 77665
Sally Jones, 2001-01-02, 8970 Pierce Road, 25, Clear Bay, Washington, 96547
Beth Moore, 2006-05-30, 456 W. Blane Ave, NULL, Charleston, West Virgina, 56897

,FirstName VARCHAR(20)
, LastName VARCHAR(20)


I am not opposed to using CTE, I really like them, but I cannot figure this one out. But I will try most anything.

View 4 Replies View Related

T-SQL (SS2K8) :: Delete All Rows Satisfying Certain Condition From Table A And Related Records From B And C

Apr 14, 2015

I have around 3 tables having around 20 to 30gb of data. My table A related to table B by a FK and same way table B related to table C by FK. I would like to delete all rows satisfying certain condition from table A and all corresponding related records from table B and C. I have created a query to delete the grandchild first, followed by child table and finally parent. I have used inner join in my delete query. As you all know, inner join delete operations, are going to be extremely resource Intensive especially on bigger tables.

What is the best approach to delete all these rows? There are many constraints, triggers on these tables. Also, there might be some FK relations to other tables as well.

View 3 Replies View Related

T-SQL (SS2K8) :: Find Same Combination Of Rows?

May 10, 2015

I have this data as below. I need to find out the combination from the data and take a count of them

( nRef INT,
nOrd INT,
Token INT,
nNode INT,
sSymbol VARCHAR(50),


if you can see, the rows with column nRefNo 1 and 3 are same i.e. with same combination of Symbol viz. Silver and Castorseed.

Hence the desired output will be

Symbol Count
Castorseed-Silver 8

How to get this combination together and then take count of them. Please note i will be dealing with more than 5 million rows.

View 1 Replies View Related

Looping Thru Records To Find Related Records

Oct 31, 2007

Hi, I have had this problem for a while and have not been able solve it.

What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.

then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.

The table name is Adm_disc_Match_tbl

Basically what i have 4 fields.
Index_key = which is the patient common link (text)
ur_episode = this wil change for each Hospital (text)
Admission_datetime = patient admission date and time (datetime)
Discharge_datetime = patient discharge date and time (datetime)

example of data

Code: ( text )
Index_key,ur_episode,Admission_datetime,discharge_ datetime
HERBERT-7/1929,513884-1686900,4/07/2006 10:58,17/07/2006 13:37
HERBERT-7/1929,C023092-1698859,17/07/2006 13:20,24/07/2006 0:30
ELSIE-5/1916,G148445-1720874,8/08/2006 11:00,30/08/2006 10:00
STANISLAWA-3/1918 ,G119981-1720045,8/08/2006 13:01,22/08/2006 12:13
FREDA-11/1925,183772-1998910,27/03/2007 9:53,3/04/2007 11:06
FREDA-11/1925,G147858-2007408,3/04/2007 10:49,26/04/2007 12:39
FREDA-11/1925,183772-2037727,28/04/2007 17:05,9/05/2007 11:41
FREDA-11/1925,G147858-2052082,9/05/2007 12:00,25/05/2007 11:17

If anyone could help it would be much appreciated.

View 6 Replies View Related

T-SQL (SS2K8) :: Find Occurrence And Remove String?

Mar 19, 2014

My sample data is as below.

plan type A change from plan type B from Plan type C

Insurance plan M changed from Insurance plan b From plan d from Plan N

Now from above strings i want to remove all data from second appearance of from. i.e. i want display values as below.

plan type A change from plan type B

Insurance plan M changed from Insurance plan b

View 3 Replies View Related

T-SQL (SS2K8) :: Enhanced Find And Replace Function

Apr 17, 2014

I'm looking for a way to pull off a complex find+replace within some code, as follows:

@step_name = N'SAME - OCF Collins (Tabard)', @command = N'DTSRun /~Z0x5F4F7B0688825E7544AC46CFD664F98AC ', @database_name = N'

We have over 200 variants of the above, but following the same syntax (@step_name, Dbname, @Command etc...


1) Note the unique identifier "~Z0x5F4F7B0688825E7544AC46CFD664F98AC". I would like it replaced for whatever is between "@step_name = N'" , and @command = N'DTSRun ; (this will form a filename).

2) Note the 'DTSRun /' string. I'd like that replaced with Dtexec /F "D:MyFileLocationFolderHere" (this folder remains constant).

View 2 Replies View Related

T-SQL (SS2K8) :: How To Find Saturday Occurrence Between Two Dates

Jun 17, 2014

I'm trying to find if the Saturdays worked in Bi-Weekly period is first or second, based on the Saturday occurrence the CODEID column value changes from 01 to another #, for example if it's first Saturday then CODEID changes from 01 to 02 and it's second Saturday then the CODEID changes from 01 to 03.

Below is my table, current result and desired results.

My Table:

SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014'
SELECT * FROM SampleData

Current Results

12348.0040.0001Friday 05/02/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12347.0040.0001Friday 05/09/2014

Expected Results

12348.0040.0001Friday 05/02/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12347.0040.0001Friday 05/09/2014

View 9 Replies View Related

T-SQL (SS2K8) :: Find Best Index For Specific Query

Dec 28, 2014

This is my table:

use tempdb
if object_id('Data', 'u') is not null drop table Data
with temp as (
select top 10000 row_number() over (order by c1.object_id) Id
from sys.columns c1 cross join sys.columns c2


What index would be best for these three queries? With best I mean the execution time, I don't care about additional space.

This is the index I currently use:
create nonclustered index Ix_Data on Data (StateId, PalletId, BoxId, Id)
The execution plan is SELECT (0%) - Stream Aggregate (10%) - Index Scan (90%).

Can this be optimized (maybe to use Index Seek method)?

View 7 Replies View Related

T-SQL (SS2K8) :: Find Alt Code Characters In Table

Feb 12, 2015

I have a table with code and description as below

create table isin_code
code varchar(5),
code_desc varchar(255)
insert into isin_code values ('aaa','aäsas')
insert into isin_code values ('aaa','as╚as')
insert into isin_code values ('aaa','aâsas')
insert into isin_code values ('aaa','asas')

I want to identify the list of alt codes available in the table.

View 6 Replies View Related

T-SQL (SS2K8) :: How To Find And Add Column Of Percentage In Table

Mar 16, 2015

i made a procedure to the below output now i need to add column percentage depends on these output

block Response Heads Dept Actuals
1Sales Sales01.Sales (net of Sales Tax)1087.5999999999999
1Finance Sales02.LESS:-EXCISE DUTY 22.800000000000001
1Sales Sales03.Net Sales 1064.8
2HR HR 04.Personnel Cost 170.60000000000002
3Materials & productionCOGS 05.Material Cost 376.70000000000005


block Response Heads Dept Actuals percentage
2HR HR 04.Personnel Cost 170.60 170.60*100/1064.8(dept=03.Net
3Materials & productionCOGS 05.Material Cost 376.70 376.70*100/1064.8(dept=03.Net

How to write make a query for that?

View 4 Replies View Related

T-SQL (SS2K8) :: How To Find All Empty Files In A Database

May 4, 2015

I need to find all empty files in the database (SQL Server 2008R2 SP2) The files become empty after the archival of a partitioned tables.

I was trying this:

select f.name, *
from sysfiles f (nolock)
left join sys.filegroups fg (nolock)
on f.name = fg.name
left join sys.indexes i (nolock)
on i.data_space_id = fg.data_space_id
left join sys.all_objects o (nolock)
ON i.[object_id] = o.[object_id]
where i.name is NULL and o.name is NULL

Did not work.

My file names are the same as the filgroup names containing the file, this is why I was joining by name.

View 2 Replies View Related

T-SQL (SS2K8) :: Find Records In A String And Display

Jul 6, 2015

I am having a column which is ntext and contains below type of data.

{ "running":"true", "all":{ }, "GPAs" : [ {"type":"item", "alias":"i_11111"} ,"GPA":"1.75" }, {"element": {"type":"item", "alias":"i_11111"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_33333"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_44444"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_22222"} ,"GPA":"1.42" }, {"element": {"type":"item", "alias":"i_55555"} ,"GPA":"15" }, {"element": {"type":"item", "alias":"i_66666"} ,"GPA":"10" } ]}

above is a single row. There are many such rows are present in the table. Now I want to find all alias and GPA and display as below.



In oracle it can be done by using REGEXP_SUBSTR. But how can we do this in SQL?

View 1 Replies View Related

T-SQL (SS2K8) :: Find Working Time Of Each Employee

Aug 18, 2015

IF EXISTS ( SELECT TOP 1 1 FROM sys.tables WHERE name = '#EmpHours' )


View 7 Replies View Related

T-SQL (SS2K8) :: CTE Query To Find Specific Record?

Sep 18, 2015

I need to build a CTE query to find for the same Cabstamp (document) where i have different Origin.

I know that i can build this with a correlated subquery, but i´am curious about using CTE.

I post sample create Script:

create table #temp (Cabstamp varchar(10), account varchar(10),document varchar(15), origin varchar(2), debit numeric(10,2), credit numeric(10,2), datalc datetime)

insert into #temp (Cabstamp,account,Document, origin, debit, credit, datalc)
select 'ADM12345',111,'CMP-01','FO',1000,0, '20150110'
select 'ADM12345',112,'CMP-01','FO', 500, 0,'20150110'
select 'ADM12345',6811,'CMP-01','DO',0,1500,'20150110'


View 5 Replies View Related

T-SQL (SS2K8) :: Find Previous Date From Table

Nov 4, 2015

I want to find previous date from selected date. below is the sample data.

DECLARE @StartDate SMALLDATETIME = '1/11/2016'

declare @tempdat table(repdate smalldatetime)
insert into @tempdat values ('10/26/2015')
insert into @tempdat values ('10/29/2015')
insert into @tempdat values ('11/1/2015')
insert into @tempdat values ('11/27/2015')
insert into @tempdat values ('11/25/2015')
insert into @tempdat values ('11/20/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/11/2015')
insert into @tempdat values ('11/11/2015')

Now if i pass the date '10/26/2015' then i want select prev date of passed date. in this example no prev date is available, so result set would be nothing.

if i pass the date '11/10/2015' then result should be '11/1/2015' which is prev small date available in table.

View 4 Replies View Related

T-SQL (SS2K8) :: Find Null Values Between 3 Datasets In One Table

Mar 21, 2014

I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.

I have aSQL Fiddle that will allow you to visualize the schema.

The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.

select [system 1], [system 2], [system 3]
SELECT distinct system, item FROM test
where item = 'item 1'
) x


View 2 Replies View Related

T-SQL (SS2K8) :: Find All Rows From All Tables In A DB For Specific Column?

Mar 28, 2014

I have a DB with some tables and, on certain tables, i've a column named "ID_COMPUTER".

I need to find all the rows where id_computer=<specific_value> from all the tables of my database, when column "ID_COMPUTER" exists.

Pseudo-code would be: select * from db.* where id_computer=<specific_value>

how to write this ?

View 2 Replies View Related

T-SQL (SS2K8) :: Find And Replace Text For All Sprocs On A Server

Apr 24, 2014

There are plenty of scripts to do this on a per-DB level, but any that will allow me to generate a script for all DB's at once? Mine are split across dozens and it would be much easier to do a loop (using MS_ForeachDB ? )

View 1 Replies View Related

T-SQL (SS2K8) :: Find Tables That Are Generated From SSIS Package

Jun 26, 2014

I have three databases and 40 tables within each database on my server, for each of the tables I want to know which SSIS package generates that table. Is there a script that can do this?

If the SSIS package does not create or populate a table on the server I then want to check if there is a stored procedure that populates this.

View 6 Replies View Related

T-SQL (SS2K8) :: Find Primary Key Table Name And Field Name Of Foreign Key

Apr 10, 2015

How can i find the primary field name and primary table name of a given foreign key.

For example

Table A:

Table B:
column3 <---- this is foreign key to table A's IDa

What i want to do is i pass input of tableB, column3 and i get name of Primary tableA and field name IDa. How is it possible to do in tsql ?

View 4 Replies View Related

T-SQL (SS2K8) :: Query To Find Older Date That Is Less Than 90 Business Days

Feb 12, 2015

select DateAdd(dd,-90,getdate())

It gives the 90 days older date but i want to exclude the weekdays and holidays (saturday ,sunday,holiday)

As we dont have permission to call a function or Sp in high level environments looking for a simple query .

View 1 Replies View Related

T-SQL (SS2K8) :: Find All Instances Of String That Contain Certain Letters - Escape Double Quote

Sep 14, 2015

I am trying to find all instances of a string that contain the letters FSC. While I am able to find most of them, I am unable to find the ones wrapped in double quotes.

Query example:

Select *
from myTable
Where item like '%FSC%'

This works great with the exception of when FSC is surrounded by double quotes.

ex: MySentencecontains"FSC"

I have tried Replace with no luck.

View 3 Replies View Related

T-SQL (SS2K8) :: Find Matching Phone Of Person Based On Relation Type - Duplicates

Aug 11, 2014

I have a patient record and emergency contact information. I need to find duplicate phone numbers in emergency contact table based on relationship type (RelationType0 between emergency contact and patient. For example, if patient was a child and has mother listed twice with same number, I need to filter these records. The case would be true if there was a father listed, in any cases there should be one father or one mother listed for patient regardless. The link between patient and emergency contact is person_gu. If two siblings linked to same person_gu, there should be still one emergency contact listed.

Below is the schema structure:

Person_Info: PersonID, Person Info contains everyone (patient, vistor, Emergecy contact) First and last names
Patient_Info: PatientID, table contains patient ID and other information
Patient_PersonRelation: Person_ID, patientID, RelationType
Address: Contains address of all person and patient (key PersonID)
Phone: Contains phone # of everyone (key is personID)

The goal to find matching phone for same person based on relationship type (If siblings, then only list one record for parent because the matching phones are not duplicates).

View 9 Replies View Related

T-SQL (SS2K8) :: Find String Before Character When Character Appears Multiple Times

May 17, 2015

I have a table that contains file paths as


and I need a query to return


I tried

SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table

But that removes everything after the first and I need it to return all data before the last

View 5 Replies View Related

Newbie-DELETE A Record In A Table A That Is Related To Table B, And Table B Related To Table A

Mar 20, 2008

Hi thanks for looking at my question

Using sqlServer management studio 2005

My Tables are something like this:

--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
ALTER TABLE [MyCompany].[Employee]
REFERENCES [myCompany].[Branch] ([BranchGID])
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]

-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
ALTER TABLE [MyCompany].[Branch]
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
ALTER TABLE [MyCompany].[Branch]

--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE

DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39

Well this might look like a very basic error:
I get this Error after trying to delete something from Table Employee?

The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.

Yes what Ive been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a Branch? entry, basically each entry in branch? and Employee? is child of each other which makes things more complicated.

My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use ON DELETE CASCADE? or something?


View 8 Replies View Related

Anything That You Find In SQL Object Scripts, You Can Also Find Them In System Tables?

Jul 26, 2005

I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you

View 4 Replies View Related

Problem: Find All Table Names In Db And Then Find

Jun 12, 2008

I have 3 database say

I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)

View 5 Replies View Related

Need Help Related To MDX Please

Nov 23, 2004


Is there a format function in MDX which will help me solve the following

One of my measures is Net Sales and I have tried all different formats
in Analysis Services so that it shows without the decimal places i.e.
instead of showing the Net Sales for any selected combination of
Dimensions as 123,456.04, I want to show it as 123,456

When I browse the cube (in Analysis Services), I can meet my
requirement. However, in my front-end (Microsoft Data Analyzer), I am
still getting it as 123,456.04 (even when the same in Analysis services,
i.e. when I browse the cube, is being shown as 123,456).

I do not have much choice at the moment and am stuck with Microsoft Data
Analyzer and unfortunately have not been able to solve this :(

Can someone think of a solution/workaround/use of MDX which will help me
get the results being displayed without the decimal. Is there a format function that I can use in MDX and how to use it???

Many TIA

View 2 Replies View Related

MS-SQL Related

Aug 5, 2006

Hello,What is uniqueidentifier as a data type?Also what is the data type for setting unique STRINGS ((nchar,nvarchar), for example to be used for emails and user names in a userregistration system).SQL Server does not allow me set primary keys for columns where datatypes are not INT.Thanks in advance.

View 3 Replies View Related

This Seems Messy To Me... [OOP-related]

Nov 20, 2006

I've been working on a performance review web application (i.e., employee's annual reviews done via the web). In the process of creating the application I've been teaching myself .NET - maybe not the best way to do it but I've been learning a lot. However, I still feel like I'm not doing something right.On each Page_Load I'm doing database work with a data reader: Reading the data in, displaying it, letting the user add, edit, or delete it, etc. So every Page_Load code behind looks like this: string sql = "SELECT UserID, Passwd, RecID, Name FROM UserList";

SqlConnection myConn = new SqlConnection("Server=BART; Database=WSSD; User ID=sa; Password=wss1231");
SqlCommand cmd = new SqlCommand(sql, myConn);
SqlDataReader dr;

dr = cmd.ExecuteReader(); And so forth and so on. Now since I re-use this code again and again - I imagine it's a good idea to implement my connection code in a class that I can re-use easily. But I have no idea where to start on something like that. What can I say? I'm a newb. A push in the right direction would be great.

View 8 Replies View Related

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