SQL Server 2008 :: Run Query To Retrieve 650 Unique Records

May 14, 2015

I've a excel spreadsheet with 650 records with unique PONumbers. I need to pull data from SQL server based on the PONumbers. I don't want to run select statement 650 times. How do I retrieve the records in efficient way?

View 9 Replies


ADVERTISEMENT

Query To Retrieve Unique Records

Jun 7, 2008

I am new to SQL, still learning.

If I want to retreive unique records for e.g. VendorName,City and State however no 2 vendors must be of the same city or state.

How can I do this?

I have used Select and distinct but that still brings unique vendornames in the same state and city. I only need one vendor per city and State.

Help!

View 4 Replies View Related

SQL Server 2008 :: Query Pulling Too Many Records

Feb 11, 2015

My query is pulling to many records becuase of the last join. This table can have multiple registration files for a computer. I just want the latest one or last one insert which is based on dttRegistration. I thought a top 1 select and order by would do it, but now returns no computer names.

Selectdr.intRecId,
c.strCategory,
mm.strManufacturer,
dm.strMake, m.strModel,

[Code] .....

View 9 Replies View Related

How To Retrieve Mismatched Records With MS Access Query

Jun 24, 2004

Hi, does anyone know how to retrieve mistmatched records across 2 tables.
To clarify, I have table A with 1175 records and Table B with 894 records.
The records from table A match exactly some the records in table B.

I want to create another table with the extra 281 records from Table A which does not match that of Table B.

I have tried the query with Select where fields_1.a<>fields_2.b AND fields_2.a<>fields_2.b etc
but that doesn't seem to work.

*desparate*

View 2 Replies View Related

Query To Retrieve Records That Sum To A Field In Another Table

May 18, 2012

I really need creating a query that will retreive all records from a table where the dbo.CorpAdv.AcctNum field equals a specific value (for this example "0023"), the TranCode = "R" and the sum of the records, starting with the latest, equals the value of a field in another table (dbo.Master.TotalAdv)

dbo.Master.TotalAdv is numeric (dollar amount) and in this example the value is $1,850.00

dbo.CorpAdv.pID is an integer and unique ID for each record, later records have higher numbers
dbo.CorpAdv.AcctNum is text field
dbo.CorpAdv.AdvAmt is numeric (dollar amounts)

[code]....

View 3 Replies View Related

MSSQL Query To List Out Unique Records

May 22, 2008

 hilet me explain my need..following are the tables im using..tbl_company (company table - parent)    id    company    1    test    2    test123    tbl_dept (department table - master)    id    dept    1    dept1    2    dept2tbl_compdept (company departments table - child)    cmpid    deptid    1            1    2            1    2            2    wats my need is.. while the company is listing..by query using joins, result was like this..company        depttest                dept1test123           dept1test123           dept2i need company test123 should be listd only once..when i use group by or distinct means, all r listed..is there any way to filter out therepeating company list by just listing the company list only once..

View 5 Replies View Related

Query On Unique Records With Multiple Criteria

Oct 19, 2013

I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required.

SCENARIO :

I am required to query from 2 tables for those unique record that meets both conditions below:-
1. Status is 1 @ max (trans_id), paychnl = CC
2. Status is 2 @ max (trans_id), paychnl = A or B

FYR, 2 tables and respective columns to query are as below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE
table CHFF --> col STATUS,PAYCHNL

FYI, status refers to the paychnl method status:-
==> 1 means the current paychnl method
==> 2 means the previous paychnl method

paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE.

Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID.

Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-

table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE
table CHFF --> col STATUS,PAYCHNL

SELECT DISTINCT PTFF.TRANSCODE,PTFF.ID,PTFF.TRANS_ID,PTFF.TRANSDATE,PTFF.EFFDATE, CHFF.STATUS,CHFF.PAYCHNL

FROM DBO.PTFF PTFF
JOIN DBO.CHFF CHFF
ON CHFF.ID = PTFF.ID
WHERE
PTFF.TRANSDATE BETWEEN 130501 AND 130831
AND PTFF.TRANSCODE='T522'
AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B')))

However, the script above returns :-

1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2,
2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B
3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID

Samples of the result:-

