[Resolved] Select Using &"Distinct&"
Jun 11, 2008
I'm having problems selecting distinct records:
SELECT distinct(dbo.Item.CompanyItemId), dbo.Job.CompanyJobId AS JobID, dbo.Batch.ReportDate AS BatchDate,
dbo.JobNoteEvent.Note as Notes,dbo.Item.UnitOfMeasure AS UOM, dbo.SourceType.CompanySourceTypeId,
dbo.Product.CompanyProductId
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid
WHERE dbo.job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR'
ORDER BY dbo.Job.CompanyJobId, dbo.Item.CompanyItemId
Result:
040013505048 2008-04-13 NULLSYPRNULL
041013505048 2008-04-08 NULLSYPRNULL
0410135050482008-04-10 NULLSYPRNULL
0500135050482008-04-07 NULLTNPRNULL
0500135050482008-04-08 NULLTNPRNULL
0500135050482008-04-09 NULLTNPRNULL
0500135050482008-04-10 NULLTNPRNULL
0500135050482008-04-13 NULLTNPRNULL
0500135050482008-04-23 NULLTNPRNULL
0500135050482008-04-24 NULLTNPRNULL
0500135050482008-04-27 NULLTNPRNULL
0500135050482008-06-05 NULLTNPRNULL
0500135050482008-06-08 NULLTNPRNULL
My result should only be 3 records (column 1) values 04001, 04101 and 05001.
Thank you.
View 4 Replies
ADVERTISEMENT
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
Feb 1, 2008
I have a sp that selects from a table. The table has a MA record (received qty) type and a PR record (used qty) type.
Below is code that produces an extract and gives 2 records, one for MA (received qty) and one for PR (used qty).
SELECT dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code, dbo.Product.CompanyProductId as product_id, SUBSTRING(dbo.Job.CompanyJobId, 1,3) as plant_id, dbo.SourceType.CompanySourceTypeId,
CASE dbo.SourceType.CompanySourceTypeId
WHEN 'PR' then SUM(dbo.ProductionEvent.Quantity)
ELSE SUM(dbo.ProductionEvent.AlternateQuantity)
END
AS
tot_qty
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
WHERE dbo.SourceType.CompanySourceTypeId = 'PR'
and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
and dbo.Item.UnitOfMeasure = 'TN'
or dbo.SourceType.CompanySourceTypeId = 'MA'
and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
and dbo.Item.UnitOfMeasure = 'TN'
GROUP BY dbo.Batch.ReportDate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.SourceType.CompanySourceTypeId, dbo.Product.CompanyProductId
Tot Qty
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 MA372.25
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 PR357.25
Now I want to extract the same data into a temp table (that I have created) and when reading the same records I would like to update either a received qty field or a used qty field depending upon record type MA or PR). The query works but both qty fields are empty. Can anyone see what I am doing wrong?
SELECT dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code, dbo.Product.CompanyProductId as product_id, SUBSTRING(dbo.Job.CompanyJobId, 1,3) as plant_id, dbo.SourceType.CompanySourceTypeId,
qty_received =
CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then SUM(dbo.ProductionEvent.Quantity) ELSE 0 END,
qty_used =
CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN SUM(dbo.ProductionEvent.AlternateQuantity) ELSE 0 END,
qty_wasted =
CASE dbo.SourceType.CompanySourceTypeId
WHEN 'MA' then SUM(dbo.ProductionEvent.Quantity)
ELSE 0
END
-
CASE dbo.SourceType.CompanySourceTypeId
WHEN 'PR' THEN SUM(dbo.ProductionEvent.AlternateQuantity)
ELSE 0
END
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
WHERE dbo.SourceType.CompanySourceTypeId = 'PR'
and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
and dbo.Item.UnitOfMeasure = 'TN'
or dbo.SourceType.CompanySourceTypeId = 'MA'
and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
and dbo.Item.UnitOfMeasure = 'TN'
GROUP BY dbo.Batch.ReportDate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.SourceType.CompanySourceTypeId, dbo.Product.CompanyProductId
QTY QTY QTY
REC USED WASTE
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 MA.00 .00 .00
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 PR.00 .00 .00
View 9 Replies
View Related
Jun 3, 2008
I have a sql line that selects records:
select * from prodstatistics order by shift_started desc
I do not need to read the entire table and want to limit the read to the first 500 records. How would I embedd that in my current statement?
Thank you.
View 3 Replies
View Related
Sep 16, 2007
RESOLVED RESOLVED RESOLVED
DOH! When I rebuilt the OS, I changed the data partition ID's, and didn't remember to change them in the connetion strings
Not great, but better than continuing to bang head against wall.
Would like to say to MS appreciators that the VS2005 reinstall actually went quite well, considering.
===========================================================================
I built an app, works fine. Had the development environment working fine (VS2005 (SP0) and SQL Server Express). It had been stable for months, no problem.
Then I had to reinstall XP Pro...(finally threw too many conflicting programs into it, I guess...)
I managed to get VS2005 reinstalled ok. However, when I opened and compiled my existing app in VS2005, using Cassini, I got the dreaded error:
An attempt to attach an auto-named database for file <file string> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I also get this error for the compiled copy of the app on the same machine, running on IIS. (Where it also previously worked fine).
The Connection strings are all of the form
connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=(specific file string).MDF;Integrated Security=True;User Instance=True"
I appreciate that there are zillions of posts on this issue. I'm hoping that someone might know why this should happen in this particular case.
The thing that's different about this case is that the app was working fine; I have the app deployed on a demo machine and also on a remote server as well as on the development machine.
The only thing that changed is that there was this fresh install of SQL Server Express.
I tried using >> sp_configure user instances enabled','1';RECONFIGURE <<< in SSMSEE, which took, but didn't help.
So something is different in SSE. Since the app is stable everywhere else, I REALLY don't want to go through and adjust the connection strings...MUCH better to figure out what SSE needs to be happy with the existing connection strings...as it was before.
Any suggestions would be appreciated.
Thanks!
EDIT----TRIED SOME THINGS:
Changed folder and file permissions to allow ASPNET full access to the .mdf's and log files (though never had to do this before on the development machine.) No change...
Can see the table contents within the .mdf through SSMSE, and also through Server Explorer in VS2005.
So because the problem happens with both IIS and Cassini, I'm assuming it's got to be an issue between ASPNET and SSE.
EDIT --- TRIED SOME MORE THINGS:
I dimly remember that when I had this problem before, it was that the error message was too "dumb". It wasn't my file that had the permissions problems, it was that the SSE System Databases had to have permissions with respect to ASPNET.
I tried to set that up but I'm a complete newb on this area, so I don't know if I did it right. In SSMSE I created a Login for MyComputer/ASPNET, and gave it all permissions and roles, and then made it an owner for the four system databases...but no joy.
Any pointers on this last piece would be helpful.
EDIT - MORE INFO
The error details
[SqlException (0x80131904): An attempt to attach an auto-named database for file <filestring>.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Dec 14, 2007
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 8 Replies
View Related
May 21, 2008
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 7 Replies
View Related
Feb 5, 2007
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 4 Replies
View Related
Oct 2, 2007
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 2 Replies
View Related
Oct 10, 2007
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 1 Replies
View Related
May 6, 2008
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 1 Replies
View Related
Mar 19, 2004
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 4 Replies
View Related
May 12, 2004
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 2 Replies
View Related
Mar 29, 2006
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 2 Replies
View Related
Jun 5, 2006
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 4 Replies
View Related
