SQL Server 2014 :: Exclude Duplicate And Fetch Max Of X Column

Sep 11, 2014

I want to fetch max of Field2 if duplicate records in Field1 and rest of the values of field1 , below is the sample format.

Field1 Field2 Field3 Field4
32 375 abc-xyz A
32 379 xyz-efg A
55 405 abc-xyz B
55 407 xyz-efg B
132 908 abc-xyz C
132 999 xyz-efg C
152 800 abc-xyz D
152 850 xyz-efg D
155 900 abc-xyz E
156 925 abc-xyz F
157 935 abc-xyz G

View 2 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: How To Fetch Data From Oracle Database

Oct 14, 2015

how to fetch data from oracle database in sql server 2014

example:

oracle schema :t1
sql server :t2

now am in t2 sql server database

now am executing below query

select * from t1.tablename ;

View 1 Replies View Related

SQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table

May 26, 2014

I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.

View 1 Replies View Related

Exclude Duplicate Records

Jan 10, 2007

Hello

I'm developing my fist Integration Service and I have this operations:

Reading from a XML
Check for duplicate records and discard them
Insert the result into the database

The XML I don't control and could came with duplicate records that I have to discard. How can I find them?

I want to find the duplicates in the XML and not in database.



tkx for the help
Paulo Aboim Pinto
Odivelas - Portugal

View 14 Replies View Related

Exclude Column For Replication In SQL Server 2000

Mar 3, 2008

Hi. I'm creating a new publication for replication. There are a few columns on our main database I do not want published as they contain some confidential information. When I go to create a new publication, I go through the wizard and I select the option to filter rows vertically. I uncheck the columns I don't want published and the wizard completes without any errors.

When I go to start the Snapshot agent to create the database on new server, it gets error saying it cannot find certain columns--the columns I do not want published to begin with.

Am i doing something wrong? I appreciate any help you may provide!

View 7 Replies View Related

SQL Server 2012 :: Exclude Rows Where Value In Column Not Found In Another Row

Jul 16, 2014

This is a followup to a previous question to a previous but in reverse of Find rows where value in column not found in another row

Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...

I would like to exclude any two rows where Type is equal to 'TypeA' and Key2 is Null that have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row.

So, given the data

**KEY1** **Key2** **Type**
1 NULL TypeA
2 5 TypeA
3 1 TypeB
4 NULL TypeA
5 NULL TypeB
6 26 TypeC
7 NULL TypeD
8 NULL TypeD

I would like to return all the rows except where Key=1 and Key=3 because those rows together meet the criteria of Type='TypeA'/Key2=NULL and does have a corresponding row with Type='TypeB'/Key1=Key2.

View 2 Replies View Related

Fetch Returning Duplicate Last Record

Sep 13, 2006

Ok, this thing is returning the last record twice. If I have only one record it returns it twice, multiple records gives me the last one twice. I am sure some dumb pilot error is involved, HELP!

Thanks in advance, Larry

ALTER FUNCTION dbo.TestFoodDisLikes

(

@ResidentID int

)

RETURNS varchar(250)

AS

BEGIN

DECLARE @RDLike varchar(50)

DECLARE @RDLikeList varchar(250)

BEGIN

SELECT @RDLikeList = ''

DECLARE RDLike_cursor CURSOR

LOCAL SCROLL STATIC

FOR

SELECT FoodItem

FROM tblFoodDislikes

WHERE (ResidentID = @ResidentID) AND (Breakfast = 'True')

OPEN RDLike_cursor

FETCH NEXT FROM RDLike_cursor

INTO @RDLike

SELECT @RDLikeList = @RDLike

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM RDLike_cursor

INTO @RDLike

SELECT @RDLikeList = @RDLikeList + ', ' + @RDLike

END

CLOSE RDLike_cursor

DEALLOCATE RDLike_cursor

END

RETURN @RDLikeList

END

View 5 Replies View Related

SQL Server 2014 :: Getting Error When Trying To Duplicate Some Code Off Of A Website?

Jan 19, 2015

