Embedded Select Distinct Query

Feb 18, 2015

This is part of a query I have. I want to display the diagnosis code attached to a patient. Sometimes there are more than one which is why I have tried distinct. I am getting the message 'subquery returned more than 1 value...'

If I use MIN or MAX, its giving me the min possible diagnosis code alphabetically or the max, not the one actually assigned to the patient which is what I want.

(SELECT DISTINCT (Diagnosis.DiagnosisCode) FROM Diagnosis
LEFT OUTER JOIN CourseDiagnosis ON CourseDiagnosis.DiagnosisSer = Diagnosis.DiagnosisSer
LEFT JOIN Course ON Course.CourseSer = CourseDiagnosis.CourseSer
JOIN Patient ON Patient.PatientSer = Course.PatientSer
WHERE Diagnosis.ObjectStatus = 'Active' AND
Diagnosis.Description not like 'ERROR%' AND
Diagnosis.DiagnosisTableName not in ('RTDS Dates')),

View 10 Replies


ADVERTISEMENT

Embedded Select Query?

Jul 20, 2005

What I'd like to do is use the result of one query as the input foranother. eg:INSERT INTO foo VALUES ((SELECT id FROM people WHERE name = "bar"),10,'foobar') WHEREid = 1;Problem is, MSSQL wants a scalar value. Is there a way around this?Can someone tell me the correct syntax for what I want to do, or is thissomething that will have to be done outside SQL?Err. Hope I've been clear. Thanks for any help you folks can give.

View 2 Replies View Related

Distinct In Select Query

Nov 9, 2006

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 4 Replies View Related

DISTINCT SELECT Query With More Than One Field

Feb 6, 2008

Hello, I want to do a DISTINCT  SELECT query with more than one field, for example a ID field with a Type field, as if both fields make the primary key, like (ID 1 ,Type 1) ,( ID 1, Type 2) and (ID 2, Type 1) is ok but not (ID 1, Type1) and (ID 1,Type 1) if its not possible to do a distinct with more than one then what other techniques are possible to get the duplicate data out.   the reason why I want to use distinct is that I can use that query to export that data to where both of these fields make the primary key.
Thanks in advance

View 5 Replies View Related

Query Help - Select Distinct Possibly?

Oct 26, 2006

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 3 Replies View Related

SELECT Distinct Query Problem

Dec 7, 2006

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 2 Replies View Related

Difficult Select Distinct Query

Jul 20, 2005

Hi,I have a table as followingaa Text1 aa, Join Bytes!, 15267aa Text1 aa, Join Bytes!, 16598aa Text1 aa, Join Bytes!, 17568aa Text2 aa, Join Bytes!, 25698aa Text3 aa, Join Bytes!, 12258I have to write a query as follows ...SELECT DISTINCT TOP 500 fldText, fldContact, fldItemidFROM tableWHERE fldCat = 10 AND CONTAINS (fldText, 'Text1')In the example you can see the table has rows in which text and contact ordouble but with different itemid's. Now my employer wants me to show only 1row when text and contact or the same. He doesn't mind which itemid I show.... but I have to show one.I've an idea of how to do this using a cursor and a temporary table but Iguess that will be fatal for the performance because then I have to loopthrough all selected rows, check each row with all other rows and store theprimary key in the temporary table if dedected it isn't double. AfterwardsI can execute ... SELECT ... FROM TABLE where primary key in (selecttemp_primarykey from #temptable).I hoped I could do everything in 1 "easy" SELECT but I should not know how?Any ideas are much appreciated.Thanks a lot.Perre Van Wilrijk.

View 1 Replies View Related

How To Use ORDER BY Clause In An SELECT DISTINCT Sql Query When AS SINGLECOLUMN Is Defined?

Mar 22, 2008

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 10 Replies View Related

Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?

Jul 6, 2007

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 2 Replies View Related

SQL Server 2012 :: Using Unions To Write Out Each Select Query As Distinct Line In Output

Aug 20, 2015

Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.

Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)

Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0

What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0

I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.

View 6 Replies View Related

Embedded Select Vs. UDF

Jul 8, 2004

Why is using an embedded SELECT statement faster than using an embedded UDF that has the same exact SQL code within?

Example (not syntax corrected)
Ex1:
SELECT myValue1, myValue2 FROM myTable WHERE aValue = (SELECT Value FROM someTable WHERE myIndex = 800)

is much faster than something like

