MS SQL Server :: Difficult Select Distinct Query

Query Help - Select Distinct Possibly?
I need to return the current case cost for every UPC in my table. In my current query I return case costs that have an effective date of today or earlier. The problem is that in my results, one UPC may have two or more case costs that were are effective <= GETDATE(). I can sort it by effective date (DESC) so I know the first of every UPC in my results will be the current effective case cost, but how do I modify my query so that in my result set I only get the first of every UPC?

Here is my query:

SELECT factCaseCosts.nUpcKey, factCaseCosts.dCaseCost, factCaseCosts.dtEffectiveDate
FROM factCaseCosts
WHERE (factCaseCosts.dtEffectiveDate <= GETDATE())
ORDER BY dtEffectiveDate DESC

Here is my current result set:
52023.762006-08-01 00:00:00
52023.762006-02-18 00:00:00
52123.762006-08-01 00:00:00
52123.762006-02-18 00:00:00
52230.362006-08-01 00:00:00
52230.362006-02-18 00:00:00
52323.762006-08-01 00:00:00
52323.762006-02-18 00:00:00

I only want the first 520 returned, the first 521 returned, the first 522 returned, and the first 523 returned.
How can I do this?
Thanks!

View Replies !

SELECT Distinct Query Problem
Hello,

I'm reasonably new to SQL.

I have a table of information about meetings with various people. In it, I have the person's name and the date/time of the meeting (simplified example).

I need to write a query that SELECTs only the most recent meeting for each person.

Any ideas would be greatly appreciated.

View Replies !

How To Use ORDER BY Clause In An SELECT DISTINCT Sql Query When AS SINGLECOLUMN Is Defined?
Hi,
I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow...
Can anyone help? Thanks in advance
Gemma

View Replies !

Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?
Hi, I have the following script segment which is failing:

CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))

INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache

When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."

Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.

The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.

Any ideas?

View Replies !

Difficult SQL Select Statement
Hi all,

i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.

sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.

i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.

Alon.

View Replies !

Difficult Query Help
I have a table that stores billing rates for our employees by client.Each employee can have a different billing rate for each client for aspecified period. Here are the columns in the table.eid - Employee ID#cid - Client ID#startdt - start date of billing rateenddt - end date of billing ratebrate - billing rateI need to create a script that will verify that for a given eid, and cidthat either the startdt or enddt for one billing rate, the periods donot overlap.For example, I need to be able to detect overlaps such as this:eid cid startdt enddt brate001 001 1/1/2003 12/31/2003 $50001 001 11/01/2003 04/01/2004 $75*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !

Difficult Query: Is This Possible In SQL?
suppose I have the following table:CREATE TABLE (int level, color varchar, length int, width int, heightint)It has the following rows1, "RED", 8, 10, 122, NULL, NULL, NULL, 203, NULL, 9, 82, 254, "BLUE", NULL, 67, NULL5, "GRAY", NULL NULL, NULLI want to write a query that will return me a view collapsed from"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)So I want a query that will returnGRAY, 9, 67, 25The principle is that looking from the bottom level up in each columnwe first see GRAY for color, 9 for length, 67 for width, 25 forheight. In other words, any non-NULL row in a lower level overridesthe value set at a higher level.Is this possible in SQL without using stored procedures?Thanks!- Robert

View Replies !

Difficult T-SQL Query (for Me Anyways)
Hi.

I am developing for a system that receives an input from an external modem.

The Transaction is split into 2 sections,

Section 1 = grants the transaction ID,

Section 2 = deliver the transaction Data.

I have 2 corresponding tables,

One called tblremoteunitrequestID (Where the transaction ID is granted)

The other called tblremoteunitrequests (Where the transaction is completed, about 1 second later)

I am writing a diagnostic report that determines if the first part of the transaction completes but the second part fails.

I am having difficulties designing the SQL for this.

Here is some sample data for tblremoteunitrequestID: (The first stage of the transaction)

RecordDate | Serial

13/11/2006 14:00:36 0000-0000-0000-0006
13/11/2006 14:00:30 0000-0000-0000-0004
13/11/2006 13:59:04 0000-0000-0000-0092 (This didtn transaction didnt complete)
13/11/2006 12:15:22 0000-0000-0000-0092 (nor did this one)
13/11/2006 10:31:54 0000-0000-0000-0092
13/11/2006 10:00:29 0000-0000-0000-0006

Here is some sample data for tblremoteunitrequests: (The second stage of transaction, 1st stage has to be completed beforehand)

DateReceived | Serial

13/11/2006 14:00:37 0000-0000-0000-0006
13/11/2006 14:00:31 0000-0000-0000-0004
13/11/2006 10:31:56 0000-0000-0000-0092
13/11/2006 10:00:31 0000-0000-0000-0006
13/11/2006 10:00:25 0000-0000-0000-0004
13/11/2006 07:19:13 0000-0000-0000-0020

From this data I can see that serial number 0000-0000-0000-0006 Successfully completed part 1 and part 2 of the transaction, as did serial number 0000-0000-0000-0004.

Serial number 0000-0000-0000-0092 had trouble, it connected at 13:59:04 (tblremoteunitrequestID) but part 2 didnt complete, so it wasent saved in tblremoteunitrequests. The same happened at 12:15:22 but at 10:31:54 it was successful so it was saved.

I Only want to display the transactions that didnt complete, sounds easy huh?

This is what I hope to get in my Results table:

DateReceived | Serial

13/11/2006 13:59:04 0000-0000-0000-0092
13/11/2006 12:15:22 0000-0000-0000-0092

I was experimenting with T-SQL today, this is what I have done so far:

SELECT DISTINCT
                      TBLRemoteFeildUnitRequestID.Serial, TBLRemoteFeildUnitRequestID.RecordDate,

CASE WHEN TBLRemoteUnitRequests.DateReceived BETWEEN DATEADD(SECOND,-1,TBLRemoteFeildUnitRequestID.RecordDate) AND DATEADD(SECOND,10,TBLRemoteFeildUnitRequestID.RecordDate)

THEN ' Ok'

ELSE ' Not ok'

END AS PROBLEM

FROM         TBLRemoteFeildUnitRequestID LEFT OUTER JOIN
                      TBLRemoteUnitRequests ON TBLRemoteFeildUnitRequestID.Serial = TBLRemoteUnitRequests.Serial
WHERE     TBLRemoteFeildUnitRequestID.RecordDate BETWEEN DATEADD(WEEK, - 2, GetDate()) AND GetDate()

ORDER BY RecordDate DESC

This kinda worked, but it caused records that satisfied the between condition to be displayed twice, once as "Ok" and once as "Not ok".

Heres a sample of the result I got:

Serial | RecordDate (1st part of transaction) | Status

0000-0000-0000-0006 2006-11-13 14:00:36.000 Ok (Duplicated)
0000-0000-0000-0006 2006-11-13 14:00:36.000 Not ok
0000-0000-0000-0004 2006-11-13 14:00:30.000 Not ok (Duplicated)
0000-0000-0000-0004 2006-11-13 14:00:30.000 Ok
0000-0000-0000-0092 2006-11-13 13:59:04.000 Not ok (Correct) (Not duplicated)
0000-0000-0000-0092 2006-11-13 12:15:22.000 Not ok (Correct) (Not Duplicated)
0000-0000-0000-0092 2006-11-13 10:31:54.000 Not ok (Duplicated)
0000-0000-0000-0092 2006-11-13 10:31:54.000 Ok
0000-0000-0000-0006 2006-11-13 10:00:29.000 Ok (Duplicated)
0000-0000-0000-0006 2006-11-13 10:00:29.000 Not ok

I have just about had enough, I have wasted an entire day on this

Someone please Help

Dan

View Replies !

Difficult Query
Hey i have a query i need help with.

I have a table where i have 4 columns in it which i need to group together and then sum up a cost column also. I want to sum up the columns where i have a parent and and child and then i want to sum up the other column where i have only a child.
Example of the data is below. I think i need to do this in a sub query

ID Ind Parent Child Cost
P110041012705921.8000
W11004101270595.4500
A110041012705921.8000
B110041012705916.3500
R110041012705916.3500
B0100420043.3000
P0100420043.3000
W0100420021.6500

View Replies !

Difficult Summing Query
Hello,Here is a brief summary:Table 1 = All Accounts- with fields such as Customer ID and Account #Table 2 = Deposit Balance Table- with fields such as Account #, BalanceTable 3 = Loan Balance Table- with fields such as Account #, BalanceAll accounts are either deposit accounts or loan accounts. What I needto do is to gather information about total balances in both depositsand loans for each customer. I haven't been able to hit the right queryfor doing this. I can easily get information about one or the other,such as the following:SELECT All_Accounts.Customer_ID, COUNT (DISTINCT(Deposit_Balance_Table.Account_Number)), Sum(Deposit_Balance_Table.Balance)FROM Product_Table, Deposit_BalanceWHERE (Product_Table.Account_Number=Deposit_Balance.Acco unt_Number)GROUP BY Product_Table.Customer_ID ORDER BY 1Which will give me one row for each user, and show me the total numberof deposit accounts each customer has and a sum of the balances in eachof those accounts. I can make a similar query involving Loan Accounts.As soon as I try to draw both, however, I wind up below my depth.Something to do with the handedness of my joins, I believe. Often Iwill get one column of information (either deposits or loans), or thequery will fail because the join I'm attempting is invalid, etc. I needto take every row in the All_Accounts table, match each one to itsbalance in either the Deposit or Loan table, and then group them all bythe Customer ID and sum them, so that I can find out the totalrelationship balance per customer. Any help would be appreciated.

View Replies !

Simple Or Difficult Query
hello,

I could need some help with a little query.

table "acme"

name1 varchar(128)
name2 varchar(128)
idate datetime

content

A,H,1/1/2005
A,H,2/1/2005
A,I,2/1/2005
A,J,3/1/2005
B,K,4/1/2005
B,L,5/1/2005

I want the following result (for 'A'):

1/1/2005,1
2/1/2005,3
3/1/2005,4

I want to filter for Column "Name1" and cumulative count the entries grouped by date.

what's the simplest solution?

best regards, thilo.

View Replies !

Difficult Query With Many-To-Many Relationship
I'm writing a workflow management application for my work, and its somewhat complicated, here's a general idea of how it works:

- Anything that a company does is defined by a workflow.
- A workflow consists of tasks.
- Some tasks in a workflow can't be started until other tasks have been completed. If task A can't be started until tasks B and C are finished, then task A depends on B and C.

You might imagine that a bank has a workflow for handling a house loan. Before a bank could sign a contract with an applicant, they'd need proof of house ownership, but before they could get proof of house ownership they need an applicant's proof of identity like a driver's license or military ID.