IDSTATUSTRANS_IDPAYCHNLTRANSDATEEFFDATETRANSCODE
51881712CC13082920130920T522
9361164CC13081620140813T522
78531153CC13082020130814T522
8949151CC13081220130801T522
8949251B13081220130801T522
19081455CC13051620131129T522
19082455A13051620131129T522
19081409CC11101920111129T522
19082409A11101920111129T522
19081404CC11092920111129T522
19082404B11092920111129T522

View 7 Replies View Related

Can I Get Unique Records Using Squel Query In MS-Access?

Jan 20, 2008

Hi Respected,
I am working in .net, and according my client he want to see all record from table uniquely, and does not want to give order also.
for doing this i did use DISTINCT, but my problem is when i used DISTINCT the query return sorted records that is why, i need guide which will help to make DISTINCT and physical stored record from the table.
thanks.
bay
regards Rajat.

View 1 Replies View Related

SQL Select Query Need For Following Criteria. Please Help, Retrieve Records With Independent Price And Its Total Volume Per Min

Jul 17, 2006

Time       Price   Volume
090048       510      6749
090122       510      2101
090135       510      1000
090204       505      2840
090213       505      220
090222       505      1260
090232       505      850
090242       505      200
090253       510      1200
090313       510      570
090343       510      250
090353       510      160
 
Criteria
Retrieve records with independent price and its total volume per minute
 
SELECT SUBSTRING(st,1,4) AS Ttime,d_price AS Price,SUM(l_cum) AS Volume FROM cmd4
WHERE sd='20060717' AND serial='0455'
GROUP BY SUBSTRING(st,1,4),d_price,l_cum
 
Result of the above query: -
Time  Price Volume                 
0900    510     6749
0901    510     1000
0901    510     2101
0902    505     200
0902    505     220
0902    505     850
0902    505    1260
0902    505    2840
0902    510    1200
0903    510    160
0903    510    250
0903    510    570
 
 
THE FOLLOWING RESULT STILL NOT A TOTAL FOR A MINUTE]
E.G
 
0901    510            1000
             +
0901    510            2101
            =         
0901    510            3101 <- I NEED THIS
 
Can any one advice or give me tips over this. Please
 

View 3 Replies View Related

SQL Server 2008 :: Parent Records Ordering And Display Child Records Next To It?

Sep 7, 2015

declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2

[code]....

This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....

Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending

--Final output should be

PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4

View 2 Replies View Related

SQL Server 2008 :: How To Retrieve Only Monday Dates From Each Month

Jan 23, 2015

How to retrieve only Monday dates from each month in sql server.

If i pass any value (Let say GETDATE()) then i should get all monday values from the current month.

I want to calculate bi weekly range data in the sql.

View 9 Replies View Related

SQL Server 2008 :: XML Nodes To Retrieve Child And Its List Of Values

Apr 8, 2015

i was trying to use the XML read functionality using t-SQL for XML attached.The column is coming with the token names and token-values in XML format and we are using the XML nodes() functionality to read the token names and token value.I am able to read only the parent token names and its values(using the sql attached) and could not be able to get the child token names and its values.how can i acheive the tokenNames with its values with the SQL query.i am attaching both SQL script which i am using and the XML entity.

View 9 Replies View Related

How To Retrieve 100,000 Records From Active Directory Using SQL Server

Nov 24, 2004

Hi,

I am new to Microsoft Technologies, can any one say how to connect/ retrieve data's from Active Directory through SQL SERVER from scratch. I need with some sample codes.....

Thanks in advance

raj d.s

View 1 Replies View Related

SQL Server 2008 :: How To Know If A Table Has Non-unique Clustered Index

Oct 30, 2015

Give a user table ‘MyTable’. How to know whether the table contains a non-unique clustered index by using SQL query?

View 2 Replies View Related

SQL Server 2008 :: Return Unique-identifier Of Newly Inserted Row?

Apr 3, 2015

I have the following insert statement:

INSERT INTO [User].User_Profile
(UniqueId, Username, EmailAddress,
Password, BirthDay, BirthMonth,
BirthYear, Age, AccountType, DateCreated,
DeletedDate, DeletedReason, ProfileStatus)
VALUES
(NEWID(), @Username, @EmailAddress,
@Password, @BirthDay, @BirthMonth,
@BirthYeat, @Age, 1, SYSDATETIME(),
null, null, 2)
SELECT @@IDENTITY

