DISTINCT To ShortDateString, Not DISTINCT To The DateTime; How?

May 8, 2006

Hello,

I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a table

CREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )

This app also calculates the total number of proposals coming from a specific branch in a given date by
SELECTing COUNT(BranchID) FROM Proposals WHERE BranchID=@prmBranchID AND Proposal_Date=@prmDate
and prints them in a table (my target table).

This target table has as many rows as the result of  the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"
and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the
"SELECT DISTINCT BranchID FROM Proposals".
This target table converts the DateTime values ToShortDateString so
that we are able to see comfortably which branch office has sent how many proposals in a given day.

So far so good, and everything works fine except one thing:

Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and
11.11.2005 10:45:30) cause some trouble in the target table,  where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)

What I need to do is to get a result like (in a neat fashion :)

"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"

So, how to do it in a suitable way?

Thanks in advance.

View 4 Replies


ADVERTISEMENT

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

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

Looking To Collect Distinct Date Part Out Of Datetime Field

Mar 11, 2006

from this, circdate being a datetime field:SQLQuery = "select distinct circdate from circdata order by circdate"I need the distinct date portion excluding the time part.this has come about when I discoveredI am inserting and updating some datetime values with the same value,but for some reason, the values are always off by a few seconds. I seta variable called SetNow assigned to NOW and then set the datetimefields to this SetNow variable. Then when I collect the distinct datetime I am assuming they will have the same values recorded incircdate, but no, they are off by several seconds. Makes no sense to meat all. I tried renaming the variable several times but it makes nodifference at all.any help appreciated, thanks.

View 5 Replies View Related

How To Show Distinct Rows Of The Column Of The Dataset And Number Of Distinct Rows Of That Column

Mar 29, 2007

suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"

I want's some thing like

field1 rowcount
aaa 5
bbb 6

View 1 Replies View Related

Distinct Values Among Distinct Column Values

Jan 9, 2015

Okay, I've been working on this for a couple of hours with no success. I'm trying to find the number of telephone numbers that are associated with multiple students at different school sites. I've created a temp table that lists all phone numbers that are associated with more than one student. I'm now trying to query that table and count the number of telephone numbers that are associated with more than one site. Essentially, I'm looking for parent/guardians that have students at different sites.

Here's an example of what I'm hoping to accomplish:

*In this example, I'm just trying to get a count of the different/distinct school sites associated with each number. If I can, at the same time, limit it to a count of > 1 (essentially excluding parents with students at the same site), even better :)

===================================
Temp table
===================================
SCHOOL | STU_ID | STU_PHONE
101 | 12345 | 111-222-3333
101 | 23456 | 111-222-3333
102 | 34567 | 111-222-3333
101 | 45678 | 999-888-7777
101 | 56789 | 999-888-7777
101 | 67890 | 555-555-5555
102 | 78901 | 555-555-5555
103 | 89012 | 555-555-5555

==================================
Wanted query results
==================================
STU_PHONE | #Students | UNIQUE_SCHOOLS
111-222-3333 | 3 | 2
999-888-7777 | 2 | 1
555-555-5555 | 3 | 3

View 1 Replies View Related

Not Too Many Distinct..

Sep 27, 2007

If I know this SELECT will get me unique username to configname records:Select DISTINCT configname, username FROM EtechModelRequests

JOIN CC_host.dbo.usr_smc ON
username = user_id

JOIN Webservices.dbo.PartNumberPricingImport ON
PartNumber = configname

Where RequestDateTime > '9/26/2007' And country = 'US' And interfacename Like '%download%' And result = 0  
 How do I show the other fields I need? The field I need is List Price but I don't want to DISTINCT on it too.

View 2 Replies View Related

Distinct And Top

Mar 31, 2008

Hi I have two tables linked by MemberID and I'll like to produce a list from the two tables but also want to specify one table to only use the Top 50 distinct records
 
Table 1 (top 50 Distinct)
Memberid (distinct)PictureDateTakenGalleryID
Order By DateTaken
Table 2
MemberidFirstNameLastNamePrivatelist
MemberidPictureFirstNameLastNameGalleryIDPrivate

View 6 Replies View Related

Distinct

Apr 2, 2004

I have a query and it is bringing up multiples of the same data
what i would like to know is if there is a way to use something like Distinct that i can use as a clause
such as

Select *
from Table
Where Distinct ColumnName

I know that distinct doesnt work in this situation
I would like to know if there is a command to do this or a way to fix it
if i use this

select distinct columnName,columnName2
from table

it returns the rows where columnName and ColumnName2 both are not the same
My purpose is that i need to select more than one column but i would like non duplicates based on ONE column name

thanks for any help

View 20 Replies View Related

Using DISTINCT

May 31, 2002