Ex2:
SELECT myValue1, myValue2 FROM myTable WHERE aValue = (dbo.FN_myUDF(@vmyIndex))

Given that dbo.FN_myUDF has the same code as the embedded select in the first example.

TIA,

KB

View 4 Replies View Related

Embedded IF EXISTS(SELECT * ...) 1 ELSE 0

Jun 11, 2008

I have a Select statement that was working just fine:
string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName) SELECT Silo_ID,'" + Keyword + "', '" + strUsername + "' FROM SiloNames WHERE Silo_Name = '" + Uri + "'";
the PM now wants to capture the internal User, which I have done but I am having real problems with the syntax if the internal User exists:
 
string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName, IsInternal) SELECT Silo_ID,'" + Keyword + "', '" + strUserName + "', if EXISTS(SELECT * FROM InternalUsersList WHERE Alias = '" + arrResult[1] + "') 1 ELSE 0 FROM SiloNames WHERE Silo_Name = '" + Uri + "'";
I am completely lost here.
 
Thanks for any help

View 3 Replies View Related

How To Reference Embedded Select In WHERE Clause

Oct 22, 2013

I need to check the value of a column being pulled from an embedded select in the WHERE clause of a script but can't figure out how to do it. The script looks like this:

SELECT
LIST_ID,
NAME,
ADDRESS
(SELECT ANSWER FROM VALID_ANSWER WHERE VALID_ANSWER.LIST_ID = VIEW_LIST.LIST_ID) AS ANSWER

FROM VIEW_LIST

WHERE ANSWER = 'No'

The syntax above works in Oracle but in SQL Server I receive the following error: "Invalid column name 'OHIP'."

View 6 Replies View Related

Trying To Add A NON-DISTINCT Field To A DISTINCT Record Set In A Query.

Mar 12, 2007

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 2 Replies View Related

When Should Ssis Package Use Embedded Sql Query?

Jun 27, 2007

On another thread a poster suggested that you can experience performance gains in Ssis by not using stored procedures.



I asked if I could have some Microsoft articles that articulated this position, but received no response.



Does anyone have links to Microsoft supported articles articulating when/if it is appropriate from Ssis to Not exec Stored Procedures for data manipulation or data collection?

View 24 Replies View Related

SQL Select DISTINCT?

Oct 16, 2006

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 5 Replies View Related

SELECT Distinct Help

Apr 19, 2007

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 4 Replies View Related

Select Not Distinct?

Apr 8, 2008

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 3 Replies View Related

Please Help Me: SQL SELECT DISTINCT

May 31, 2008

 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 3 Replies View Related

Select Distinct

Jun 25, 2001

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 1 Replies View Related

Select Distinct

Feb 15, 2000

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 2 Replies View Related

Select Distinct

Aug 5, 2004

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 3 Replies View Related

Select Distinct Help?

Jan 19, 2005

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 3 Replies View Related

Select Distinct

Oct 26, 2006

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 13 Replies View Related

Using Distinct And * In Select

Mar 2, 2006

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 3 Replies View Related

Help With A Distinct Select?

May 10, 2006

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 1 Replies View Related

SELECT DISTINCT

Oct 16, 2006

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 8 Replies View Related

Select Distinct

May 16, 2007

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 2 Replies View Related

Select Distinct

Oct 16, 2007

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 6 Replies View Related

SELECT DISTINCT

Jul 20, 2005

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 2 Replies View Related

Select Distinct

Mar 4, 2008

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 7 Replies View Related

SELECT DISTINCT

Aug 31, 2007

Hi


Just a question

I have a query that selects profile data for members, if I don€™t do a select distinct it gives me a lot of correct values, (unique values) of members i.e. only one record per member, but every now and then I get duplicate values for one member, multiple times.

Why does this occure?

I know SELECT DISTINCT is there to remove duplicates, but without SELECT DISTINCT why would this €śmistake€? happen?

Any help would be greatly appreciated.

I.e.

2 | 3 | John | Slack | Philips |5
1 | 2 | Jason | Limrick | Jones | 3
1 | 2 | Jason | Limrick | Jones | 3
1 | 2 | Jason | Limrick | Jones | 3
1 | 2 | Jason | Limrick | Jones | 3
1 | 2 | Jason | Limrick | Jones | 3
2 | 3 | Jane | John | Parker |4

Why would it create duplicate records if the values are the same?

Kind Regards
Carel Greaves

View 8 Replies View Related

Select Distinct Help

Sep 28, 2006




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 1 Replies View Related







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