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

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

[SQL] can we filter data after group by?

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :ASP.Net

with SaleSheet(SaleDate,SaleAmount,VipId) as
select '2010/02/01' as SaleDate ,10 as SaleAmount,'Allen' as VipId
union all
select '2010/02/01' as SaleDate ,20 as SaleAmount,'Allen' as VipId
union all
select '2010/02/01' as SaleDate ,20 as SaleAmount,'Joy' as VipId
union all
select '2010/02/02' as SaleDate ,10 as SaleAmount,'Allen' as VipId
union all
select '2010/02/03' as SaleDate ,20 as SaleAmount,'Allen' as VipId      -- Allen reach the thresold  on 2010/02/03
union all
select '2010/02/04' as SaleDate ,10 as SaleAmount,'Joy' as VipId
union all
select '2010/02/05' as SaleDate ,10 as SaleAmount,'Amy' as VipId
union all
select '2010/02/05' as SaleDate ,10 as SaleAmount,'Joy' as VipId
union all
select '2010/02/05' as SaleDate ,50 as SaleAmount,'Amy' as VipId        -- Amy reach the thresold  on 2010/02/05
union all
select '2010/02/06' as SaleDate ,10 as SaleAmount,'Joy' as VipId
union all
select '2010/02/06' as SaleDate ,10 as SaleAmount,'Amy' as VipId
union all
select '2010/02/06' as SaleDate ,10 as SaleAmount,'Joy' as VipId

-- I run Query on 2010/02/06 and want to know which member reach the thresold ( SaleAmount > 50 ) be

View Complete Post

More Related Resource Links

One user in group waiting results forever using BDC data filter


I gave a group of users to be able to search a plan name through a BDC filter on SharePoint 2007. All the users have no problems to access BDC data sources, but only one user in the group got waiting on the searching results forever. But this user has no problems to use any other SharePoint features.
I will appreciate it your any ideas.


Pulling data from MyProfile page to People & group's list

Hi, Is it possible to pull the data from Profile page, like the email, contact number etc to be displayed in the People & Groups page. Please refer the screenshot for better understanding..

Link to the image: http://img694.imageshack.us/i/pplgrps.png/

Sharepoint list group by column data ,display in webcontrol like panel or label


I  have a sharepoint list[Managers] with groupby sector column. like below




Each Sector group by column data i want to display in label or panel control.

How to do that ? how to write the logic?



Regards, Mansoor

Table Group can not handle more data

Hi All,   I've a report and from Query, I am generating all serial numbers with comma as seperation. Now I've got an error with serial numbers around 200.   I'm generating invoices through SSIS process and it failed and when I tried to figure out the reason, I found out this. And I tried to Save the Preview in PDF and it gave me the error   An Error Occured during local report processing. Index was out of range. Must be non-negative and less than the size of the collection. Parameter Name : index   I tried to remove that serial_no group from the table, and it worked perfect (but i want that serial_number and thats MUST field in report.) And its working for all other reports. just not for something like BIG report with 4 products each have more than 200 quantities (means 200 serial numbers each)   (Also it will retrive all the data in preivew section. but nothing if you select PRINT PREVIEW)   Any suggestions or ideas whats going on. Thanks in Advance.  

Sum data by group in gridview

Hi, I got a grid with data like this Col 1   Col2-----   ------A         2A         4Total   6B         2B         3Total   5C         5C         6Total   11D         5D         7Total    12How to do like this in gridview asp.net 2.0?Provide coding

How to filter view using ListViewXML with group by option?

I try to filter my view in list using ListViewXml property. The view has group by section. After implementing a filter for the view data under groups are the same(not filtered), but group rows show quantity of rows for each group  like filter is implemented. So it works well without group by.    XmlDocument document = new XmlDocument();  document.LoadXml(webPart.ListViewXml);  XmlNode queryNode = document.SelectSingleNode(".//Query");  XmlNode whereNode = queryNode.SelectSingleNode(".//Where");    if (whereNode != null)     {        whereNode.InnerXml = query.Query;      }   else       {        queryNode.InnerXml += "<Where>" + query.Query + "</Where>";        }  webPart.ListViewXml = document.InnerXml;    

Group by library name using Data View Web Part

I'm using a data view web part to disply documents in multiple libraries.  Is it possible to group on the name of the library instead of grouping on an individual column? Thanks

How to filter managed meta data column using page query string?