I have 6 fields in my table and I want to display only distinct values from one of those columns. However, I also need to use the fields from the remaining 5 columns in my asp page. For example,

Columns:
CompID
CompanyName
Ticker
Industry
CEOName
MarketCap

In one table on the page, I want to display only unique entries for 'CompanyName' which I've been doing with this statement:

SqlText = "SELECT DISTINCT CompanyName FROM [tablename] WHERE UserID=" & intUserID & ""

However, I also need to use the other values associated 'CompanyName' in my asp page after opening my recordset. I need to display <%=Rs("CompID"%>, <%=Rs("Ticker"%>, <%=Rs("Industry"%> etc. while maintaining the DISTINCT portion of my statement.

Thanks again.

View 1 Replies View Related

Using Distinct

Sep 12, 2006

I have 3 records in a db..

Table
id
file_name
category

Records
1
bob.jpg
male

2
rob.jpg
male

3
pam.jpg
female

I am trying to grab the distinct category so only 'male and female' is output, but i am also needing to grab the id, file_name to use later on the page as well.

When i try

select distinct category, id, file_name
from tbl_pictures

it outputs all the records since there is no exact match in all 3 fields,

but i am wanting this to happen

select distinct category
from tbl_pictures

but i am still needing to grab the other two fields because i need to use them in the next part of the page

how can i go about doing this

View 1 Replies View Related

Distinct

Apr 22, 2008

What is the difference between distinct and group by please?

View 4 Replies View Related

DISTINCT... Help :)

Apr 24, 2008

Hi,
I have data in several tables and Im having trouble filtering the data.

This is statement that Im executing:
1. SELECT DISTINCT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills

This is the statement that gives me the results I want:
2. SELECT DISTINCT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName

The problem with this is I need to have Persons.Skills in the statement and DISTINCT doesnt filter the data the way I need it to because the data in Persons.Skills is different which results in I get duplicated results.

Is it possible to do something like
SELECT DISTINCT column1,..,column5,(SELECT Persons.Skills)

by this I mean to apply distinct to some of the columns in the SELECT statement?

View 6 Replies View Related

Distinct Value

May 20, 2008

I require only the distinct objects that were depend on my stored procedures

i tried for sp_depends test_sp
for this
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolayout_slno
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolayout_type
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolevel1
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolayout_operator
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonorlevel1desc
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonorlevel2desc
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenoyesguid
dbo.abb_account_budget_dtluser tablenonocompany_code
dbo.abb_account_budget_dtluser tablenonofb_id
dbo.abb_account_budget_dtluser tablenonofin_year_code
dbo.abb_account_budget_dtluser tablenonofin_period_code
dbo.abb_account_budget_dtluser tablenonoaccount_code

being dbo.abb_account_budget_dtl were repeated more than once.i need only the distinct objects..any other system procedure is there??

View 1 Replies View Related

DISTINCT

Jun 3, 2008

I'm trying to find the Distinct for CODEID but it's still returning duplicate data. What is the best way to write this query so that the result I get are Distinct Codeid?

SELECT DISTINCT dbo.AgreementDurationCode.CodeID, dbo.Item.PartNumber, dbo.ItemShadow.AgreementDurationCode, dbo.AgreementDurationCode.CodeCategory,
dbo.AgreementDurationCode.CodeCategoryID, dbo.AgreementDurationCode.Code, dbo.AgreementDurationCode.CodeName,
dbo.AgreementDurationCode.CodeAbbreviation, dbo.AgreementDurationCode.CodeShortAbbreviation, dbo.Item.ItemName,
dbo.AgreementDurationCode.CodeMaxcimAbbreviation, dbo.ItemShadow.ItemStatusCode, dbo.ItemShadow.LicenseTypeCode
FROM dbo.Item INNER JOIN
dbo.ItemShadow ON dbo.Item.ItemID = dbo.ItemShadow.ItemID INNER JOIN
dbo.AgreementDurationCode ON dbo.Item.AgreementDurationCodeID = dbo.AgreementDurationCode.CodeID
WHERE (dbo.ItemShadow.LicenseTypeCode IN ('SEL', 'OLP', 'OLV')) AND (dbo.ItemShadow.ItemStatusCode = 'COM')

View 3 Replies View Related

How To Get Distinct ID With Same Value

Apr 18, 2014

I have data like this

course_id Session
--------- --------
1234 8.00 - 10.00
4567 8.00 - 10.00

From the above table i want the result to be like this

course_id1 course_id2 session
---------- ---------- -------
1234 4567 8.00 - 10.00

Looking for query to get the above result format.

View 3 Replies View Related

DISTINCT

Mar 13, 2007

Hi All,

I am using SQL Server 2000. I have 3 fields in my table. I want to do distinct on one field but also want to show the remaining two fields in the result. How can I do that?

Thanks

-G

View 6 Replies View Related

Using DISTINCT

Apr 6, 2007

Is there some way to use the distinct keyword so that it applies ONLY to a subset of the items in the select list???

For example, suppose I want to
select col_1, col_2, col_3, col_4

but I do not want distinct applied against all 4 items...
Maybe I want all 4 items in the selection list,
but I want distinct to use only col_3 as its filtration criteria...

I know the syntax shown below is not valid, but I am showing it anyway because I am hoping it will illustrate what I am looking for...

Is there a VALID syntax that is something like this???

select col_1, col_2, (distinct col_3), col_4
or
select col_1, col_2, distinct (col_3), col_4

View 5 Replies View Related

Using Distinct

May 22, 2007

I am trying to pull information out of a table using distinct, but instead of just pulling a certain column I want to pull multiple columns in a row?

However when I use the command below I only get the "workitem_number" column available, where there are approx. 4 other columns that I need (workitem_title, workitem_comment, ETC) When I add the additional columns (after the distinct statement) it doesn't work due to the type of data.

"SELECT DISTINCT workitem_number from workitem_cost_view where assigned_to_worker_nt_id = '" & Request.QueryString("Name") & "' AND workitem_start_on = '" & Request.QueryString("schDate") & "' order by workitem_number"

Is there any way to pull multiple columns, based only on distinct for one column?

I hope I'm explaining this correctly.

Thanks.

Rwj6001

View 13 Replies View Related

How To Have Distinct Value?

Sep 1, 2007

Hi

I have table 'TblProd' and columns ID, categories, make, description, model. I am using the below procedure


create procedure getsubcategories
@category nvarchar (32)
As
Select ID, make, model from TblProd where category = @category
GO

My question how do I get distinct 'make' and other respective columns.

advance thanks

View 11 Replies View Related

Distinct

Jan 28, 2008

Hi Guys,,
want I need your help

I have the ff records:
Customer
ID CustNo Name Type
10 1 Madonna R
10 2 Madonna R


I used the ff query

select distinct(ID), CustNo, name,type
from Customer
where Type = 'R'
order by Name
but the result is wrong.

I want this result:
10 2 Madonna R

View 2 Replies View Related

SQL Distinct Help PLS

Mar 4, 2008

Hello - I am having trouble with my sql statement:

SELECT * FROM (SELECT DISTINCT rfs_id FROM tbl_comment) DT INNER JOIN summary_rfs t1 ON DT.rfs_id = t1.rfs_id INNER JOIN tbl_callStatus as t2 on t1.callstatus_id=t2.callstatus_id INNER JOIN tbl_user as t3 on t1.requestor=t3.user_name INNER JOIN tbl_user as t4 on t1.assigned_to=t4.user_name WHERE date_opened BETWEEN '1/1/2008' AND '1/1/2009' AND ASSIGNED_TO='Elaine Tran' ORDER BY priority_id ASC

this runs fine - but I want to retrieve data from tbl_comment how can I do this to display info from tbl_comment but not see the duplicates - can I flatten or merge the data?

View 1 Replies View Related

Get Only Max Of Each Distinct?

Nov 7, 2007

Hello,I have a tableItemID Version12 1.012 1.112 2.013 2.013 1.014 1.015 1.015 5.015 2.1How do I write a Select query to get me all distinct item IDs, whichmare of the latest version?Like this:ItemID Version12 2.013 2.014 1.015 2.1Any help would be appreciated.Thanks

View 2 Replies View Related

SUm Distinct

Jul 9, 2006

Hi people,

I love reporting services 2005 BUT have struck a major limitation!

Basically I need a sum distinct function. I have various duplicate details lines and just need to sum the unique values. Anyway this is not possible and a number of people a stuck with this. Yes you can write another sql statement using DISTINCT but then how can you easily integrate that into a table with scope? you can't!

Anyway has anyone been able to achieve this nicely in reporting services? I was thinking of calling a distinct SQL statement from an Expression in a text box on a header field and passing another text box as a parameter to get around this limitation. Is this possible?

Here is a link with similiar problem

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=240606&SiteID=1

Cheers
Damien

View 17 Replies View Related

Distinct Issue

Oct 8, 2006

HiI wanna write a proc that returns Distinct CustomerID's in a table and returns result in output parameterWhen i try this, i get error - incorrect syntax near distinct. Any ideas??ALTER PROCEDURE proc_Report_CountCustomers_Sept(  @CustCount int OUTPUT)ASSET NOCOUNT ON select  @CustCount = distinct(CustomerID)from   OrdersWhere   OrderDate > '2006-09-01' and OrderDate < '2006-10-01'

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

Distinct Problems.

Dec 28, 2006

OK heres what I have so far:
SELECT     TOP (100) PERCENT dbo.EVENTS.EVENTIME, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC,                       dbo.EVENTS.DEVID, CASE WHEN (dbo.EVENTS.DEVID = '23' OR                      dbo.EVENTS.DEVID = '24' OR                      dbo.EVENTS.DEVID = '25' OR                      dbo.EVENTS.DEVID = '26') THEN 'OUT' ELSE 'IN' END AS STATUSFROM         dbo.READER INNER JOIN                      dbo.EVENTS ON dbo.READER.READERID = dbo.EVENTS.DEVID INNER JOIN                      dbo.UDFEMP INNER JOIN                      dbo.EMP ON dbo.UDFEMP.ID = dbo.EMP.ID ON dbo.EVENTS.EMPID = dbo.EMP.IDWHERE     (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) = CONVERT(CHAR, GETDATE(), 101)) AND (dbo.EVENTS.EMPID <> 0)ORDER BY dbo.EVENTS.EVENTIME
Works great, however, I need to display only one instance of each employee and that instance should be the latest instance found.
So instead of several differant emplyees with several different "IN" and "OUT" times:EvenTime                        FirstName    Last Name    Ext       ReaderDesc       DevID    Status



12/28/2006 7:22:01 AM
  Rechie
         Michael
       75766
  SE Glass Door
  21
        IN



12/28/2006 7:10:01 AM
  Rechie
         Michael
       75766
  SE Glass Door
  21
        OUT



12/28/2006 7:01:01 AM
  Rechie
         Michael
       75766
  SE Glass Door
  21
        IN
I just want the latest record for any given employee regardless of wether its status is "IN" or "OUT":
EvenTime                        FirstName    Last Name    Ext       ReaderDesc       DevID    Status



12/28/2006 7:22:01 AM
  Rechie
         Michael
       75766
  SE Glass Door
  21
        IN
EVENTS.EMPID would be what I would want to be DISTINCT but I dont know how to use it in the code above and I dont know how Id specify DISTINCT based on latest time found.
Any help/direction would be greatly appreciated.
TIA,Stue

View 18 Replies View Related

Distinct Command ...

Jan 29, 2007

mytable
 





Cat1

Cat2

Cat3

Start price

End price


A

a

a

12

21


A

a

b

98

11


A

a

a

99

12


A

b

b

12

11


A

b

a

9

0


A

b

b

1

1






















 
 
My result should be like this …..
 
( Result of SQL Query? )
 





Cat1

Cat2

Cat3


A

a

a


A

a

b


A

b

a


A

b

b














 
What is the sql command to do like this?
 
 

View 4 Replies View Related

Distinct At Two Columns

Mar 8, 2007

I have this stored procedure:
ALTER PROCEDURE usp_My_Procedure  ( @Country varchar(5) )  AS SELECT DISTINCT City, Short FROM Table1 WHERE Country = @Country
RETURN
I want to select just one of each 'city' and 'short' in the database....But this is not working correct......Whats wrong?
Lets say that I have a table that looks something like this
City               Short
New York       NY
Los Angeles   LA
Lake Alice      LA
Los Angeles   LosAng

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

UPDATE DISTINCT - Help

May 20, 2007

Hi All,
I have a problem I just cant suss out. I have a table of products that are grouped together using a "GroupID" (this is used to have 1 product with many variations) , there is then a "isTop" bool  I need 1 product per group to be set to true or 1.I need some sort of UPDATE DISTINCT or UPDATE GROUP BY .. I don’t really know sub queries that well (understatement J) . but I tried unsuccessfullyUPDATE (SELECT  distinct GroupID, isTopFROM Product) as bob SET  bob.isTop = 1 And UPDATE DISTINCT GroupID  SET  = isTop 1
My table data looks soemthing like this:GroupID        isTop 15                  015                  015                  015                  026                  026                  027                  027                  029                  030                  031                  032                  0
But wants to look like this:
GroupID        isTop 15                  115                  015                  015                  026                  126                  027                  127                  029                  130                  131                  132                  1Hope that makes sense ... any help would be appreciated.
Lee

View 6 Replies View Related

Selecting Distinct

Jul 18, 2007

 Hi, This is quite an obvious problem, but for some reason I can't think through the solution. I have two columns, a datetime (datecreated) and an id (FK)  othertableid (id)what I would like to be able to do is select a list of id and datecreated, but only chose the latest row for each id.So I guess it's a bit like a distinct on the id column, but ensuring the date returned is the top date. Please help 

View 1 Replies View Related

Distinct Name And Email.

Aug 22, 2007

hey guys
.
you see how fenix.sn has a couple of entries BUT the same email and DIFFErENT BET IDS.
okay this is what i wanna do.
i wanna send an email to fenix.sn (just ONE email) saying you have won the following bets: xxx, xxx, xxx, xxx
then i wanna delete the entreis from the table.
 

View 5 Replies View Related







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