|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SELECT DISTINCTthe same item in dropdown lists? I use alot of CASE and DATEADD functions but I do not see anything worng. SELECT DISTINCT CATEGORY, DATEADD(m, - 6, DISCONTINUEDDATE) AS SUNSET, DATEPART(YEAR, DISCONTINUEDDATE) AS Year, DATEPART(QUARTER, DISCONTINUEDDATE) AS Quarter, ID, TYPE, PRODUCTID, PRODUCT, VENDORID, VENDOR, VERSIONID, VERSION, DESCRIPTION, PLATFORM, GLOBALRECOMMENDATION, LOCALRECOMMENDATION, SUPPORTED, SUPPORTGROUP, ESTIMATEDCOST, LICENSESCHEME, OBTAINCHANNEL, BUDGETCODE, PRODUCTPUBLISH, VERSIONPUBLISH, DISCONTINUEDDATE, GTPM, COMPOUND, CREATEDDATE, REQUESTSTATUS, PRODUCTLASTUPDATEDATE, VERSIONLASTUPDATEDATE, PROJECT_COUNT, APP_COUNT, RELEASEDATE, Details DiscontinuedDate, DATEDIFF(quarter,getdate(),DiscontinuedDate), DATEDIFF(quarter,getdate(),DiscontinuedDate), CASE WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) < 1 THEN 'Discontinued' WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) = 1 THEN 'warning' WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) = 2 THEN 'caution' ELSE 'go' END as 'Current Quarter', CASE WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) < 2 THEN 'stop' WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) = 2 THEN 'warning' WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) = 3 THEN 'caution' ELSE 'go' END as 'Next Quarter', CASE WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) < 3 THEN 'stop' WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) = 3 THEN 'warning' WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) = 4 THEN 'caution' I "REMOVED SOME OF THE ABOVE REPEATING CODE TO CUT DOWN ON LENGTH" FROM EOL WHERE DiscontinuedDate > getdate() ORDER BY DiscontinuedDate The database is 1 int (primary key aut-inc) 4 varchar and 4 datetime fields pretty simple. TicToc wrote:
Show quote > What could cause a SELECT DISTINCT to still show multipule items of DISTINCT applies to ALL the columns in the SELECT list. Are you sure> the same item in dropdown lists? > > I use alot of CASE and DATEADD functions but I do not see anything > worng. > > SELECT DISTINCT CATEGORY, DATEADD(m, - 6, DISCONTINUEDDATE) AS SUNSET, > DATEPART(YEAR, DISCONTINUEDDATE) AS Year, DATEPART(QUARTER, > DISCONTINUEDDATE) AS Quarter, ID, TYPE, PRODUCTID, PRODUCT, VENDORID, > VENDOR, VERSIONID, VERSION, DESCRIPTION, PLATFORM, > GLOBALRECOMMENDATION, LOCALRECOMMENDATION, SUPPORTED, SUPPORTGROUP, > ESTIMATEDCOST, LICENSESCHEME, OBTAINCHANNEL, BUDGETCODE, > PRODUCTPUBLISH, VERSIONPUBLISH, DISCONTINUEDDATE, GTPM, COMPOUND, > CREATEDDATE, REQUESTSTATUS, PRODUCTLASTUPDATEDATE, > VERSIONLASTUPDATEDATE, PROJECT_COUNT, APP_COUNT, RELEASEDATE, Details > DiscontinuedDate, DATEDIFF(quarter,getdate(),DiscontinuedDate), > DATEDIFF(quarter,getdate(),DiscontinuedDate), CASE WHEN > DATEDIFF(quarter,getdate(),DiscontinuedDate) < 1 THEN 'Discontinued' > WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) = 1 THEN 'warning' > WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) = 2 THEN 'caution' > ELSE 'go' END as 'Current Quarter', CASE WHEN > DATEDIFF(quarter,getdate(),DiscontinuedDate) < 2 THEN 'stop' WHEN > DATEDIFF(quarter,getdate(),DiscontinuedDate) = 2 THEN 'warning' WHEN > DATEDIFF(quarter,getdate(),DiscontinuedDate) = 3 THEN 'caution' ELSE > 'go' END as 'Next Quarter', CASE WHEN > DATEDIFF(quarter,getdate(),DiscontinuedDate) < 3 THEN 'stop' WHEN > DATEDIFF(quarter,getdate(),DiscontinuedDate) = 3 THEN 'warning' WHEN > DATEDIFF(quarter,getdate(),DiscontinuedDate) = 4 THEN 'caution' I > "REMOVED SOME OF THE ABOVE REPEATING CODE TO CUT DOWN ON LENGTH" FROM > EOL > WHERE DiscontinuedDate > getdate() > ORDER BY DiscontinuedDate > > The database is 1 int (primary key aut-inc) 4 varchar and 4 datetime fields > pretty simple. there are duplicate rows, not just duplicates in some column(s)? You have datetimes in there. Are you sure you are displaying all of the date and the time? Just a millisecond difference is enough to make the column distinct. If you still think you have a problem then please post enough code to reproduce it: A CREATE TABLE statement and INSERT statement(s) for some data. -- David Portas SQL Server MVP -- I am using 3 dropdownselects in ASP.NET VB here is one of them
Sub Ddl2_ItemSelected( sender As Object, E As EventArgs ) Dim sql As String SQL = "SELECT DISTINCT CATEGORY, DATEADD(m, - 6, DISCONTINUEDDATE) AS SUNSET, DATEPART(YEAR, DISCONTINUEDDATE) AS Year, DATEPART(QUARTER, DISCONTINUEDDATE) AS Quarter, ID, TYPE, PRODUCTID, PRODUCT, VENDORID, VENDOR, VERSIONID, VERSION, DESCRIPTION, PLATFORM, GLOBALRECOMMENDATION, LOCALRECOMMENDATION, SUPPORTED, SUPPORTGROUP, ESTIMATEDCOST, LICENSESCHEME, OBTAINCHANNEL, BUDGETCODE, PRODUCTPUBLISH, VERSIONPUBLISH, DISCONTINUEDDATE, GTPM, COMPOUND, CREATEDDATE, REQUESTSTATUS, PRODUCTLASTUPDATEDATE, VERSIONLASTUPDATEDATE, PROJECT_COUNT, APP_COUNT, RELEASEDATE, Details DiscontinuedDate, DATEDIFF(quarter,getdate(),DiscontinuedDate), DATEDIFF(quarter,getdate(),DiscontinuedDate), CASE WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) < 1 THEN " + Chr(39) + "Discontinued" + Chr(39) + " WHEN " END as " + Chr(39) + "Next Quarter" + Chr(39) + ", CASE WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) < 3 THEN " + Chr(39) + "stop" + Chr(39) + " WHEN END as " + Chr(39) + "Next 8 Quarters" + Chr(39) + " FROM EOL where Category= '" & ddl2.SelectedValue & "' and Vendor='" & ddl1.SelectedValue & "'" I will try the GROUP BY as well. Good idea. Thanks Show quote "David Portas" wrote: > TicToc wrote: > > What could cause a SELECT DISTINCT to still show multipule items of > > the same item in dropdown lists? > > > > I use alot of CASE and DATEADD functions but I do not see anything > > worng. > > > > SELECT DISTINCT CATEGORY, DATEADD(m, - 6, DISCONTINUEDDATE) AS SUNSET, > > DATEPART(YEAR, DISCONTINUEDDATE) AS Year, DATEPART(QUARTER, > > DISCONTINUEDDATE) AS Quarter, ID, TYPE, PRODUCTID, PRODUCT, VENDORID, > > VENDOR, VERSIONID, VERSION, DESCRIPTION, PLATFORM, > > GLOBALRECOMMENDATION, LOCALRECOMMENDATION, SUPPORTED, SUPPORTGROUP, > > ESTIMATEDCOST, LICENSESCHEME, OBTAINCHANNEL, BUDGETCODE, > > PRODUCTPUBLISH, VERSIONPUBLISH, DISCONTINUEDDATE, GTPM, COMPOUND, > > CREATEDDATE, REQUESTSTATUS, PRODUCTLASTUPDATEDATE, > > VERSIONLASTUPDATEDATE, PROJECT_COUNT, APP_COUNT, RELEASEDATE, Details > > DiscontinuedDate, DATEDIFF(quarter,getdate(),DiscontinuedDate), > > DATEDIFF(quarter,getdate(),DiscontinuedDate), CASE WHEN > > DATEDIFF(quarter,getdate(),DiscontinuedDate) < 1 THEN 'Discontinued' > > WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) = 1 THEN 'warning' > > WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) = 2 THEN 'caution' > > ELSE 'go' END as 'Current Quarter', CASE WHEN > > DATEDIFF(quarter,getdate(),DiscontinuedDate) < 2 THEN 'stop' WHEN > > DATEDIFF(quarter,getdate(),DiscontinuedDate) = 2 THEN 'warning' WHEN > > DATEDIFF(quarter,getdate(),DiscontinuedDate) = 3 THEN 'caution' ELSE > > 'go' END as 'Next Quarter', CASE WHEN > > DATEDIFF(quarter,getdate(),DiscontinuedDate) < 3 THEN 'stop' WHEN > > DATEDIFF(quarter,getdate(),DiscontinuedDate) = 3 THEN 'warning' WHEN > > DATEDIFF(quarter,getdate(),DiscontinuedDate) = 4 THEN 'caution' I > > "REMOVED SOME OF THE ABOVE REPEATING CODE TO CUT DOWN ON LENGTH" FROM > > EOL > > WHERE DiscontinuedDate > getdate() > > ORDER BY DiscontinuedDate > > > > The database is 1 int (primary key aut-inc) 4 varchar and 4 datetime fields > > pretty simple. > > DISTINCT applies to ALL the columns in the SELECT list. Are you sure > there are duplicate rows, not just duplicates in some column(s)? You > have datetimes in there. Are you sure you are displaying all of the > date and the time? Just a millisecond difference is enough to make the > column distinct. > > If you still think you have a problem then please post enough code to > reproduce it: A CREATE TABLE statement and INSERT statement(s) for some > data. > > -- > David Portas > SQL Server MVP > -- > >
Show quote
"TicToc" <Tic***@discussions.microsoft.com> wrote in message "SELECT DISTINCT" usually means "I have no idea what my query is actually news:EDEFC235-A14F-497C-97FF-828D429BEE5C@microsoft.com... > What could cause a SELECT DISTINCT to still show multipule items of > the same item in dropdown lists? > > I use alot of CASE and DATEADD functions but I do not see anything > worng. > > SELECT DISTINCT CATEGORY, DATEADD(m, - 6, DISCONTINUEDDATE) AS SUNSET, > DATEPART(YEAR, DISCONTINUEDDATE) AS Year, DATEPART(QUARTER, > DISCONTINUEDDATE) AS Quarter, ID, TYPE, PRODUCTID, PRODUCT, VENDORID, > VENDOR, VERSIONID, VERSION, DESCRIPTION, PLATFORM, > GLOBALRECOMMENDATION, LOCALRECOMMENDATION, SUPPORTED, SUPPORTGROUP, > ESTIMATEDCOST, LICENSESCHEME, OBTAINCHANNEL, BUDGETCODE, > PRODUCTPUBLISH, VERSIONPUBLISH, DISCONTINUEDDATE, GTPM, COMPOUND, > CREATEDDATE, REQUESTSTATUS, PRODUCTLASTUPDATEDATE, > VERSIONLASTUPDATEDATE, PROJECT_COUNT, APP_COUNT, RELEASEDATE, Details > DiscontinuedDate, DATEDIFF(quarter,getdate(),DiscontinuedDate), > DATEDIFF(quarter,getdate(),DiscontinuedDate), CASE WHEN > DATEDIFF(quarter,getdate(),DiscontinuedDate) < 1 THEN 'Discontinued' > WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) = 1 THEN 'warning' > WHEN DATEDIFF(quarter,getdate(),DiscontinuedDate) = 2 THEN 'caution' > ELSE 'go' END as 'Current Quarter', CASE WHEN > DATEDIFF(quarter,getdate(),DiscontinuedDate) < 2 THEN 'stop' WHEN > DATEDIFF(quarter,getdate(),DiscontinuedDate) = 2 THEN 'warning' WHEN > DATEDIFF(quarter,getdate(),DiscontinuedDate) = 3 THEN 'caution' ELSE > 'go' END as 'Next Quarter', CASE WHEN > DATEDIFF(quarter,getdate(),DiscontinuedDate) < 3 THEN 'stop' WHEN > DATEDIFF(quarter,getdate(),DiscontinuedDate) = 3 THEN 'warning' WHEN > DATEDIFF(quarter,getdate(),DiscontinuedDate) = 4 THEN 'caution' I > "REMOVED SOME OF THE ABOVE REPEATING CODE TO CUT DOWN ON LENGTH" FROM > EOL > WHERE DiscontinuedDate > getdate() > ORDER BY DiscontinuedDate > > The database is 1 int (primary key aut-inc) 4 varchar and 4 datetime > fields > pretty simple. doing". Replace it with GROUP BY to get a handle on what rows your query returns. David  |
|||||||||||||||||||||||
Other interesting topics