As you can I have a uniqueidentifier (UniqueId) column which I populate with NewID() I'm trying to return this as I need it for other functionality of the website but I can't figure out how I can get it after the insert has completed?

View 3 Replies View Related

SQL Server 2008 :: Generate 8 Char Alphanumeric Unique Sequence

Apr 20, 2015

GENERATE 8 CHARACTER ALPHANUMERIC SEQUENCES

Requirements
• ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99
Last 8 bytes will alternate between 2 byte alpha/2 byte numeric
• Generate from Alphabets – A through Z Numbers -0 to 9
• Generate Unique Sequence (No Duplicates).
• Must Eliminate letters I and O

Output Expected
• AA00AA00………..ZZ99ZZ99
• Using 24 alphabets & 10 digits ,
24*24*10*10*24*24 = 3 317 760 000 records

Below is my Sql Function -

CREATE function [dbo].[SequenceComplexNEW]
(
@Id BIGINT
)
Returns char(8)

[Code] .....

View 9 Replies View Related

SQL Server 2008 :: Creating Index - Add Unique Key That Will Cover 3 Fields

Sep 30, 2015

Table Name: Denominator
Already has the following constraint:
PK_Denominatorclustered, unique, primary key located on PRIMARY DenominatorID

How can I add a unique key that will cover the 3 fields --> MemberID,MeasureID,TimePeriodID

I also want to know whether we can include the " WITH ( IGNORE_DUP_KEY=ON ) "

View 3 Replies View Related

SQL Server 2008 :: Export Information With Unique Numerator Per Line

Oct 27, 2015

I have 2 tables:

1 is with only one field: numerator
2ed is (for example) sales information.

I need to export (using SP) the sales information with unique numerator per line. So,
I need to get the value from table 1 (numerator),
I need to promote the numerator by 1 for every line, and
I need to update the first table (numerator) (as i need to keep it updated for the next run).

At the next run I need to get again the updated numerator from the first table, and so on...

View 3 Replies View Related

HOW To Select A Matrix (cross Join) With Empty Records To Retrieve The Same Amount Of Records For Each Cell

Nov 2, 2006

Hello

Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:

This is a simple matrix


test a

text b








text c








text d

text e

text f








text g










This is a matrix with all the same row-height.



test a

text b

.








text c

.
.









text d

text e

text f








text g

.

.









Thx you a lot

View 3 Replies View Related

SQL Server 2008 :: Returning Unique Rows - Read Date Field?

Jul 26, 2015

I have the following query:

Select p.Id [SenderId], p.Username, up.PhotoId,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END AS NewMessage,
p.LastLoggedIn, p.LoggedIn

[Code] ....

The above query returns me all messages (inbox functionality) that have been sent to mr.ReceipientId, the issue I have is when I send another email to the recipient the readdate field will be null, and the other emails linked to the recipient which have also been sent via me will have a readdate date. This causes duplicate rows to appear due to the case statement, I'm trying to figure out if / how it is possible to only display the one row per conversation and set newmessage to 1 if there is an un-read message otherwise show 0 ?

So instead of showing this:

2Person102015-07-26 17:07:24.9370
2Person112015-07-26 17:07:24.9370

I'm trying to get it to look like this:

2Person112015-07-26 17:07:24.9370

Or if we have no new mail (read date is not null) then it will appear like this, and I can confirm when there is no new mail it works as expected.

2Person102015-07-26 17:07:24.9370

View 1 Replies View Related

SQL Server 2008 :: Statement Error Out Due To Source Field Being Unique Identifier

Sep 22, 2015

I have a field I am trying to bring into a SQL statement

,ISNULL(Convert(nvarchar(50),OPP1.OriginatingLeadId),'') AS 'OriginatingLeadId'

I get this error message

Conversion failed when converting from a character string to uniqueidentifier.

the field specs' originatingleadid is attached ....

View 9 Replies View Related

SQL Server 2014 :: Getting Unique Number For Groups Of Records?

Aug 6, 2014

I'm trying to do the following and haven't been able to figure it out.

Say there's a table with these records:

Col1 Col2 Col3
a b c
a b c
a b d
e f g
e f g