Here's an oversimplified visual:

Each arrow points to its dependency. Each task can have multiple dependencies.

The setup above is represented in the database by a Tasks and a Dependencies table. Tasks has an ID field, and Dependencies has a TaskID and DependencyID field which are both foreign keys to Tasks.ID.

Code:

[Tasks]
ID Status Name
-- ------ ----
1 Done Start Processing Loan Application
2 Done Photocopy applicant's driver's license
3 NotDone Photocopy proof of house ownership
4 NotDone Get a copy of applicant's W-2 forms
5 NotDone Perform credit check on applicant
6 NotDone Sign loan contract

[Dependencies]
TaskID DependencyID
------ ------------
1 0
2 1
3 1
4 2
5 2
5 3
6 4
6 5

Tasks has a many-to-many relationship with itself.

Here's the hard part:
- A task can't be started until all of its dependencies have been completed.
- after a task is completed (meanings its status is marked "done"), I need to return a list of all the new tasks that are ready to be started.

When TaskID 2 is marked "Done", then TaskID 4 is ready to begin; however, TaskID 5 is not ready to begin since it depends on 2 and 3, and 3 hasn't been completed yet.

The requirements of the query are very simple, but the implementation is difficult.

I'll post a prelimenary solution in the next post:

View Replies !

A Challenge: Need To Write A Difficult Query
GO
CREATE TABLE [dbo].[Product]
(
[ProductId] [smallint] IDENTITY(1,1) NOT NULL CONSTRAINT PkProduct_ProductId PRIMARY KEY,
[Name] [varchar](52) NOT NULL,
[Type] [smallint] NOT NULL,
)
For this table
I have to write the querywhich willget the TOP 1 Row of each Type.
I know the alternate way of doing this by union.
But this is not professional.
Can anyone resolve this issue?

View Replies !

Trying To Add A NON-DISTINCT Field To A DISTINCT Record Set In A Query.
I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks

View Replies !

Learning SQL: Rather Difficult Query Needed: And If You Know This With Explanation What You're Doing If Possible :)
If you know the answer please explain what you're doing if possible, that'll help me :)I have the following tables:CREATE TABLE [dbo].[tblUserData]( [UserCode] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DisplayName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,) ON [PRIMARY]CREATE TABLE [dbo].[tblFriends]( [UserCodeOwner] [int] NOT NULL, [UserCodeFriend] [int] NOT NULL, [createdate] [datetime] NOT NULL CONSTRAINT [DF_tblFriends_createdate] DEFAULT (getdate())) ON [PRIMARY]in tblFriends relations are stored twice, so for a relation between user 5 and 6, there will be 2 rows: 5-6 and 6-5Now, I want to get the columns (UsercodeOwner,UsercodeFriend,createdate,username,displayname) for relations that were created in tblFriends in the last 10 days for the FRIENDS of a person with usercode 5.Example:tblUserdata5 peter Petertje6 john Johnny11 simon SimonSays15 monique MontjetblFriends5 6 'createdate 30 days ago'5 11 'createdate 5 days ago'6 5 'createdate 30 days ago'6 11 'createdate 3 days ago'6 15 'createdate 7 days ago'11 5 'createdate 5 days ago'11 6 'createdate 3 days ago'15 6 'createdate 7 days ago'The resultset for a query on usercode 5 would now be (usercode1, username1, displayname1,usercode2, username2, displayname2,createdate):6 john Johnny 11 simon SimonSays 'createdate 3 days ago'6 john Johnny 15 monique Montje 'createdate 7 days ago'As you can see each relation is only returned twice even though there are always two entriesWhat would be the SQL statement, if possible without temp table..Thanks!

View Replies !

Difficult Query: Return Recordset From Concatenated Strings?
Hi All,I have what seems to me to be a difficult query request for a databaseI've inherited.I have a table that has a varchar(2000) column that is used to storesystem and user messages from an on-line ordering system.For some reason (I have no idea why), when the original database wasbeing designed no thought was given to putting these messages inanother table, one row per message, and I've now been asked to providesome stats on the contents of this field across the recordset.A pseudo example of the table would be:custrep, orderid, orderdate, comments1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer askedfor a brown model2, 10002, 2004-04-12, :Comment 3:Comment 4:1, 10003, 2004-04-12, :Comment 2:Comment 8:2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled orderSo, what I've been asked to provide is something like this:orderdate, custrep, syscomment, countofsyscomments2004-04-12, 1, Comment 1, 12004-04-12, 1, Comment 2, 22004-04-12, 1, Comment 3, 12004-04-12, 1, Comment 8, 12004-04-12, 2, Comment 1, 12004-04-12, 2, Comment 3, 12004-04-12, 2, Comment 4, 22004-04-12, 2, Comment 6, 22004-04-12, 2, Comment 7, 1I have a table in which each of the system comments are defined.Anything else appearing in the column is treated as a user comment.Does anyone have any thoughts on how this could be achieved? The endresult will end up in an SQL Server 2000 stored procedure which willbe called from an ASP page to provide order taking stats.Any help will be humbly and immensely appreciated!Much warmth,Murray

View Replies !

&&"distinct&&" In Select Query
when i use select :

select flattened

(select productid, $support from [model name].[table] )......

i have result with many record same.

so i write :

select flattened

(select distinct productid, $support from [model name].[table] )......