I found an article on the CodeProject website named T-SQL MapReduce. I'm trying to duplicate the SQL code there into a SQL Server 2914 Developer Edition database I've got, and also the C# code using Visual Studio. I've defined the user data types fine (e.g.: word_t, words_t and so on), but I got an error when I tried to create the UDF dbo.mapper from the page. The error I got was:

Msg 102, Level 15, State 1, Procedure mapper, Line 5

Incorrect syntax near 'CALLER'.

The SQL code I used is straight off of the page; it looks like this:

[code=sql]
create function dbo.mapper(@documents DocumentsWithAutoIncrementIndexTable READONLY, @sep nvarchar)
returns @t TABLE ( wordindex int, word word_t )
WITH EXECUTE AS CALLER
[/code]

What have I done wrong? I'm guessing that the author has done something like left off a variable declaration or something like that. what am I missing?

View 6 Replies View Related

SQL Server 2014 :: Duplicate Record Results On 2 One To Many Tables?

Feb 1, 2015

I have 3 Tables

TableA - TAID, Name, LastName
TableB - MaleFriendsName, MaleFriendsLastName [One to many]
TableC - FemaleFriendsName, FemaleFriendsLastName [one to many]

A query returns duplicate results from TableB as well as TableC

TableB and TableC have nothing in common and should not interfere with each other.

with TwoTables as (
select
a.QuickSpec as QuickSpecId,

[Code].....
Resultset returns duplicate values on TableB AND only for 1 record in the results [As per Lynn examples]

View 1 Replies View Related

SQL Server 2014 :: Find Duplicate Rows Like Same Entries More Than One Time?

Sep 11, 2014

i have a table like below

create table staff_attendance
(
attendance_id int,
attendace_date datetime,
staff_id int,
working_year int,
hours int
)

values like

1 2014-06-30 00:00:00.0ST10121
2 2014-06-30 00:00:00.0ST10122
3 2014-06-30 00:00:00.0ST10122 ----same entry like previous one
4 2014-07-01 00:00:00.0ST10121
5 2014-07-01 00:00:00.0ST10122
6 2014-07-02 00:00:00.0ST10121
7 2014-07-02 00:00:00.0ST10122
8 2014-06-30 00:00:00.0ST10221
9 2014-06-30 00:00:00.0ST10222
10 2014-07-01 00:00:00.0ST1022 1
11 2014-07-01 00:00:00.0ST102 22
12 2014-07-02 00:00:00.0ST102 21
13 2014-07-02 00:00:00.0ST102 22

I Need to find the duplicate rows like same entries which is having more than 1 rows.... how do i find?

View 3 Replies View Related

SQL Server 2008 :: How To Fetch Drop Down Values Stored In XML Column

Aug 13, 2015

Below is a XML column data. How to get the Id and respective Names for "Case Manager" Dropdown ONLY in SQL server 2008. I don't want to get anything related to "Intake Staff" drop down.

<DropDown Prompt="Case Manager" Column="case_manager" AddOnly="false" ReadOnly="false" Required="false" CanHaveNotes="no" LabelCssClass="" IndentLevel="1" FirstEntryBlank="false" CssClass="" DefaultValue="" ChoiceType="1">
<Items>
<Item Id="1">ABC</Item>
<Item Id="2">DEF</Item>
<Item Id="3">GHI</Item>
<Item Id="4">JKL</Item>
<Item Id="5">MNO</Item>

[code]....

View 1 Replies View Related

Exclude Records From A Table Where ID Column Is Same But Mail Code Column Is Multi-valued

Nov 12, 2012

I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)

ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC

So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.

View 3 Replies View Related

SQL Server 2014 :: Count Duplicate Records Based Upon Account Number?

Jul 16, 2014

I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.

SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END

View 9 Replies View Related

SQL Server 2014 :: Error On Unique Index - Duplicate Key When Insert / Select

Aug 4, 2014

I have an issue where I am getting an error on an unique index.

I know why I am getting the error but not sure how to get around it.

The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.

But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.

It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.

How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.

CREATE TABLE #TestTable
(
Name varchar(50),
Email varchar (40)
)
Insert #TestTable (Name,Email) Values('Tom', 'tom@aol.com')

