SQL Server 2012 :: Match Name In Different Tables Where Name Order Is Reversed

Aug 6, 2015

CREATE TABLE #NAME1(FULLNAME VARCHAR (100))
INSERT INTO #NAME1(FULLNAME) VALUES('JOHN X. DOE')
INSERT INTO #NAME1(FULLNAME) VALUES('FITZGERALD F. SCOTT')

CREATE TABLE #NAME2(LASTNAME VARCHAR(25), MI VARCHAR(2), FIRSTNAME VARCHAR(25))
INSERT INTO #NAME2(LASTNAME, MI, FIRSTNAME) VALUES('DOE', 'X', 'JOHN')
INSERT INTO #NAME2(LASTNAME, FIRSTNAME) VALUES('FITZGERALD', 'F SCOTT')

My task is to find matches between these two tables on "name."

View 1 Replies


ADVERTISEMENT

Txt File Col Order Reversed After Using Dts&&>ssis Mig Wiz

Jun 25, 2007

I migrated a simple DTS package and it is completely reversing the column order in the output file.

e.g. col1,col2,col3
becomes
col3,col2,col1

Why has this happened?

How can I change the column order back to what it should be?

Data flow task has
ole db source -> flat file

View 3 Replies View Related

SQL 2012 :: Generate Scripts Result In Order Of Tables And Then Stored Procedures In Server

Sep 10, 2014

I have created one table and one stored procedure for to insert/delete/update the data in that table.

So,I was trying to move the scripts from one database to another by Generating Scripts options in SQL Server.

Generating Scripts:

Object Explorer --> Databases --> Database --> Tasks --> Generate Scripts

The generated script output is in a order of stored procedure first and then table.

REQUIREMENT: My stored procedure is dependent on table. So, I need the table script first and then stored procedure.

Note: I can generate two separate scripts for table and stored procedure, But in a just curiosity to know, Is there any way, can we re order the Generate Scripts output in SQL Server.

View 6 Replies View Related

SQL Server 2012 :: How To Match Values From A PIVOT

Jan 31, 2014

I'd like to get a extract table result, with a reference id primary key, showing the maximum dates for events and who was responsible for them. I can get the max(date) field in columns using PIVOT, but can't see a way to get the 'who' field without lots of LEFT JOINs.

Here's some test data and code which shows the principle:

CREATE TABLE #t
(
ref INT ,
id INT ,
who VARCHAR(10) ,
checkin DATE

[Code] ....

The result set is:

ref 1 who1 2 who2 3 who3 4 who4
123 2014-01-18 carol 2014-01-18 andy 2014-01-16 bill 2014-01-17 carol
456 NULL NULL 2014-01-17 NULL NULL NULL NULL NULL

Is there some way to avoid all the LEFT JOINs, maybe by using another PIVOT, to produce the same result?

View 4 Replies View Related

SQL Server 2012 :: Get OrderIDs That Only Match The Criteria

Aug 8, 2015

I am stuck with a query that I am working on. I have data like below.

DECLARE @Input TABLE
(
OrderID INT,
AccountID INT,
StatusID INT,
Value INT
)

INSERT INTO @Input VALUES (1,1,1,15), (2,1,1,20), (3,2,1,5), (4,2,2,40), (5,3,1,20), (6,1,2,40), (7,1,2,40)

If an Account's value reaches 20 for StatusID = 1 and 40 for StatusID = 2, that is a called "Good". I want to find out which order made the Account become "Good".

By looking at the data, it is understandble that AccountID 1 crossed Status ID 1's limit of 20 with order 2, but the status ID 2's limit was only crossed after the 6th order was placed. So my output should show 6 for AccountID 1.

For AccountID 2, value of statusID 1 was 5 with orderid 3, but it reached the limit for status id 2 of 40 with order 4. But the first condition was not met. so it shouldn't be seen in the output.

Same with AccountID 3 as well, It reached the limit of status id 1 with order 5 but the limit for order 2 wasn't reached so it should be ignored as well.

I wrote the code as below, its working fine but I still know there are better ways to write since I will be working with atleast a million records.

;WITH CTE AS
(
SELECT OrderID,AccountID, StatusID, SUM(Value) OVER(Partition By AccountID, StatusID ORDER BY OrderID) AS RunningTotal
FROM @Input

[Code] ....

View 2 Replies View Related

SQL Server 2012 :: Excluding Records Whose Values From 2 Different Fields Match

Aug 31, 2015

Using MSSQL 2012

I have a simple select query and I need to eliminate records whose values from 2 different fields match. I thought I had this working, but if one of those fields in my data IS NULL it filters out those records. If I comment out my last line then my number record shows, if I include that statement that record drops. The only thing I see in my data is the Name and PName are both NULL in the data for that particular number. Just need to filter out any records where it finds those 3 Names that also have "Default" as the PName, then include everything else even if Name or Pname is NULL.

Below is my where clause.

WHERE [DETERMINATION] <> 'Denied'
AND [Number] ='A150731000039'

---- Removes incorrect records where these names match----
AND ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')

View 4 Replies View Related

SQL Server 2008 :: Finding Only Fields That Don't Match In Two Different Tables

Feb 12, 2015

I have two table People and Employee, both have firstname and lastname as fields

I want to display only the names that don't match on firstname and lastname

View 3 Replies View Related

SQL Server 2012 :: Generate Flag To Check Whether Join Condition Match Or Not

Oct 12, 2015

I want to join 2 tables, table a and table b where b is a lookup table by left outer join. my question is how can i generate a flag that show whether match or not match the join condition ?

**The lookup table b for column id and country are always not null values, and both of them are the keys to join table a. This is because same id and country can have multiples rows in table a due to update date and posting date fields.

example table a
id country area
1 China Asia
2 Thailand Asia
3 Jamaica SouthAmerica
4 Japan Asia

example table b
id country area
1 China Asia
2 Thailand SouthEastAsia
3 Jamaica SouthAmerica
5 USA America

Expected output
id country area Match
1 China Asia Y
2 Thailand SouthEastAsia Y
3 Jamaica SouthAmerica Y
4 Japan Asia N

View 3 Replies View Related

SQL Server 2014 :: Update Commands Being Reversed

Oct 6, 2015

I'm running a update on a table and it appears to work. But when I check the data about 10 mins later it has been reversed back to the original form!

View 9 Replies View Related

SQL Server 2008 :: How To Delete Tables In Database Whose Table Names Match A Criteria

Jul 22, 2015

The database has approx. 2500 temporary tables. The temp tables match a pattern such as APTMP... I tried deleting the tables in SSMS with the statement, Delete from Information_Schema.tables where substring(table_name,1,5) = 'APTMP' This returns the error message"Ad hoc updates to system catalogs are not allowed".

What is the correct way to delete a group of tables whose name match a pattern from within SSMS?

View 9 Replies View Related

SQL Server 2012 :: How To Match Two Different Date Columns In Same Table And Update Third Date Column

May 30, 2015

I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.

I have table containing 5 columns.

caseid
referenceid
startdate
enddate
caseopendate

[code]...

View 4 Replies View Related

SQL Server 2012 :: How To Order By Using Parameter Value

Jul 15, 2014

I have a stored procedure that I would like to order by the order of the parameters it takes in starting for current item number 1, prior item number 1, current item number 2, prior item number 2, and so on.

currently I am ordering it by:

c.CurrentItemNumber
, p.PriorItemNumber

among other fields, but I would like to replace the part above with the parameters 1 through 5 (current and prior).

Is this possible?

This is my stored procedure for reference:

ALTER PROCEDURE [cost].[Ingredient_Cost_Comparison]
(
@CurrentSalesQuoteNumberNVARCHAR(20)
,@PriorSalesQuoteNumberNVARCHAR(20)
,@CurrentItemNumber1NVARCHAR(20)
,@PriorItemNumber1NVARCHAR(20)
,@CurrentItemNumber2NVARCHAR(20)

[Code] ....

View 5 Replies View Related

SQL Server 2012 :: Get Results In A Specific Order?

Sep 11, 2014

I have a simple example of what I am trying to do. Here is some code to make a quick table to demonstrate:

create table #students
(
lastname varchar(50)
,firstname varchar(50)
,address1 varchar(50)

[Code].....

I want to select all the records, and them them be in alphabetical order first by lastname, then by firstname, then by address. HOWEVER, and this is the tricky part, I want to group names together that have the same address. So, in this example, I want the results to be in this order:

HallC6309 N Olive
HallP6309 N Olive <---- grouped with the C record because they have the same address
HallE5488 W Catalina <---- back to alphabetical by first name
HallJ7222 N Cocopas

View 7 Replies View Related

Match In Two Tables

Dec 28, 2007

Hi, hope someone can help.

I have a table of employees and two sites. Most employees only do shifts at the one site but may occasionally work at the other site. What i want is the details of those who have worked at both sites.

for e.g.

Employees
1 Dave
2 Peter
3 John

Site 1
Mon John
Tue Peter
Wed John
Thu John
Fri Peter

Site 2
Mon Dave
Tue Dave
Wed Dave
Thu Dave
Fri Peter

So the answer should be 2 Peter. Hope this explains the problem. Probably very obvious but it has me stumped.

Thanks

View 4 Replies View Related

SQL Server 2012 :: Order Of Execution Of Statement With GO Statements?

Jan 16, 2014

Curious if I have the code below as an example and I execute this code does sql execute from top to bottom? And does the Update run and complete before the delete occurs? Or does SQL execute the update and delete in parallel?

UPDATE tbl1 SET col1 = 1

GO

DELETE FROM tbl1 where col2 = 2

View 2 Replies View Related

SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables

Oct 7, 2015

I am using the following select statement to get the row count from SQL linked server table.

SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')

MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?

View 1 Replies View Related

I Have Two Tables One With Id And One With Storyid Which Match...

Aug 29, 2007

i have two tables one with id and one with storyid which match. i need to check which record is at top of table two. and then pull top four from table one where the top story from table two (storyid) is not one of the top four in table one (id). is this possible? can anyone help?

View 8 Replies View Related

SQL Server 2012 :: How To Modify ORDER BY Clause At Runtime Using Parameter

Jun 4, 2015

the code below works (this is only a quick dumbed down version of the actual code, it might not work 100% for all cases). Is it at all possible to exploit the functions that were added to SSQL since v. 2005 to simplify this code ?

In SSRS, a parameter allows the user to create a list of invoices (from CRM) to be ordered in any of the following ways the user prefers:

'Document Date (most recent date first)'
'Document Number (highest number first)'
'Document Date (most recent first) and Number'
'Document Number (lowest number first)'

The invoices have a (supposedly) sequential identity-generated number. However Accounting may want to set a different date than the creation date on some invoices. So there is no way the invoice numbers will be in the same sequence as the invoice dates.

So I just created the "sorting fields" - they appear as junk in the output dataset (just do not drop them in the SSRS tablix - they have to be part of the SELECT statement to be usable in the ORDER BY clause.

The code is:

DECLARE @ls_OrderBy varchar(80)
--'Document Number (highest number first)'
--'Customer and Document Date (most recent date first)'
--'Customer and Document Number (highest number first)'
--'Document Date (most recent first) and Number'

[code]....

View 9 Replies View Related

Give Results Even If Two Tables Used Don't Match

Aug 12, 2014

I have a table of customer information (ci), but I have to link to another table in order to get the street address (sa).

If I exclude the street address I get 33 results. If I make the link to street address I will only get 30 results.

What I found is that if the ci table field for country is blank and I try linking to sa table then that result will not be shown.

Is there a way to get all 33 results while linking the two tables?

View 4 Replies View Related

Delete Records Are Match On Different Tables

Feb 19, 2007

Hello, Everyone

I had 2 tables contain data:
1) old_data: 601,195 records
firstname
lastname
address
city
state
zip
zip4

2) current_data: 410,185 records
firstname
lastname
address
city
state
zip
zip4
3)Questions: How I write a query make to delete records? From table 'old_data' delete match records reference to table 'current_data' and keep the remain good records are from 'current_data' table.

****Here's below my test query that came up with if there are any error and please provide me any suggestion or new query. Very important task ....Thanks you to all.

/*******
Delete current_data
From old_data Cross Join current_data
Where old_data.FirstName = current_data.FirstName and old_data.LastName = current_data.LastName
and old_data.Address = current_data.Address
********/


RV

View 8 Replies View Related

Transact SQL :: Match Items Between Two Tables

Jul 6, 2015

I have a table variable @tbl with one column (user) with example Laura, Scott, and Kevin.

Then I have a table usergroups with two columns (groupid, user) with example:

1, Laura
1, Scott
2, Laura
2, Scott
2, Kevin
3, Laura
3, Kevin
3, Scott
3, Jim
4, Laura
4, Kevin
4, Scott

I want to find groupid 2 and 4 because they exactly match the content of @tbl.

View 8 Replies View Related

SQL 2012 :: Query To Match Multiple Results And Average

Mar 3, 2014

I work for a school district and new requirement we were just given for scoring some student scores.

Everything will currently be in one table to keep track of students test scores for various things. This table will contain students information and a student will be shown more than once in the table. The Student ID is what we will key off of to find the multiple instances of the student. The table contains the following columns: studentName, StudentId, teacherName, focus1, controllingIdeas1, reading1, development1, organization1, conventions1, and contentUnderstanding1. All of the columns with a 1 at the end will be numeric values with possible decimal values.

What we need to be able to do is some how perform a search for these multiple entries of each student and when found, average the 2 scores for each 7 test categories. The result needs to be a single line for each student that gives the student name, student id, and the 7 test category averages exported to an csv file.

View 9 Replies View Related

Link Two Tables Using Partial Word Match

Jan 18, 2007

Hi All,I need to link two tables using partial word match.How can I write a SQL statement to do so? (I am using MS-Access.)Table One: [Table Name: tblStreet] [Field Name: Street]123 ABC Street124 ABC Street125 ABC Street1 XYZ AVE2 XYZ AVE3 XYZ AVE10 CBS Street11 CBS Street12 CBS Street100 Apple Road101 Apple Road102 Apple RoadTable Two: [Table Name: tblWord] [Field Name: Word]ABCCBSThe output should be:123 ABC Street124 ABC Street125 ABC Street10 CBS Street11 CBS Street12 CBS StreetCould this be done?Thank you in advanced.- Grasshopper -

View 2 Replies View Related

SQL Server Admin 2014 :: SSIS 2012 Environment Variables Are Not On Sort Order

Feb 10, 2014

I have SSIS 2012 Enterprise, using catalog deployment and have more that 50 environment variables for connection to databases across my enterprise.

The problem when i go to configure the packages after deployment and pick the proper env variables, that are not sorted, so i have to browse all entries in order to find the proper entry in environment variables.

View 1 Replies View Related

SQL 2012 :: DQS Term Based Relations Partial Match Not Working

Feb 11, 2015

Why terms are not being corrected on a partial match? I thought this was the point of term based relation rules.

I am testing in an email address (nvarchar) field?

E.g. trying to correct @@ to @ and .con to .com (just to test)

And everything passes.

If I test with an entire value, the field is corrected.

E.g. 605688878@@qq.com corrects to 605688878@qq.com if I enter those exact values as term based relations.

View 0 Replies View Related

Transact SQL :: How To Count Where Two Tables Multiple Columns Match

May 4, 2015

There are two tables

TABLE 1 (NAME - Groupseats)

id session course groupcode sub1 sub2 sub3

1 2015 ba1 137 HL EL Eco
2 2015 ba1 138 EL SL HS
3 2015 ba1 139 SL EL His

From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.

Here is the TABLE 2 Structure and sample data:

table 2 (NAME - tblstudetail)

id studentID session course sub1 sub2 sub3

1 15120001 2015 ba1 EL SL HS
2 15120002 2015 ba1 HL EL Eco
3 15120003 2015 ba1 SL EL His
4 15120004 2015 ba1 HL EL Eco

AND so no..........................

Now i just want to COUNT the Number of Groups Filled in tblStudateil.

View 10 Replies View Related

SQL Server 2012 :: List Of Order Numbers Based On Stock Availability - Filter Results?

Dec 23, 2014

Trying to build a list of order numbers based on stock availability.

The data looks something like this:

OrderNumber Stockcode quantityordered quantityinstock
123 code1 10 5
123 code2 5 10
124 code3 15 20
124 code4 10 10

In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.

View 1 Replies View Related

SQL 2012 :: Number Of Variables Declared In INTO List Must Match That Of Selected Columns

Apr 28, 2015

I am getting error [[Msg 16924, Level 16, State 1, Line 13

Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.]] when i execute below script.

Declare @mSql1 Nvarchar(MAX)
declare @dropuser int
declare @dbname Nvarchar(max)
declare @username Nvarchar(max)

DECLARE Dropuser_Cursor CURSOR FOR

[Code] ....

View 9 Replies View Related

Duplicate Results In 2 Columns But Reversed

Feb 17, 2004

I have to write a query which extracts everyone from a table who has the same surname and forenames as someone else but different id's.

The query should have a surname column, a forenames column, and two id columns (from the person column of the table).

I need to avoid duplicates i.e. the first table id should only be returned in the first id column and not in the second - which is what i am getting at the mo.

This is what i have done

select first.surname, first.forenames, first.person, second.person
from shared.people first, shared.people second
where first.surname= second.surname
and first.forenames = second.forenames
and not first.person = second.person
order by first.surname, first.forenames

and i get results like this

Porter Sarah Victoria 9518823 9869770
Porter Sarah Victoria 9869770 9518823 - i.e. duplicates

cheers

View 5 Replies View Related

Parent-Child Hierarchy But Reversed(!)

Feb 21, 2007

Hi!

I have a report that uses a parent-child hierarchy in a table. It is hidden with the toggle set to itself, and that works perfectly.

However, instead of having it look like this(simple example):

+ Profit

After the + has been clicked:

-Profit
+Sales
+Cost

I would like it to look like this:

+Sales
+Cost
-Profit

Is this possible with when using a parent-child hierarchy?



http://ssasfreak.spaces.live.com/

View 2 Replies View Related

SQL Server 2012 :: Two Tables - Join One To Many

Oct 11, 2015

I have two tables, INCIDENT and PEOPLE

INCIDENT has two fields, ID and Date
PEOPLE has three fields, INC_ID [foreign key to INCIDENT.ID], PersonIteration, Complaint

There is always only one incident, but there can be one or more people involved in each incident.

I'd like to retrieve incidents, but have it include information from all involved person on one line.

No matter how I have tried to join these tables, I am ending up with one record each for each person involved in an incident, like so

ID PersonIteration Complaint
1 1 Head
1 2 Neck
1 3 Shoulder

I would like to join in this fashion:

ID PersonIteration1 Complaint1 PersonIteration2 Complaint2
1 1 Head 2 Neck

Is there any way to do this?

View 5 Replies View Related

SQL Server 2012 :: Query To Return Top 10 Tables On Each Database On Server?

Nov 21, 2013

i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.

View 3 Replies View Related

SQL Server 2012 :: Merge Data From Two Tables

Feb 3, 2014

Merging table :

--------Dummy TABLE

create table #Tbl1 (date1 date,WSH varchar(10),ITN int,Executions int)
insert into #Tbl1 (date1 , WSH , ITN , Executions)
select '20130202' ,'ABC', 1 , 100
union all
select '20130203' ,'DEF', 1 , 200

[Code] .....

I want Result like this :

date1 WSH ITNExecutionsMCGPositions
2013-02-02 ABC 1 100 2 500
2013-02-03 DEF1 200 NULL NULL
2013-02-05 NULLNULL NULL 2 600

View 2 Replies View Related







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