but when i run, it's error. ( don't know what error).

how can i do to get table record with not same record (loop)

note : it write in DTS and i use sql 2000

View Replies !

Please Help Me: SQL SELECT DISTINCT
I have a table myTable (ID, Year, Name, Note)data in this table:ID Year Name Note 1 2008 Petter hdjhs2 2008 Nute jfdkfd3 2007 Suna dkfdkf4 2007 Para jfdfjd5 2009 Ute dfdlkf Please help me to Select DISTINCT [Year]]ex:1 2008 Petter hdfdfd3 2007 Suna fdkfdk5 2009 Ute fkdfkdfd Thank!

View Replies !

SELECT Distinct Help
Hello Everyone
Hopefully someone can help me create a SQL statement for this.
I need the ff: fields
Prov_ID, Record_ID, PROV_NAme, LOC_city, LOC_Zip_CODE, Specialty
Let say I have a table.
Prov_ID, Record_ID, PROV_NAme, LOC_city, LOC_Zip_CODE. Specialty1000 999 Mike James Plano 75023 Internal Medicine1000 998 Mike James Allen 75021 Internal Medicine3333 700 John Smith Arlington 70081 Dermatologist3333 701 John Smith Dallas 72002 Dermatologist2222 630 Terry Walker Frisco 75001 Optalmologist2222 632 Terry Walker Dallas 76023 Optalmologist4444 454 Tim Johnson San Anontio 72500 Internal Medicine 4444 464 Tim Johnson Frisco 72660 Internal Medicine
I want to select only "one" instance of the provider it doesnt matter what is selected
either the first address or the second address.
It should show
Prov_ID, Record_ID, PROV_NAme, LOC_city, LOC_Zip_CODE. Specialty1000 999 Mike James Plano 75023 Internal Medicine3333 700 John Smith Arlington 70081 Dermatologist2222 632 Terry Walker Dallas 76023 Optalmologist4444 464 Tim Johnson Frisco 72660 Internal Medicine
And yes, the table is not Normalized..Is there anyway I could get away with it without having to normalize?
Thanks
Lorenz

View Replies !

Select Not Distinct?
Is their a way to select all items from a table that are not distinct? Meaning, I want to know which items in a column occur more than once.
Example:
Suppose we have a table with student names, ss# and address. I want to display only records where their is more than one studen with the same name. So for example their could be ten people with the name of "Mike" in a class?

Ralph

View Replies !

SQL Select DISTINCT?
OK I have a Forum on my website make up of 3 tablesTopisThreadsMessageI show a list of the 10 most recent Changed Threads. My Problem is that my Subject field is in the messages Table, IF I link Threads to Messages then try to use Select Disticnt I get mutliple Subject fields as the messsges are not unique (obvisally) So I want to get the top 10 Threads by postdate and link to the Messages table to get the Subject headerAny help? Or questions to explain it better?

View Replies !

SELECT DISTINCT
I don't know what the correct syntax is to do what I want with the DISTINCTfunction (if it's actually possible).I have a query which displays a variety of fields from a variety of tables(pretty standard).However, I only want to show records where the contents of one particularcolumn in the query are unique - I do not want to perform the function onthe entire record because other fields in the records may be duplicated foras reason.

View Replies !

Select Distinct Help?
Can you have "Select Distinct" in Union Query,because that is what I am trying to do and this is the error message I get.

"The text, ntext, or image data type cannot be selected as DISTINCT."

I would need to do that because i have duplicate records,because these records are getting written into the db when templates are generated and sometimes if they double click it generates two and writes that many results as well, so that is why I was thinking that select distinct would solve my problem.

Thanks for your help

This is the query in question:

SELECT Distinct 'O' AS Origin, a.RecordID, a.RelocateID, a.SupplierID, a.DateIn, a.DateOut, a.NoOfDays, a.AgreeAmt, a.PaymentMethod, a.AccomType, a.Reason,
a.InvRecvd, a.RelocateeTempAccomTS, a.BedConfiguration, a.NumberOfPax, a.AdditionalItems, a.Currency, a.TotalAmount, a.EnteredBy,
a.LastModifiedBy, a.ReferenceNumber, a.Location, a.Comments, a.ArrivalTime, a.PONumber,CommissionRate, ISNULL
((SELECT TOP 1 ExchangeRateToUSD
FROM luCurrencyExchangeRates c
WHERE a.Currency = c.CurrencyID AND a.DateIn >= c.ActiveDate), 1.0) AS ForeignExchangeRate, ISNULL
((SELECT TOP 1 ExchangeRateToUSD
FROM luCurrencyExchangeRates c
WHERE 'AUD' = c.CurrencyID AND a.DateIn >= c.ActiveDate), 1.0) AS AUDExchangeRate, a.WhenConfirmed, e.RequestID AS RequestID,
e.DocumentID AS DocRequestID, e.RequestWhen AS RequestWhen, e.WhereClause AS WhereClause,
dbo.luDecisionMaker.DecisionMakerName AS DecisionMadeBy, dbo.viewZYesno.Description AS CommissionableDesc
FROM dbo.RelocateeTempAccom a LEFT OUTER JOIN
dbo.luDecisionMaker ON a.DecisionMaker = dbo.luDecisionMaker.DecisionMakerID LEFT OUTER JOIN
dbo.viewZYesno ON a.Commissionable = dbo.viewZYesno.[Value] LEFT OUTER JOIN
dbo.docRequests e ON '{RelocateeTempAccom.RecordID}=' + CONVERT(VARCHAR a.RecordID) = e.WhereClause
WHERE (ISNULL(a.Cancelled, 0) = 0)