I have two managed term sets as follows: Articles:Downloads Projects:Downloads I am trying to use a content query web part to filter a list based on the above manged meta data column. I want to filter out only "Articles:Downloads" Scenario 1: When I edit the web part and directly set the filter option with the managed meta data column to "Articles:Downloads" it works fine. When I export the web part, in the .webpart file, the "FilterValue1" property has a value "Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910". Scenario 2: I want the web part to get the filter information from query string [PageQueryString:pageType]. So I tried using these options: a) pageType=Downloads      This works but returns downloads under 'projects' as well. (i want downloads only under 'articles')  b) pageType=Articles:Downloads     Does not work and shows no records c) pageType = Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work. (This value is the same as one found in 'FilterValue1' of the static web part). c) pageType = Articles:Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work. d) pageType = 9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work.   What am I missing here? Can some one please help?

Any way to filter group or limit the view with recurring events??

It seems that recurring events (in Events view) cause greatlimitations in the views available. For example filtering or groupingon the start date of events does not work.I tried to use a calculated column that makes a 'Start Date' of theStart Time in order to filter on it, but the result is that only thefirst date of a series of a recurring task is shown, which of courseis useless.It seems that the Calendar view is the only way to limit views in day/week/etc.Did anyone else come up with an acceptable way to group or filterrecurring events?  Specifically filtering these recurring events by Week and Month.

Filter out logically deleted data from dbf file using SSIS?

Hi Guys, while loading dbf file to sql server using SSIS,How can i filter out the records that are logically deleted in dbf file?    ~Technology Analyst~

Filter External List by a range of data

I have an external list web part that I am currently using a choice filter on to filter by a certain status.  The options are yes, no, or (empty).  However, I would like to be able to add an additional option to filter where the person has the previous status set to no, but has a value in a different field greater than 90. I realize that the choice filter probably wouldn't do this, and that I could do this easily with separate list views, where I have some sort of web part that can change the list views on the fly. I would think I could use one of those list filters to do this, but when I tried to do that for my original problem, I was unable to connect the filter to my external list. I could connect the list view filter to any internal list without an issue however.  Somebody commented on my old thread that I posted as cptInsane0 that I could just make an infopath view form that has buttons that do this.  I have dabbled with infopath a little bit, but I wasn't able to get infopath to connect to my external list either.  I first had to connect to an internal one, and then for some reason I could add the external list as an alternate data source. I can't be the first person who has had this issue. Could someone point me in the right direction?

group on or filter multiple datasets in 1 table?

I am fairly new to using BIDS, so please bear with me. I currently have 2 datasets, and 1 table.  The layout of the table is: Header HeaderGroup1 HeaderGroup2 Details FooterGroup2 FooterGroup2 Footer Group1 Footer Group1 Footer This is working, but what I need to do is figure out a way to add another Footer Group2 that includes an expression which involves either using data from a 3rd data set because of needing a different filter, or figuring out a way to filter the same data set twice in the one table.   I can give more specific info if you let me know what you need. Thanks!

links to data view group by headers

I have a content editor webpart with some image links,now my requirement is that some images on clicking should go to a dataview web part group header.how can this be done with just one data view webpart ,the way I am doing right now is create few data view webparts from the same list and group them but again filter them by each group,so instead of using just one DVWP I am using as many number as my group headers are there and then edit page to add a hidden CEWP on top of each DVWP and add a div tag to CEWP so that it can reference from other page with image links,I just want to use one DVWP for performance reasons.

Data View that handles both a Filter and "Show All"


I had a nice search facility up and running that filtered SQL data displayed in a Data View via a "contains" Filter clause on an entered string.

Pesky users now request a "Show All" button as well to display all data in the Data View.  For the life of me I cannot get this up and running - anybody got a tip/pointer?  I can't figure a combination of Filter criteria that would allow this.




Full text search not working for pre-existing data on table after installing docx filter.However wor


Hello All,


I have a strange issue. I have pre-existing data in my table with full text index defined along with catalog. I noticed that I did not have the docx filter ,so I installed the filter and performed the steps below. After installing the filter  I added a couple of new records. I noticed that the full text search pulled up the new records matching the criteria but did not return any pre-existing records. Could you please let me know the issue.Please note – currently I am using sql 2008 , but also would like to verify the same in sql 2005 as well.

Also could you please verify the steps below for installation in sql 2008 and sql 2005 and with different architectures (Eg windows 2003/2008, 32bit, 64 bit etc)


This was tested on VM image on 64 bit architecture Windows 200

Display columns in matrix based on a group even if data don't exist in resultset


I am trying to create columns in a matrix for data that currently does not exist.  I created a matrix that groups on office name (ROWS) and Month of Appointment Date (Columns).

For example, the matrix currently looks like this.....

  January February March April
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