[Code] .....

View 1 Replies View Related

SQL Server 2014 :: Eliminate Duplicate Rows When Joining Multiple Tables

Jun 8, 2015

We have the below query which is pulling in Sales and Revenue information. Since the sale is recorded in just one month and the revenue is recorded each month, we need to have the results of this query to only list the Sales amount once, but still have all the other revenue amounts listed for each month. In this example, the sale is record in year 2014 and month 10, but there are revenues in every month as well for the rest of 2014 and the start of 2015 but we only want to the sales amount to appear once on this results set.

SELECT
project.project_number,
project.country_code,
project.project_desc,
gsl.global_service_line_desc,
buy.buyer_desc,

[Code] ....

View 9 Replies View Related

SQL Server 2012 :: Delete / Recreate Identity Column / Fetch Newly Created Values In Update Statement?

Jul 25, 2015

I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.

The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.

The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.

Below is the schema of the three tables

I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.

Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.

This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.

Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.

I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.

Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows

After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.

I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.

DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'

[code].....

View 2 Replies View Related

Exclude Row If Calculated Column Is Zero?

Oct 3, 2013

I am attempting to create an SQL statement that will query a file and give me amount totals by company number/customer number. The totals have to be combined into 4 groups (1/2/3/4) for each amount total in company number/customer number combination. In effect it will look something like this:

COMPANY | CUSTOMER | SORT | AMOUNT
==================================
00001 | 11111 | 1 | $55
00001 | 11111 | 2 | $12
00001 | 11111 | 3 | $19
00001 | 11111 | 4 | $ 0
00001 | 22222 | 1 | $99
00001 | 22222 | 2 | $53
...and so on.

I HAVE THIS PART WORKING ALREADY. The problem is that I am trying to exclude the rows that have 0 (zero) in the amount column from showing up in the output. The amount is a calculated field of all the invoice for that company number/customer number combination for that sort (eg: Company 00001/Customer 11111/Sort 1 has $55 associated to it). I cannot use the calculated field in my where clause.

I will include a simplified version of my select statement so you can see how I got as far as I have and where to go so I pretty much say "WHERE SUM(SubTBL.Amount) <> 0".

----SELECT STATEMENT-----
SELECT
MainTBL.Cust#,
SUM(SubTBL.Amount) As TotAmt,
CASE
WHEN (days (currdate) - days (MainTBL.DateFLD)) <= 30 THEN '1'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 31 AND 60 THEN '2'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 61 AND 90 THEN '3'
WHEN (days (currdate) - days (MainTBL.DateFLD))> 90 THEN '4'

[code]....

View 1 Replies View Related

How To Exclude Records That Have A Common Column

Dec 26, 2007

I am creating a query which will show patients that are enrolled in more than one program, but I need to exclude those patients that enrolled more than once in the same program. Here's part of the code:

SELECT member_id, service_id
FROM pat_prg_info ppi

This query produces results like the following:

member_id service_id
1001 1
1001 2
1003 9
1003 9
1004 2
1004 9

I would like to exclude 1003, since this member_id is enrolled twice in service_id 9. How can I accomplish this?

View 1 Replies View Related

Transact SQL :: Need To Exclude Last Column Comma Using Script

Jun 4, 2015

I want to populate data from Production to UAT(except Identity column).For that I created query,its generating script what i have required.But in last column getting with comma.I should eliminate the last comma.

script to my requirement:

declare @TABLE_SCHEMA varchar(10)
set @TABLE_SCHEMA='dbo'
declare @TABLE_NAME varchar(100)
set @TABLE_NAME='Demo_Table'
SELECT ORDINAL_POSITION AS COLUMN_POSTION ,TABLE_SCHEMA,TABLE_NAME

[Code] ....

View 12 Replies View Related

Change Duplicate Data In Server Column

Oct 17, 2014

I want to change duplicate data in my sql server column.

the data= 'Barack Obama' , what I want
'Barack Obama 23453'

I have the following query:

UPDATE klant SET naamvoornaam=naamvoornaam + CAST(klantnummer AS VARCHAR)
WHERE naamvoornaam NOT IN (
SELECT(naamvoornaam)
FROM klant
GROUP BY naamvoornaam
HAVING ( COUNT(naamvoornaam) = 1 ))

Message from messages:
String or binary data would be truncated.

I think the max length exceeds the length of the destination column. I've tried:

Max(Len(naamvoornaam + CAST(klantnummer AS VARCHAR)))

message from messages:
An aggregate may not appear in the set list of an UPDATE statement.

View 7 Replies View Related

Fetch Data From XML Column To Table

Aug 29, 2007

I do an insert of the xml into the table and that works fine, but how do I split the tags to different tables. I have tried SSIS and a XML Source to an OLEDB Source, but since the xml file contains different groups that do not work.

The xml is created by Infopath and it seems like the groups are created if the components belongs to different sections.

Table1
id int,
xmltag xml

I am starting to be desperate, I really need some help solving this one way or another.

View 2 Replies View Related

How To Fetch Latest Column Values

Feb 12, 2008

Hi All,

I have two tables.
Table A contains a unique column of some Keys.
Table B contains Key column,Value column and Entry_Time column.
Now I need the most recently entered value for each of the Keys in table A using table B.
I dont need any repeatation of Keys in my result.

Please help me out.I am using DB2.

View 1 Replies View Related

SQL Server 2012 :: Dropping Rows With Duplicate Column Name

Feb 13, 2014

I have a query that produces unique rows. However, some of the unique rows have the column called testname that has the same test listed more than once. All I want to do is drop the older testname and keep the testdate column.

Select
Distinct
TestID,
TestDate,
TestName

From third.test

I want to keep testdate in the query....guessing I need to put in to a temp table then drop the oldest one somehow by doing a subquery using Select Max....

View 1 Replies View Related

SQL Server 2012 :: Reducing Duplicate Row Because Of Different Column Values?

Oct 14, 2014

With the data example below I am trying to consolidate the duplicate rows by flattening the dealer and billcode, or putting those values in each of the columns instead of creating separate rows...

74 MARTHA PATNE RIPLEY 1 23,327,76 ROTTINGDAM AAC SPRINGFIELD 3052 USA MPATRIP@AMERICANALARM.COM,MPATRIP@COMCAST.NET,PRIPLEY@ONECOMMUNICATIONS.COM

[table]
arnumbercustomernamedealerbillcodeaddl_addraddr1addr2branchcityzipcodecountryemaildefaultEmail
39SUSAN THALKER 2271BOTTOMWOOD RDAACHAMMOND02180-2703USASUWS3@COMCAST.NET1
56ANN REBELLO 123SHERIDAN AVEAACMEDFORD2155USANULLNULL
58DARRELL/PATTI SANDERS 6020DOTY AVENUEAACDANVERS1923USANULLNULL
74MARTHA PATNE RIPLEY 123ROTTINGDAM DRAACSPRINGFIELD3052USAMPATRIP@AMERICANALARM.COM1
74MARTHA PATNE RIPLEY 1327ROTTINGDAM DRAACSPRINGFIELD3052USAMPATRIP@COMCAST.NET1
74MARTHA PATNE RIPLEY 176ROTTINGDAM DRAACSPRINGFIELD3052USAPRIPLEY@ONECOMMUNICATIONS.COM1
[/table]

View 1 Replies View Related

How To Fetch Data Before Inserting A New ROW If We Are Using Identity Column??????

Apr 21, 2008

Hi,

I am using SQL Server 2005 Mobile Edition & Merge Replication

in this I want to insert a record into table,
in that table I have taken UserID coloumn as auto incrementing Identity type.

In a book I read that,
If you are using an Identity column, you must find the next available number and reseed before an insert can be successful. You will also have to set up ranged identity columns on the published database to prevent errors when the new data is merged.

Now I want to ask here that, how should I ressed that value before inserting?????

any help in a form of CODe will be appriciated.....

thanks in advance...

View 8 Replies View Related

Fetch Metadata From A Column With An Alias In A View

Aug 2, 2007

Hi!

I have created a view and one of the columns in the view has an alias assigned to it.

