.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Query to return conditional counts both distinct and non distinct.

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :ASP.Net

I have the following table structure:

Table EmailTracking



Table EmailTrackingDetail


The following query returns the default recordset:


SELECT     TOP (200) EmailTracking.EmailAddress, EmailTrackingDetail.TranType, EmailTrackingDetail.TranDate
FROM         EmailTracking INNER JOIN
                      EmailTrackingDetail ON EmailTracking.RecordID = EmailTrackingDetail.EmailTrackingRecordID







2010-09-21 16:59:29.980



2010-09-21 17:19:58.790



2010-09-27 12:00:30.940

View Complete Post

More Related Resource Links

GridView Paging + Unsupported overload used for query operator 'Distinct'


The issue is two fold in my ASP.NET Web Application. I need to use a custom Distinct() via LINQ to SQL, and then assign thevar as a DataSource to a GridView. If I use the custom Distinct that implements IEqualityComparer, I get "Unsupported overload used for query operator 'Distinct'" exception. but if i somehow get past this, i get "The data source does not support server-side data paging" exception on GridView.DataBind(). here is the code:

distinct_employees =
    (from emp in wsq.LIT_Active_Employees
    where (String.IsNullOrEmpty(ssFirstName.Text) ? (emp.FIRST_NAME == null || emp.FIRST_NAME.StartsWith(String.Empty)) : emp.FIRST_NAME.Contains(ssFirstName.Text)) &&
            (String.IsNullOrEmpty(ssLastName.Text) ? (emp.LAST_NAME == null || emp.LAST_NAME.StartsWith(String.Empty)) : emp.LAST_NAME.Contains(ssLastName.Text)) &&a

Please help me to find out distinct value from linq to sql query given.

profileuserDataContext db = new profileuserDataContext(); var Q = (from p in db.edufinals join RM in db.edufinals on p.UserName equals RM.UserName where p.name.StartsWith(srcval) select new { p.name, RM.ProfielID, RM.ProfielPic, RM.city }).OrderBy(x => x.name).Distinct().Take(10);//, p.Key.ProfielID, p.Key.ProfielPic, p.Key.city, p.Key.country foreach (var pg in Q.Distinct()) { Response.Write(pg.name); ..................................... .................................... } how to refine this query to get distinct "name" column? Please help me to find out exact value.

Trying to build query using DISTINCT or GROUP BY...beginner here

Hi all, I have a table with the following format: instanceID    timeStamp  stepID 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:33.807 2 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:34.807 3 ... EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:26.773 2 so I need to build a query which will return 1 instanceID and all its stepIDs in one row. So the results would have to be something like this: instanceID    timeStamp  StepIDs 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1,2,3 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1,2 and if possible I would like to specify something like...bring me the data where 'timeStamp' > 2010-09-07 20:35 ps: I tried using DISCTINCT and GROUP BY but could not reach the desired results. Thank you!JCD

Avoiding a SELECT distinct query generated by SSAS when using dimension derived from fact table

Hi, I am using a dimension derived out ot a fact table and the factt able primary key is dimension key. Issue is, there are large number of rows and so many attributes. SSAS issues distinct query and it takes large amount of time. Without the distinct statement, query takes only 3 min for 4 million rows. With the distinct, it takes 20 min. Becuase the fact primary key is the dimension key there is no need of a distinct statement. I know there is a option in the dimension to say "By Table" to avoid this. But unfortuantely, i breach the 4 GB limit for strings. Any suggestions for optimization? Thanks,  Sambath

Need multiple distinct counts, have 1 fact and 1 dimension

I am using SSAS 2005. I have 1 fact table and 1 dimension. I would like to create multiple distinct counts in 1 Measure group, at least I would like them to appear as in 1 measure group to end-user. I have tried role-playing dimensions, and a roll your own approach that work in limitation but didn't scale. Any help and advice would be great.

Distinct or Max - T-SQL QUERY


Hi Guys,

I need distinct or max of below two columns. I am using distinct on both columns but still I result are two instead of one. 


FileName                               FilePath

SALES_082753_20100324     D:\SQL01\LandingFiles\SALES_082753_20100324_2010427.csv

SALES_082753_20100324     D:\SQL01\LandingFiles\SALES_082753_20100324_2010427.csv

SALES_082753_20100323     D:\SQL01\LandingFiles\SALES_082753_20100323_2010427.csv

SALES_082753_20100323     D:\SQL01\LandingFiles\PluSALES_082753_20100323_2010427.csv

SALES_082753_20100322     D:\SQL01\LandingFiles\SALES_082753_20100322_2010427.csv

SALES_082753_20100322     D:\SQL01\LandingFiles\SALES_082753_20100322_2010427.csv

SALES_082753_20100317     D:\SQL01\LandingFiles\SALES_082753_20100317_2010427.csv

SALES_082753_20100325     D:\SQL01\LandingFiles\SALES_082753_20100325_2010427.csv

SALES_082753_20100325     D:\SQL01\LandingFiles\SALES_082753_20100325_2010427.csv


Out-Put Required

FileName                       &nbs

Distinct query generated to populate a dimension

When processing a dimension attribute, what elements qualify as necessary for the auto generated distinct count in SQL?    I am assuming is based on the attribute relationships, but how can I control what goes into the distinct count?  (we have an issue in which we only want a distinct could to include certain attributes and not others)

Javier Guillen

Linq Query Returning Distinct


This is my query 

    Dim s_Person = From Person In db.People Join ProspectStatus In db.ProspectStatus

                       On Person.Id Equals ProspectStatus.Prospect_ID

                       Where (Person.Organization_ID = s_Organization_ID)

                       Select Person

I only want to return DISTINCT values of Person.ID.  Right now, it returns the same person multiple times because there are several entries in the status table.

Any ideas?

    Dim s_Person = From Person In db.People Join ProspectStatus In db.ProspectStatus
                       On Person.Id Equals ProspectStatus.Prospect_ID

distinct datagridview

how can i select distcint from datagridview

How can I GROUPBY Branch, then by Client and then by Referral Type and COUNT DISTINCT LPS # in JavaS

Hi, I have an array in JavaScript. How can I GROUPBY Branch, then by Client and then by Referral Type and COUNT DISTINCT LPS # in JavaScript. Please note I need to do this client side using JavaScript. Array Data: Branch Client    Referral Type LPS # 402036   402430    Psychological File Review 30 402049   402805    In-Home Assessment 10.87927 402050      402993   Chiropractic Assessment 100 402049 402805   Chiropractic File Review 10.88054 402049 402806   Chiropractic File Review 10.88055 402049 402806   Defense Medical 20 402049 402807   Chiropractic Assessment 10 402049 402807   In-Home Assessment + Form 1 10.88054 402049 402807   Physiotherapy Assessment 10 402049 402808   Attendant Care Assessment 10 402049 402808   Chiropractic Assessment 10.88041 402049 402808   In-Home Assessment 10 402049 402816   Chiropractic Assessment 10.85316 402049 402827   Neurological File Review 10.88047 402049 402827   Social Work File Review 10.88044 402050 402605   Psychological File Review 880434 402050 402661   Physician Assessment 878203 402050 402993   Physician A

Check if Linq query return result

HelloI have Linq query likeDim Prood= (From p InProdutcts _                                          where p.ProductID="InputInteger"                                                         ..... ) .SingleOrDefaultI could Use Count if I Use .ToList Instead of .SingleOrDefault , But now that I use 'SingleOrDefault', how Could I find if Prod contains any record or it's Empty.

Return default value from LINK query

I am using the  query listed below which includes multiple joins, in the result of the query I need to do some value replacement values Example: db.ASSETS.BUILDING_ID=300 that ID does not exist in db.BUILDINGS, in that case I need to return BUILDING_ID=0, db.BUILDINGS.NAME="Unassigned" Please help  var productQuery = from assets in db.ASSETs join relocatableUnits in db.UNITs on assets.UID equals Units.RUID into assets_units from Units in assets_units.DefaultIfEmpty() join build in db.BUILDINGs on assets.BUILDING_ID equals build.BUILDING_ID into assets_bins from build in assets_bins.DefaultIfEmpty() join test in db.TEST_SUITEs on assets.TEST_SUITE_ID equals test.TEST_SUITE_ID into test_bins from test in test_bins.DefaultIfEmpty() join testTrgt in db.TEST_SUITEs on assets.T_TEST_SUITE_ID equals testTrgt.T_SUITE_ID into testTrgt_bins from testTrgt in testTrgt_bins.DefaultIfEmpty()

MDX query to only return first row.

I have a given MDX query AND would like to only return a single row instead of the full result set. also I need to do this in a way so I can simple add a line to it without re-writting the entire statement. any suggestions how to do this? background: I running stored MDx queries against the cube using OPENQUERY. the result set I parse first to understand what is part of my dimension (not time) and what column defines the time so I can later on work with the results. any hekp is appreciated.

SSAS 2008 Measure group Distinct count

Hi all, I have a data of as number of trasactions,DD, SO, BouncedDD, CancelledDD all are in count (number) while adding these measures manullay to a measure group I have selected usage as DistinctCount for one measure and for all the remaining measures as DiscinctCount.While deploying the cube it shows error as "Fact table canot have more than one distinct count"  

Can't get Distinct work, when combining fields from inner joined tables

Hi to all.I have a users table, each user has a session and each session has multiple parameters.I would like to retrieve all params for a specific user and get the paramID from the params table and the date submitted from the sessions table.This is the linq to SQL query so far:var items = (from param in db.Params join session in db.Sessions on param.SessionID equals session.ID where userID.Equals(session.UserID) //userID is the function argument orderby session.DateSubmitted descending select new { ParamID = param.ID, DateSubmitted = session.DateSubmitted }).Distinct(); I have noticed that when the DateSubmitted is fetched, i have multiple instances of a param in the result set, meaning that the Distinct does not work as expected. If i remove it like this:var items = (from param in db.Params join session in db.Sessions on param.SessionID equals session.ID where userID.Equals(session.UserID) //userID is the fu

Linq Distinct

Hi, I'm not sure if this is the right forum to ask. But I'm using ASP.net 3.5 & I've below code:string[3] arrSerialKey = {"abcd", "45pr", "ty66"}; var arrDistinctSerialKeys = arrSerialKey.Distinct(); Now, I want to check how many elements are present in this distinct array & if the number of elements in this distinct array is more than 500, then I need to fetch only those first 500 elements. How can I acheive this? Thanks! 

select distinct best row based on a condition

Hi all . The application I'm working on only allowed that one user had one usergroup. I create a User_UserGroup table to allow a user to have n usergroups. My problem now is the following: User 'ablanco' belongs to UserGroup 'comercial' and 'financeira' If i execute this commands i get the following results: SELECT * FROM [T_APP_ModulePermition] WHERE [UserGroup] = 'comercial' http://img26.imageshack.us/i/49234843.png ----------------------------------------------------------------------------------- SELECT * FROM [T_APP_ModulePermition] WHERE [UserGroup] = 'financeira' http://img259.imageshack.us/i/32526938.png ----------------------------------------------------------------------------------- I now need to get distinct rows that belong to ablanco's Usergroups but if on one group the module is Enable=0 and on the other the same module is Enable=1 it should return the ont that has Enable = 1 The attempt i made without taking in consideration this 'Enable' part was the following: SELECT MP.* FROM [T_APP_ModulePermition] MP     INNER JOIN T_APP_UserGroup_Detail UGD ON MP.UserGroup=UGD.UserGroup     INNER JOIN T_APP_User U ON UGD.UserId = U.UserId WHERE U.UserId='ablanco' But since i cant use DISTINCT.* i get all the 14 rows. If someone can help me on this i would be thankful.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend