Get The SUM Of The TicketBillableTime And TicketProfit For Each DISTINCT TherapistID?
Dec 29, 2008
I have a table called Tickets where it has 3 columns in particular that I'm working with... TherapistID, TicketBillableTime, TicketProfit and I'm left joining the TherapistID with the Therapists table to get the associated TherapistName.What I need to do is get the SUM of the TicketBillableTime and TicketProfit for each DISTINCT TherapistID or TherapistName as it goes..
In VB6 I would have several different recordsets opened one after another to
A. Find all the unique project names within the table (and put them into a For/Next loop)
B. For each unique project name open a recordset to look up unique vendor names applicable to it (add For/Next)
C. For each unique projname, unique vend combination find unique exptype....for next loop....
D. For each unique projname, vendor name, exptype, date combination, sum up the dollar amounts...
The end effect is that you have a net dollar amount for each unique combination of project/vendor/expensetype/date.I dont like doing it like this because I have to keep hitting the database, I wanted to know if there was something I could do within the actual dataset which will be filled when I open the application with the values of the entire table..I got to ds.tables("Text").select.distinct( ....not sure how to proceed and everthing I've read online leads me into a complicated direction of creating new datatables...
I load a 47,000 line Text file into a Datagridview, not a problem at all.
I would like to get the Distinct values for Column1. I have been scratching my head on how to do this, I have not as of yet found an easy way to do this,
If I would connect to database I would run a query like this: "Select distinct Column1, column2 from tablename where somefield=somevalue order by column2". How can I run this query to a datatable? I know .select but that's not enough like dataset.datatable("name").select(filter,short). Still need distinct.
When the user has typed in some information I would like to output possible matches in a descending order, so if someone types in a full first name and a full surname, it should be listed above a result where just the surname matches.I've done something similar in SQL before which worked perfectly, but this time I'd like to do it in LINQ.
Firstname, Surname, City, Country as string variables. Dim DataEnum As IEnumerable(Of frmTelephone.clsPerson) = alPerson.OfType(Of frmTelephone.clsPerson)()
I can't quite figure out why this Linq Statement isn't working as i would expect:
[Code]....
I would assume that this would create a new collection of anonymous types, that would be distinct. Instead it creates a collection the size of the "ThisParentCollection" with duplicate "MyAnonymousType" in it (duplicate id's).
I have a very interesting LINQ question. I have a document, that I am trying to filter results on, but to filter, I am matching on a REGEX result from one element of the XML. I have the following, working LINQ to XML to get the individual data that I'm looking for.
I have a three column table which lists every person, every event they may have attended, and what percentage of the time they were there. I would really like to display this on one page with the person names on the side and the event names across the top.
Here' an example of what I have:
NAME EVENT %ATTENDANCE Smith Rock Climbing 50 Allen Rock Climbing 78
I am using strongly typed dataset and have many tables in that. The problem is now I want to filter data from GetData() function which has a query like select * from table_name
How can I filter a particular table and distinct values from it. Also if I try to filter it return all column but rest have null values except the one I asked, so I cannot assign it as a datasource to a datagrid or combobox. How can I do this..
I have a problem running a simple query: "select distinct payee from table". This query is set up in the dataset and is called GetAllPayee.My code goes something like:
Dim dtSources as new Datatable dtSources = Me.TblExpenseRecordsTableAdapter.GetAllPayee Upon running I get the error:Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.The problem is, I haven't set ANY constraints on either the new datatable, or the dataset table (tblExpenseRecords). i.e. there is no primary key etc.
Can someone please help me out with this problem?I have no problems running the query through the wizard where I can view the results successfully.
Is it possible to select distinct in the rowfilter method?
I have something like:
VB 'Create a data view so we can get each items name Dim dvwItemNames As New DataView(dtlItems) dvwItemNames.RowFilter() = "SELECT DISTINCT Name"
This gives me a syntax error exception. I tried just "Distinct Name" but this had the same result. "Name" is the column name. I would rather do it this way then looping through the entire table and putting the name in an array list or something.
I'm trying to fill a ComboBox from 3 columns in the same table. I need output DISTINCT and the drop down box would look better if it had no spaces between groups.
Using myCommand As New MySql.Data.MySqlClient.MySqlCommand("SELECT ten_id1, ten_id2, ten_id3 FROM tenants", myTenConnection) Dim myReader As MySql.Data.MySqlClient.MySqlDataReader
I have a database which need to be assigned into array arr()(), e.g.
arr(0)(0) = aa arr(1)(0) = ab arr(2)(0) = aa arr(0)(1) = bb arr(1)(1) = ba arr(2)(1) = ba arr(0)(2) = cc arr(1)(2) = cc arr(2)(2) = cc
I need to find distinct / unique value in each arr(k)(0), arr(k)(1), arr(k)(2); where k=0..2. Then i need to insert the result into array cat as follows:
I've tried code below but it doesn't work. The assigning process into array arr()() from DB is OK. Problem only come up if I try to find the distinct value (If Not (cat(j).Contains(ds.Tables(0).Rows(i).Item(j))) ... End If).
For i = 0 To 2 list(i) = New Integer(3) {} For j = 0 To 2
I have a single columned datatable inside a single tabled dataset.I just want to convert this dataset to distinct rows. Here is my code, it gives compile error '.' expected.
Dim query = _ From email In ds.Tables(0) _ Select email.Field<string>("Email").Distinct()
EDIT: I changed to (Of String) and it works... BUT NOW 'query' is an ienumerable collection of characters... not a datatable... so how do I convert back easily without manually doing a loop?
I'm trying to LINQ two tables based on a dynamic key. User can change key via a combo box. Key may be money, string, double, int, etc. Currently I'm getting the data just fine, but without filtering out the doubles. I can filter the double in VB, but it's slooooow. I'd like to do it in the LINQ query right out of the gate.
[Code]...
"Range variable name can be inferred only from a simple or qualified name with no arguments."
I'm trying to LINQ two tables based on a dynamic key. User can change key via a combo box. Key may be money, string, double, int, etc. Currently I'm getting the data just fine, but without filtering out the doubles. I can filter the double in VB, but it's slooooow. I'd like to do it in the LINQ query right out of the gate.
I have to perform the following SQL query: select answer_nbr, count(distinct user_nbr) from tpoll_answer where poll_nbr = 16 group by answer_nbr The LINQ to SQL query
I have a strongly typed dataset that has rows with duplicate [sopnumbe] I am looping through them and displaying row data I want to only display the Distinct sopnumbe rows in my initial loop1.. but the sub loop uses the data out of all rows.. So I cant filter the actual dataset.
I have set up a class to hold song information - artist, title. I then created another class that containg a BindingList(of clsSong). I use this binding list to create a data object that can be used to create an RDLC report. This all works. Now I am trying to remove duplicates from the binding list so they don't show up twice on the report. I can't figure out how to get a distinct list for my report. [code]...
I used the following code to select unique values from the database [code]And it does not retrieve the unique values, it remains the same. But if I use only SELECT DISTINCT Author FROM tblBook it works fine.