UNION ALL

SELECT Distinct 'D' AS Origin, RecordID, RelocateID, DTASupplierID AS SupplierID, DTADateIn AS DateIn, DTADateOut AS DateOut, DTANoOfDays AS NoOfDays,
DTAAgreeAmt AS AgreeAmt, DTAPaymentMethod AS PaymentMethod, DTAAccomType AS AccomType, Reason, InvRecvd,
RelocateeDTATS AS RelocateeTempAccomTS, BedConfiguration, NumberOfPax, AdditionalItems, Currency, DailyTotal AS TotalAmount, EnteredBy,
LastModifiedBy, ReferenceNumber, Location, Comments, ArrivalTime, PONumber,CommissionRate, ISNULL
((SELECT TOP 1 ExchangeRateToUSD
FROM luCurrencyExchangeRates d
WHERE b.Currency = d .CurrencyID AND b.DTADateIn >= d .ActiveDate), 1.0) AS ForeignExchangeRate, ISNULL
((SELECT TOP 1 ExchangeRateToUSD
FROM luCurrencyExchangeRates d
WHERE 'AUD' = d .CurrencyID AND b.DTADateIn >= d .ActiveDate), 1.0) AS AUDExchangeRate, WhenConfirmed, e.RequestID AS RequestID,
e.DocumentID AS DocRequestID, e.RequestWhen AS RequestWhen, e.WhereClause AS WhereClause,
dbo.luDecisionMaker.DecisionMakerName AS DecisionMadeBy, dbo.viewZYesno.Description AS CommissionableDesc
FROM dbo.RelocateeDTA b LEFT JOIN
dbo.luDecisionMaker ON b.DecisionMaker = dbo.luDecisionMaker.DecisionMakerID LEFT JOIN
dbo.viewZYesno ON b.Commissionable = dbo.viewZYesno.[Value] LEFT OUTER JOIN
dbo.docRequests e ON '{RelocateeDTA.RecordID}=' + CONVERT(VARCHAR, b.RecordID) = e.WhereClause
WHERE ISNULL(Cancelled, 0) = 0

View Replies !

Select Distinct
Hi,

I wonder if anyone here can shed some light on why the query below produces duplicate EmailAddress values even though we specify the DISTINCT clause.

SELECT DISTINCT(EmailAddress) SubscriberID, FirstName, Surname, SubscriberID
FROM TestMailingList
ORDER BY EmailAddress

Thanks.

View Replies !

Select Distinct
Hi! I have 4 tables and they have a common column (eg. regionid). These
4 tables have data overlapping with the others. Some data exist in a table
but not on the others. What I want to do is to do a select that will display
all distinct regionid from these tables. It should be total of all the tables but will suppress any duplicates with the others.

Note that UNION is working but I can't use that. Why ? because UNION is not supported or maybe not working properly with RDB database. I'm doing an appliaction for heterogenous datasource.

Any tips, hints or info will be appreciated.
thanks in advance.

zrxowm

Table REGION1 :
RegionID RegionDescription
----------- --------------------------------------------------
10 Place1
11 Place11
1 Eastern
2 Western
3 Northern
4 Southern
(6 row(s) affected)

Table REGION2 :
RegionID RegionDescription
----------- --------------------------------------------------
21 Place21
22 Place22
1 Eastern
2 Western
3 Northern
4 Southern
(6 row(s) affected)

Table REGION3 :
RegionID RegionDescription
----------- --------------------------------------------------
33 Place33
31 Place31
1 Eastern
2 Western
3 Northern
4 Southern

(6 row(s) affected)

Table REGION4 :
RegionID RegionDescription
----------- --------------------------------------------------
41 Place41
42 Place42
1 Eastern
2 Western
3 Northern
4 Southern

(6 row(s) affected)

View Replies !

Select Distinct
Can I run Select distinct on one fieldname only while I'm selecting more than one fielname, like

Select Distinct col1, col2, col3 from table

I need distinct on col1 only and not on the other 2 columns, is it possible.

Thanks

View Replies !

Select Distinct
Does anyone know why this does not work?

SELECT DISTINCT tb2.column20 tb2.column20, tb1.column10, tb2.column21, tb2.column22, tb3.column30
FROM table1 tb1, table2 tb2, table3 tb3
WHERE tb1.column11 = 'P'
AND tb2.column23 = 'P'
AND tb1.column12 = tb2.column24
AND tb2.column25 = tb3.column31
ORDER BY tb2.column20

Its supposed to return only the distinct entries in tb2.column20

View Replies !

Select Distinct
I may be new at this but I can't find any explanation why
SELECT DISTINCT(Stno), Grade
shows distinct occurrences for each Stno-Grade combination rather than just distinct occurences of Stno. What is the solution?

View Replies !

Select Distinct Help

select distinct ISNULL (a.account,'') as "Account", ISNULL (c.address1,'') as "Address",
ISNULL (c.city,'') as "City", ISNULL (c.state,'') as "State",
ISNULL (c.postalcode,'') as "Zip Code", ISNULL (a.mainphone,'') as "Phone",
a.userfield1 as "GID", s.division
from sysdba.account as a
join sysdba.address as c on a.addressid = c.addressid
join sysdba.staff as s on a.accountid = s.accountid
where a.type like '%client%' and a.userfield1 is not null and (s.division like '%HR%' or s.division like '%db%') and s.type = 'client'