I want to generate a number that represents the groups of columns like this:

Col1 Col2 Col3 MyNumber
a b c 1
a b c 1
a b d 2
e f g 3
e f g 3

So that each grouping gets its own identifier. I've tried this:

SELECT Col1, Col2, Col3
row_number() OVER (PARTITION BY Col1, Col2, Col3
ORDER BY Col1, Col2, Col3) AS MyNumber
FROM MyTable

But I get this:

Col1 Col2 Col3 MyNumber
a b c 1
a b c 2
a b d 1
e f g 1
e f g 2

View 9 Replies View Related

SQL Server 2008 :: No Option To Automatically Partition Table Based On Unique Values Of Column?

Jun 17, 2015

A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.

View 4 Replies View Related

SQL Server 2008 :: How To Get Records In One Row

Apr 15, 2015

IdIncident IDIncident Description

2853459140511
2861733118512
2861733118601
2869113140511
2872349118512
2872349118601
2895400140511
2900999140511
2914574140511
2923590140511
2925735274503
2925735274504
2925735274511

View 9 Replies View Related

SQL Server 2008 :: Multiple Records On One Row

Aug 20, 2015

I have a table of customer sales (dbo].[CustomerSales] which has a complete record od sales of our products for the past 5 years. I would like to create a report that extracts all the sales per customer on one row ie [CustomerID] 00011 has had 25 sales in the past 5 years and I would like to have their sales from left to right (starting with their earliest sale date at the beginning [SaleDate] field on the one row. I would also like to include the store location field [Location] along with the date the sale took place. So in other words the extract would look something like:

[CustomerID], [SaleDate], [Location], [SaleDate], [Location], [SaleDate], [Location], [SaleDate], [Location], [SaleDate], [Location], etc etc

Obviously some customers will have had less sales than others in which case I’m assuming these fields would just contain NULL values.

View 4 Replies View Related

SQL Server 2008 :: How To Get Latest Records From Table

Mar 17, 2015

I have a table where i am inserting into temp table, I mean selecting the records from existing table. From this how can i get latest records.

create table studentmarks
(
id int,
name varchar(20),
marks int
)
Insert into dbo.studentmarks values(1,'sha',20);

[Code] ....

How to write a sql query to get the below output

studentname totalmarks

sha 90
hu 120

View 1 Replies View Related

SQL Server 2008 :: Selecting Records Not Between 2 Dates

Jul 22, 2015

I’m trying to extract a list of records that don’t appear between 2 dates. I have 4 tables:

tblCustomers
tblMachines
tblServiceOrders
tblMachinesServiced

tblCustomers contains a CustomerID that is unique to each customer.

tblMachines contains a list of all machines with a MachineID that is unique to each machine. It also contains the CustomerID number.

tblServiceOrders contains a list of each time each customer was serviced. It contains the ServiceDate, CustomerID, and ServiceOrderNo. But it does not have any information on the machines.

tblMachinesServiced contains a list of each machine that was serviced for each service order. It contains the ServiceOrderNo and the MachineID number.

What I want is to be able to extract a list of machines that were not serviced between 2 dates. What I end up getting is a list of machines that were serviced outside of the date range I provide.

For instance, say machine A was serviced in 2013 and 2015 but not in 2014. And say machine B was serviced in all 3 years. When I try to extract my list of machines not serviced in 2014 I end up with a list that contains machine A-2013, A-2015, B-2013 & B-2015. But what I need is just machine A-2014, since that machine wasn’t serviced in 2014.

I’ve tried several different queries but here is an example:

SELECT tblMachines.MachineID,ServiceMachines.ServiceDate
FROM tblMachines
LEFT JOIN
(SELECT MachineID, ServiceDate FROM tblServiceOrders, tblMachinesServiced
WHERE tblServiceOrders.ServiceOrderNo=tblMachinesServiced.ServiceOrderNo
) ServicedMachines
ON tblMachines.MachineID=ServicedMachines.MachineID
WHERE YEAR(ServiceDate) != '2014'

I understand why it returns the records that it does, but I'm not sure how to get what I want, which is a list of machines not serviced in 2014.

View 9 Replies View Related

SQL Server 2008 :: Grouping By Records That Are Already Grouped?

Jul 27, 2015

I have results from a query that have anywhere from 1-4 results. I have a subid of 1-4 which is grouped by a certain criteria. so for example...

id subid text
1 processing A records
2 1000 records processing
3 importing A records
4 1000 records processed
1 processing B records
2 500 records processing
3 importing B records
4 1000 records processed

Here is what my desired output is giving each group of subids 1-4 an id to be grouped together.

id subid text
1 1 processing A records
1 2 1000 records processing
1 3 importing A records
1 4 1000 records processed
2 1 processing B records
2 2 500 records processing
2 3 importing B records
2 4 1000 records processed

View 1 Replies View Related

SQL Server 2008 :: Only Show Duplicated Records

Aug 4, 2015

I have a query below to show all the records with joining these two tables.

SELECT DISTINCT B.BF_ORGN_CD, B.LEV5, A.BF_ACTY_CD
FROM BF_ORGN A
INNER JOIN BF_ORGN_CNSL_TBL B
ON A.CD=B.BF_ORGN_CD
WHERE A.BF_ACTY_CD IS NOT NULL
ORDER BY B.BF_ORGN_CD,A.BF_ACTY_CD

My goal is only to show all the duplicate records.

Bf_ORGN_CD LEV5 BF_ACTY_CD
AC_21234_2 AC_21200_1 402
AC_21236_2 AC_21200_1 402
AC_21238_2 AC_21200_1 402
AC_29000_1 AC_29000_1 802 ---> NOT SHOW (ONLY 1 RECORD)
AC_29988_1 AC_29988_1 801 ---> NOT SHOW (ONLY 1 RECORD)

[code]...

View 9 Replies View Related

SQL Server 2008 :: Records That Don't Match From Two Database

Sep 24, 2015

I am using the following views from two db's to find records that don't match.

My question is can I have output of fields from the second table

SELECT distinct T1.[last name],t1.[first name],ENum
FROM ECLINICIAN_Info T1
WHERE NOT EXISTS(SELECT *
FROM ACLINICIAN_Info T2
WHERE t1.Enum = t2.Anum
and t1.[last name] = t2.lname and t1.[first name] = t2.Fname)

I would like to include t2.Anum in my select ...

View 5 Replies View Related

SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.

E.g.

Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'

Table

Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

SQL Server 2008 :: Append Records With Different Number Of Columns?

Feb 16, 2015

I have a table of Customers & their data in about 20 Columns.

I have another table that has potential Customers with 3 Columns.

I want to append the records from Table 2 onto Table 1 to the Columns with the same names.

I've thought of using UNION ALL or Select Insert but I'm mainly stuck on the most efficient way to do this.

There is also no related field that can be used to join the data as these Customers in table 2 have no Customer ID yet as they're only potential Customers.

Can I just append the 3 columns from Table 2 to the same 3 columns in table 1?

View 2 Replies View Related

SQL Server 2008 :: Identify Columns And / Or Tables From Records

Mar 3, 2015

I'm using MS SQL Server 2008 and I'm trying to figure out if it is possible to identify what tables / columns contain specific records.

In the example below information generated for the end user, so the column headers (Customer ID, Customer, Address, Phone, Email, Account Balance, Currency) are not necessarily the field names from the relevant tables, they are simply more identifiable headers for the user.

Customer ID CustomerAddress Phone Email Account Balance Currency
js0001 John Smith123 Nowhere Street555-123-456 jsmith@nowhere.com-100 USD
jd2345 Jane Doe 61a Down the road087-963258 jdoe@downthe road.com-2108 GBP
mx9999 Mr X Whoknowsville 147-852369 mrx@whoknows.com0 EUR

In reality the column headers may be called eg (CustID, CustName, CustAdr, CustPh, CustMail, CustACBal, Currency).

As I am not the generator of this report, I would like to know whether or not it is possible to identify the field names and / or what tables they exist in, if I were to used the report info to search for it. For example, could I perhaps find out the field name and table for "jd2345" or for "mrx@whoknows.com", because the Customer ID or Email may not be what the actual fields are called.

I'm not a DB admin and I don't have rights to do a stored procedure on the server. I'm guessing what I want is not so simple to do, but is it possible to do via a query?

View 2 Replies View Related







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