So how do i solve this sql statment
Select distinct colour from wwwstocktbl where colour <> ' ' order by colour
The stament works as a statment but when run in asp i get an error as theres no primary key, but if i add the primary key then the select distinct fails
Select distinct ProductCode, Colour from wwwstocktbl where colour <> ' ' order by colour
Hi,
I have a page for saving a "home" data. As the page unloads I will
redirect the user to the page on which he can add the photos to this
"home".
Well I structured my photo adding page with first requesting the "homeID" which is an auto Integer (PK).
Because it's renaming the uploaded photos with the homeID and _01, _02...
Because of that i need to send the user to the photo adding page with "response.redirect......aspx?homeID=.....
homeID= ???
But I don't know how to get the homeID value, that the SQL Server automaticly selects for the data. As it is just saving it...
Thanks a lot.
I have few chart based reports where I am showing different costs in series. I want to fix the colour of each type of cost so that I can maintain the same colour for each cost in different reports.
Is it possible?? If yes, then please let me know.
Thanks in advance!!!
Hi to everyone,
Is it possible (and if so, how should it be done?) to change some settings (i.e. the background colour) when user puts the mouse over a cell?
Thanks in advance,
Jesus M
Any change or bug in SSIS 2012 which doesn't allow you to change the font colour of an annotation. I am able to change the font type,size and style just not the colour. I have migrated packages from 2008R2 with colour and when placed into 2012 they show as black although when I check the setting they still indicate they are set to the particular colour.
I am running SSIS 2012 version 11.0.5058.0 in Visual Studion 2010 shell Version 10.0.40219.1 SP1 with MS .NET Framework Version 4.5.51641 SP1.
I have a chart on a report that shows value in Ł on the Y Axis. The values can be both positive and negative. I would like to have the negative labels to show in red and the positive in black. What is the expression syntax to achieve this? i know it will be an IIF statement, but what is the syntax to show the Y Axis label as <0?
eg if i was doing this for a field it would be iif(Field!Fieldname.value <0, "RED","BLACK") but what would i replace the FIELD!Fieldname.vaue with so the expression would change the Y Axis label.
Thanks in advance for any help
Dave Munro
i want to apply a hatch pattern to the fill colour on my bar graph and it doesn't work. i select fill colour, then i select hatch colour, i preview and it doesn't show the hatch pattern.
i would use gradient only i use 2 colours the same (hence why i want to differ them in a way other then colour) and they show up the same in the legend - legends don't display gradients
Hi,
I have a matrix table with a subtotal column
I would like to make the background colour off this different to the rest of the matrix, how would i do this.
Also I would like to make some portions off the matrix results a different colour as well, how I can i acheive this
thanks
Is there any way I can set the background colour for an image in a table cell (or even for a non-embedded image for that matter)? I always seem to get a white background.
It is a GIF image file.
Thanks
I am creating a matrix in which row color changes alternately(1st row white second row blue and so on .) now i am calling this matrix as a sub report in other report and i had applied grouping on that row in which this MATRIX is called the problem i am facing is in first sub report 1st row is coming in white color while in second sub report 1st row is coming as blue color background  And so on...
Why so is happening as i want 1st row as white and second row as blue i all sub reports .
I have an issue in trying to format rows base on conditions. Below is a replication of the tables and the select statement.
CREATE TABLE #CompareVal
(CompareValID INT Not Null
, ValName NVARCHAR(75) Null
, Vehicle INT Null
, Driver INT Null
);
GO
[Code] ....
First issue, James and Jane does not have a driver available and that should show "No Driver available"I am to compare values in VehicleAvailable and DriverAvailable to the first row - (Group Value Standard row) so that when a value is less than the value in first row, it should be Gold, if equal to, Blue and if greater than then, Red.
The first row is to be Black. In other for me to be able to compare, I added columns like so:
SELECT #CompanyName
, [Description]
, ISNULL(CAST(VehicleAvalible AS NVARCHAR(30)),'N/A') AS VehicleAvalible
, ISNULL(CAST(DriverAvailable AS NVARCHAR(30)),'No Driver available') AS DriverAvailable
, 0 AS TotalVehicles
, 0 AS TotalDrivers
[Code] ...
And my expression for "VehicleAvailable" column is :
=Switch(Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value = Fields!TotalVehicles.Value, "Black"
, Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value < Fields!TotalVehicles.Value, "Black"
, Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value > Fields!TotalVehicles.Value, "Black"
[Code]....
This doesn't work as I am comparing integer against text value. How do I format to get result like the below image?
I was wondering whether it is possible to change the font color of a cell depending on the value of the cell.
For example I want to set a Variance value to Red if the value is less than zero.
Hi,
I am currently trying to figure out how to change the background colour of a cell when that row happens to be a subtotal
I have included a screen shot of my report design below:
(The link to the image is http://diagnostic.iipuk.co.uk/images/RS_Questions.JPG should the image not be displayed)
When the cell that is circled is on a subtotal row (i.e. is the subtotal of either the first subtotal (which I have called RegionTot) or the second subtotal (which I have called QCTotal) I want the background colour to change to black). This is so the user cannot see the subtotal for that column on the screen.
I have tried using the following expression in the background colour property for that cell (without success!):
=IIf(ReportItems!QCTotal.Value = "Total", "Black", "White")
The error that comes up states that report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.
I have searched help and haven€™t managed to find anything!
Any help would be greatly appreciated!
Thanks
Marek Kluczynski
Information Manager
Investors in People UK
I am new to ssrs super scripts. I want to display superscript as B with bold & red colour for one column data
o/p:
Name:
America B
India B
UAE B
Here B is super script & red color....
I want to change my text box text colour for following ;
MY data was:
column name: Â NAme
Unitedstatesofamerica
america state
Northamerica
southamerica
America should be bold and red colour.
I want to write expression to BOLD and change colour in ssrs for America.
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?
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 RelatedHello,
I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a tableCREATEd 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 bySELECTing COUNT(BranchID) FROM Proposals WHERE BranchID=@prmBranchID AND Proposal_Date=@prmDateand 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 and11.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.
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
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
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.
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
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
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.
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
What is the difference between distinct and group by please?
View 4 Replies View RelatedHi,
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?
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??
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')
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.
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
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
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