So what happens now is that if an account is listed in two division I get two distinct rows returned, but each with the same GID column. When I try to push this to a new database that has GID as the primary key I get duplicate on that column and it errors out.

I need to be able to get only a single row if the division is both HR and db. how to tackle this problem.

Thanks!

View Replies !

Select Distinct
Hi members,

Is there a way to count the number of data with distinct column a and column b (combination)??

ex

col A               Col B
1                        1
1                        2
1                        1
2                        1
3                        3
3                        3
4                        3

should give 5.
The ones in red are duplicates that I want to eliminate.
Thanks,

View Replies !

Select Distinct???

select ExpenseCodeID, [Group], SubGroup, GLAccount,ExpenseCode, ProjType

from BridgeFinance..OPS_ExpenseCodes

Order By ExpenseCode

I have this query only thing wrong with it is that I dont know how to only select different values from my expenseCode column that looks like below....I dont want to select "Employee Only Meals" as many times as it appears in the table just once do i want to select it....any help with how i should write my query would be great! thanks!

Administrative contract work
Cell phone
Courier/Shipping
Employee only Meals
Employee only Meals
Employee only Meals
Employee only Meals
Employee only Meals
Employee trans/parking
Health Club Memberships
Home Office Expenses
IT equipment-non capitalizable

View Replies !

Select Distinct
I have a select query
Select distinct a,b,c,d from xyz
I would like to know what the syntax is if I want only a,b,c to be distinct and not d.
I tried something like
Select (distinct a,b,c),d
but getting error what is the correct query to do this.
Please help.

View Replies !

SELECT DISTINCT
Hello,

When I try the SELECT DISTINCT like this:

USE CHEC

SELECT DISTINCT
[DATE_CONVERSION_TABLE_NEW].MONTH,
DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) AS DayofMonth,
DAT01.[_@550] AS LoanType,
DAT01.[_@051] AS Branch,
DAT01.[_@TP] AS ProdTypeDescr,
SMT_Branches.[BranchTranType] AS TranType,
--SMT_Branches.[AUCode] AS AuCode,
COUNT(*) AS Totals
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW]
--ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]
ON DAT01.[_@040] = [_@040]
INNER JOIN SMT_BRANCHES
ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType]
WHERE
DAT01.[_@040] Between '06/01/2006' And '06/30/2006'
And SMT_BRANCHES.[BranchTranType] = 'RETAIL'
AND DAT01.[_@051] = '540'
--And SMT_Branches.[AUCode] = '1882'
And DAT01.[_@TP] = '115'
And DAT01.[_@550] = '3'
GROUP BY
DAT01.[_@051],
DAT01.[_@550],
DAT01.[_@TP],
SMT_Branches.[BranchTranType],
--SMT_Branches.[AUCode],
[DATE_CONVERSION_TABLE_NEW].MONTH,
DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE])
ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH,
DAT01.[_@051],
DayofMonth ASC
--SMT_Branches.[AUCode] ASC
--COMPUTE sum(count(*))

I get the same result set as before.

What do I need to change?

Kurt

View Replies !

Help With A Distinct Select?
Hi,

I am new to this forum so hello to everyone!

I need some help getting unique records from a query, I have a large amount of nested selects and i want to only display distinct records, I have a unique identifier (party ID) but the code was written by someone else (who is on holiday!) and i need to work out where to insert the disctinct select (if at all? - open to a better way?) this query should pull back records and then the results are pasted in to excel, however would a DTS solve the issue with duplicates??

Any help more than appreciated!

Heres the code...

CREATE PROCEDURE dbo.negative_surplus_report

AS

SELECT dbo.Cubit_Override_ID.UserName AS [User], dbo.Cubit_Customers.RecordDateTime AS Date,

dbo.Cubit_Customers.Customer_Status AS [Customer Status], dbo.Cubit_Customers.Call_Prompted_By AS [Call Prompted By],

dbo.Cubit_Outcomes.Outcome_Description AS [Outcome], ISNULL(dbo.Cubit_EPH.Total_Balance, 0) AS [Egg Debt], ISNULL(dbo.Cubit_Debt.Income_Total,

0) AS Income, ISNULL

((SELECT SUM(Balance)

FROM Cubit_Debt_Card INNER JOIN

Cubit_Debt ON Cubit_Debt_Card.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID), 0) AS [External Card Debt], ISNULL

((SELECT SUM(Balance)

FROM Cubit_Debt_Loan INNER JOIN

Cubit_Debt ON Cubit_Debt_Loan.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID), 0) AS [External Loan Debt], ISNULL(dbo.Cubit_Spending.Out_Mortgage, 0)

AS [Mortgage Payment], ISNULL(dbo.Cubit_Spending.Out_Rent, 0) AS [Rent Payment], ISNULL(dbo.Cubit_Debt.Mortgage_Balance, 0)

AS [Mortgage Balance], ISNULL(dbo.Cubit_Debt.Property_Value, 0) AS Property, ISNULL(dbo.Cubit_Customers.Party_ID, '') AS [Party ID],

ISNULL(dbo.Cubit_Customers.Cubit_Cust_ID, '') AS [Cubit ID], ISNULL(dbo.Cubit_Spending.Out_Total, 0) AS Outgoings,