I'm able to read the metadata from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE and also lookup
from which table each column in the view orginated from except for the column that has an alias assigned to it.

Is there any other way to lookup a column that has an alias assigned to it?


Thanks alot!

Adam

View 2 Replies View Related

SQL Server 2014 :: Get Latest Per One Column

Oct 9, 2014

I have the following table

DECLARE @TABLE1 TABLE (D_ID int, C_ID int, C_HIST_ID int)
INSERT INTO @TABLE1
VALUES(1000016,71,77),
(1000017,71,547),
(1000017,71,553),
(1000017,71,564),
(1000013,71,565),
(1000017,71,3329),
(1000017,71,3330)

SELECT * FROM @TABLE1 ORDER BY C_HIST_ID

D_ID C_IDC_HIST_ID
10000167177
100001771547
100001771553
100001771564
100001371565
1000017713329
1000017713330

I was able to get the following result

SELECT D_ID,C_ID,C_HIST_ID
FROM @TABLE1 ACH
WHERE ACH.C_ID= 71
AND ACH.C_HIST_ID IN (SELECT MAX(ACH1.C_HIST_ID) FROM @TABLE1 ACH1
WHERE ACH1.C_ID = ACH.C_ID
AND ACH.D_ID = ACH1.D_ID )
ORDER BY C_HIST_ID

D_ID C_IDC_HIST_ID
10000167177
100001371565
1000017713330

But the result I need is

D_ID C_IDC_HIST_ID
10000167177
100001771564
100001371565
1000017713330

C_HIST_ID = 564, which I'm unable to get it through my query...

View 4 Replies View Related

SQL Server 2014 :: Add New Column With Default Value

Apr 20, 2015

I want to set a default value to a column while adding it.

Create table test(id int )

insert into Test values(123)

alter table test add column2 int default(0)

In the above query I have set the default value, but it will not set the default value to the already inserted columns.

So how to set a default value for the already inserted rows, while adding a new column to it ?

View 6 Replies View Related

Analysis :: MDX To Fetch Measure Source Table And Column ID

Jun 12, 2015

MDX query to get the source table and column for a measure from ssas database. i want below highlighted using mdx.

View 4 Replies View Related

SQL Server 2014 :: Transform Data Into Column

Jan 8, 2014

I have data like this in the table :

IntRecpieID strName intMealtypeID Total
100 ‘A’ 1 20
101 'B' 2 30
100 'A' 3 40

Desired Output required:

IntRecpieID StrName 1 2 3

100 'A' 20 Null 40

101 'B' Null 30 Null

View 5 Replies View Related

SQL Server 2014 :: Update Duplicates In A Column

Aug 13, 2015

How do I append an accumulating number to duplicates in a column? I would prefer to update the table

for example

Name
John
John
Tom
Bob
Bob
Bob
Mike

to look like

Name
John
John1
Tom
Bob
Bob1
Bob2
Mike

View 1 Replies View Related

SQL Server Admin 2014 :: Add New Column To A Table

Aug 24, 2015

I am new in SQL Server, What to check ,what action do i need to take while adding a new column to a table.

View 5 Replies View Related

SQL Server 2014 :: Show Sum Of Particular Column In Last Row Of Table

Sep 18, 2015

I have a table with some rows and columns what i want is i want to Show sum of particular column in the last row. This is my code.

SELECT DISTINCT Cluster.ClusterName, Gruppe.GruppeName, Arbeitspaket.ArbeitspaketName, BMWProjekt, AnzahlAP, Abgerechnet, InBearbeitung, Billanz FROM Bestellung
INNER JOIN Cluster ON Bestellung.Cluster = Cluster.rowid
INNER JOIN Arbeitspaket ON Bestellung.Arbeitspaket = Arbeitspaket.rowid
INNER JOIN Gruppe ON Bestellung.Gruppe = Gruppe.rowid
WHERE Projekt ="EA-284-Nxx" AND AnzahlAP <> 0 AND Abgerechnet is 1 AND InBearbeitung is NULL AND Billanz is NULL;

View 4 Replies View Related







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