ISNULL(dbo.Cubit_EPH.Total_Monthly_Pmt, 0) AS [Egg Payments], ISNULL

((SELECT SUM(Monthly_Pmt)

FROM Cubit_Debt_Card INNER JOIN

Cubit_Debt ON Cubit_Debt_Card.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID), 0) AS [External Card Paymements], ISNULL

((SELECT SUM(Monthly_Pmt)

FROM Cubit_Debt_Loan INNER JOIN

Cubit_Debt ON Cubit_Debt_Loan.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID), 0) AS [External Loan Payments], dbo.Cubit_Debt.Income_Total -

(SELECT SUM(Monthly_Pmt)

FROM Cubit_Debt_Card INNER JOIN

Cubit_Debt ON Cubit_Debt_Card.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID) -

(SELECT SUM(Monthly_Pmt)

FROM Cubit_Debt_Loan INNER JOIN

Cubit_Debt ON Cubit_Debt_Loan.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID) - dbo.Cubit_Spending.Out_Total - dbo.Cubit_EPH.Total_Monthly_Pmt AS Surplus,

dbo.Cubit_Override_ID.Mandate_Level

FROM dbo.Cubit_Customers INNER JOIN

dbo.Cubit_Managers ON dbo.Cubit_Customers.Manager_ID = dbo.Cubit_Managers.Manager_ID INNER JOIN

dbo.Cubit_Areas ON dbo.Cubit_Managers.Area_ID = dbo.Cubit_Areas.Area_ID LEFT OUTER JOIN

dbo.Cubit_EPH ON dbo.Cubit_Customers.Cubit_Cust_ID = dbo.Cubit_EPH.Cust_ID LEFT OUTER JOIN

dbo.Cubit_Spending ON dbo.Cubit_Spending.Cust_ID = dbo.Cubit_Customers.Cubit_Cust_ID INNER JOIN

dbo.Cubit_Outcomes ON dbo.Cubit_Customers.Outcome_ID = dbo.Cubit_Outcomes.Outcome_ID LEFT OUTER JOIN

dbo.Cubit_Additional_MI_Data ON dbo.Cubit_Customers.Cubit_Cust_ID = dbo.Cubit_Additional_MI_Data.Cubit_Cust_ID INNER JOIN

dbo.Cubit_Override_ID ON dbo.Cubit_Customers.Input_By_NTID = dbo.Cubit_Override_ID.NT_ID LEFT OUTER JOIN

dbo.Cubit_Debt ON dbo.Cubit_Customers.Cubit_Cust_ID = dbo.Cubit_Debt.Cust_ID

WHERE (dbo.Cubit_Areas.Area_ID IN (2, 3, 4, 11, 12)) AND (dbo.Cubit_Customers.Non_Relevant_Call = 0) AND (dbo.Cubit_Customers.Spending_Assessed = 1)

AND (dbo.Cubit_Customers.Debt_Assessed = 1) AND (dbo.Cubit_Debt.Income_Total > 0) AND (dbo.Cubit_EPH.Total_Monthly_Pmt < 999999) AND

(dbo.Cubit_Debt.Income_Total -

(SELECT SUM(Monthly_Pmt)

FROM Cubit_Debt_Card INNER JOIN

Cubit_Debt ON Cubit_Debt_Card.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID) -

(SELECT SUM(Monthly_Pmt)

FROM Cubit_Debt_Loan INNER JOIN

Cubit_Debt ON Cubit_Debt_Loan.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID) - dbo.Cubit_Spending.Out_Total - dbo.Cubit_EPH.Total_Monthly_Pmt < 0) AND

(dbo.Cubit_Customers.RecordDateTime >= '04/11/2006')

ORDER BY dbo.Cubit_Areas.Area_ID, dbo.Cubit_Override_ID.UserName, dbo.Cubit_Customers.RecordDateTime, Cubit_Customers.Cubit_Cust_ID

Thanks!

Matt

SQL newbie!

View Replies !

Using Distinct And * In Select
Bahrudeen writes "Hi..
hw to use Select query for both distinct and *

(eg) select * , distinct(building_id) from g_building where
(condition)

i want all information with distinct building id..
give a solution

advance thanx..."

View Replies !

Select Distinct
Hi. I am trying to create a view where it will find out the sum of hours for each employee, for each month and year.

SELECT DISTINCT EmpId,
SUM(Hours) AS Hours,
YEAR(WeekStartDate) AS startyear,
MONTH(WeekStartDate) AS startmonth
FROM dbo.BankHours_History
GROUP BY EmpId, WeekStartDate

View Replies !

How Do I Use Order By When I Use Select Distinct.
Hi
I have a query which returns some rows.. what happens if i use a select distinct instead of a select.. this is my sproc
DECLARE @Counter TABLE(
PlanId int,
FundId int,
ClientFundName varchar(110),
DisplayOrder int IDENTITY(1,1),
IsDefault bit,
IsPortfolioFundOnly bit
)
INSERT INTO @Counter
(
PlanId,
FundId,
ClientFundName,
IsDefault,
IsPortfolioFundOnly
)
SELECT
5923,
f.FundId,
d.FundName,
CASE WHEN d.FundDefault IS NULL THEN 0 ELSE 1 END,
CASE WHEN Lower(p.FundType) = 'modfundonly' THEN 1 ELSE 0 END
FROM
PlanDetail d
INNER JOIN Statements..Fund f
ON d.CUSIP = f.CUSIP
OR
d.Ticker = f.Ticker
OR
d.Ticker = f.ClientFundId
OR
d.CUSIP = f.ClientFundId
-- Do an internal join on the PlanDetail table to get the value of the FundType to derive whether
--fund can only be chosen as part of a portfolio.
LEFT JOIN PlanDetail p
ON d.FundName = p.FundName
AND
d.PortfolioName = p.PortfolioName
WHERE
d.PlanNumber IS NOT NULL
AND
p.PortFundPercent IS NULL
GROUP BY
f.FundId,
d.FundName,
d.FundDefault,
--d.PlanNumber,
--d.Cusip,
-- d.Ticker,
--d.RowNumber,
p.FundType

ORDER BY
Min(d.PlanNumber),
Min(d.RowNumber)

any help will be appreciated.
Thanks
Karen

View Replies !

Select Distinct Records
Hello, I have the following tables: declare @B table (Bid int identity, description varchar(50)) declare @P table (Pid int identity, Bid int, description varchar(50)) declare @T table (Tid int identity, description varchar(50)) declare @TinP table (TinPid int identity, Tid int, Pid int) insert into @B (description) select 'B1' insert into @B (description) select 'B2' insert into @P (description, Bid) select 'P1', 1 insert into @P (description, Bid) select 'P2', 1 insert into @P (description, Bid) select 'P3', 2 insert into @T (description) select 'T1' insert into @T (description) select 'T2' insert into @T (description) select 'T3' insert into @TinP (Tid, Pid) select 1, 2 insert into @TinP (Tid, Pid) select 2, 2 insert into @TinP (Tid, Pid) select 3, 3 select * from @B select * from @P select * from @T select * from @TinP I need to get all records in T (Tid and description) which are related to a given BId So for @Bi = 1 I would get: Tid Description 1 T1 2 T2 So I need the distinct values. How to solve this? Thanks, Miguel

View Replies !

Select Distinct Question
Is there a way to do a Select Distinct on a single column in a result set?
Example:
Select Distinct(PersonID) PersonID, FirstName, LastName From People

View Replies !

Select Distinct [name] From Merchantcategory
hi all,
i wanna ask a question regarding select distinct statement.....
this is my sql query : select distinct [name] from merchantcategory
how can i get the other attribute from the result of the query ???
is this possible ?
thanks

View Replies !

SELECT DISTINCT F1, F2, F3, F4 FROM 'table Name'
Newbie question
SELECT DISTINCT F1, F2, F3, F4 FROM 'table name' returns distinct rows for whole table. Is there a way to just return distinct rows from say column F1 instead of all the fields. I suppose i could just do SELECT DISTINCT F1, but also would like to display other fields. Thanks in advance

View Replies !

Select Distinct For Different Rows
I have the following tablecolumns: [col1], [col2],[col3] and [NAME].I want to select the name column for each row where [col1]='07'.The problem is that there are several rows where [col1] contains '07' and also the name is the same. [col2] and [col3] contain different data for these double rows...however, I cant use the [col1] and [col2] values in my query because I dont know what values they contain beforehand.So now, when I execute my query and add the DISTINCT key I still get all the double rows!I hope this explains my problem, help is really appreciated...ow, btw: deleting the double rows is not an option....

View Replies !

Distinct In Select Statement
Hey there, is there a way I can use command such as distinct in a select statement to do the following. Lets say I want to do a search of products based off their location and I want to list the companies that will have products in that area. I only want to list the company once, but if Iâ€™m searching by products in the area I might come up with 15 results for that company. I have not written the code yet for this, Iâ€™m just planning ahead.

Iâ€™m programming using VB so I guess I would do something like this.

State = Trim(Request.QueryString("State"))

SelectStatement = "Select * From Products Where State='" & _
State & "'"

This would of course give me hypothetically speaking a list as long as the amount of products in one given area. Is there a way to cut this down and only list the company once? Any help would be greatly appreciated. Thanks in advance.

View Replies !

Select Distinct Rows
Hi,

I'm having a little bit of trouble trying to figure out how to do this query, right now I have:

SELECT I.AppItemId, P.ProductID, P.PartNum, P.Relist, I.AppUserId
FROM ProductsToRelist I join Products P on P.ProductID = I.AppSKU
WHERE P.Relist = 1 and I.AppStatus = 5 and Not I.AppItemId is Null

and it returns something like this:

AppItemId ProductID PartNum Relist AppUserId
2786 -32730 SELECT_OOS11
2787 -32729 SELECT12
2788 -32727 SELECT_OOS11
4269 -30987 SELECT_OOS12
1665 -30987 SELECT_OOS11
2433 -30987 SELECT_OOS11
4272 -30984 SELECT11
2436 -30984 SELECT11
2793 -32708 SELECT11

But I only it want it to return 1 record for each ProductID like so:

AppItemId ProductID PartNum Relist AppUserId
2786 -32730 SELECT_OOS11
2787 -32729 SELECT12
2788 -32727 SELECT_OOS11
4269 -30987 SELECT_OOS12
4272 -30984 SELECT11
2793 -32708 SELECT11

ProductID is the primary key for the Products table, and a product can be in the ProductsToRelist table many times but each row would have a unique AppItemId. I know that I need to use Distinct or a different kind of join, but I'm not sure which. How would you suggest to do this?

Thanks

View Replies !