.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 filter dates by DAY

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
i have the following data : first column: dates and 2nd column: values i want to filter the dates per day and calculate the AVERAGE of their values per day i can filter them manually by setting day(datetimefield) = 12 and calculate the AVERGAE but i dont know how to do it for all in one query? each record resulting the average of that day   2010-07-12 00:00:00.000 900 2010-07-12 10:00:00.000 1000 2010-07-12 10:10:00.000 1200 2010-07-12 12:00:00.000 1100 2010-07-12 15:00:00.000 1300 2010-07-12 15:10:00.000 1500 2010-07-13 00:00:00.000 6500 2010-07-13 10:00:00.000 5500 2010-07-14 00:00:00.000 5500 2010-07-14 10:00:00.000 4400 2010-07-15 10:00:00.000 4500 2010-07-16 00:00:00.000 1000 2010-07-17 00:00:00.000 7000 2010-07-18 00:00:00.000 8500 2010-07-19 00:00:00.000 1500 2010-07-20 00:00:00.000 1700 2010-07-21 00:00:00.000 1900 2010-07-22 00:00:00.000 2000  

View Complete Post

More Related Resource Links

Filter out a subsite with Content Query

I'm using a sp 2007 Content Query to pull in news feeds from all over our site collecton.  I'd like to filter out one sub site using the "additional filters" option.   Is there a sub-site guid I could use, or a way to exlude one single site via the additional filter?   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?

MDX query to filter on specific dimension attributes

Ok, trying to figure out how to filter on specific dimension attributes. Are there any suggestions? I have a measure that needs to be filtered by a dimension that has numerous attributes (20 attributes in total) but I only want to show that measure against 10 of the attributes and once I get that result I need filter that result set against my time dimension (which this part I have figure out)   This is what I have so far: With member [calculated_measure_name] as ( [measures].[count_of_people], { [dimelgiblestatus].[elgible].[elgible1], [dimelgiblestatus].[elgible].[elgible2], [dimelgiblestatus].[elgible].[elgible3], [dimelgiblestatus].[elgible].[elgible4], [dimelgiblestatus].[elgible].[elgible5], [dimelgiblestatus].[elgible].[elgible6], }   )   Select [calculated_measure_name] on columns, [date].[fsicalyear].&[2008], [date].[fsicalyear].&[2009], [date].[fsicalyear].&[2010], [date].[fsicalyear].&[2011] on rows from mycube   When I run it, it gets no errors but in the result set for the numbers I get "#error"; do I need to basically create a subcube in order to get the result like I would like? If so how would I start doing that?   ThanksNetwork Analyst

How to apply dynamic filter in Datasheet View from Query String

The datasheet view doesn't support either connections/dynamic filters from Query string. How to filter the data using query string?

Filter Vs Range - which would be fast in these query?


Hi All,


select {[Measures].[Cost]} on columns, NON EMPTY(filter([CALENDAR].[CALENDAR DATE].[All].children,
CDate([CALENDAR Date].[CALENDAR DATE].Item(0).MemberValue) >= CDate('2010-01-06') and
CDate([CALENDAR Date].[CALENDAR DATE].Item(0).MemberValue) <= CDate('2010-07-25'))) on rows
from (select (
{[Product].[Product TYPE].[STANDARD],[Product].[Product TYPE].[TRIGGERED],[Product].[Product TYPE].[Not Specified]},
{[Account].[ACCOUNT_NAME].[All].children}) on columns
from [Sales])
select {[Measures].[Cost]} on columns, NON EMPTY( 
[CALENDAR].[CALENDAR DATE].[06 Jan 2010]:[CALENDAR].[CALENDAR DATE].[25 Jul 2010]) on rows 
from (select (
{[Product].[Product TYPE]

Multiple Dates Query


I need help in creating a query to get info for year selected and previous year of the year selected. Needs to be by quarter(QTR) for both years. QTR1 will always be the month 03, QTR2 month 06, QTR3 month 09, & QTR4 month 12. No computations are required or averaging.

Fields/columns are YEARMONTH stored as mm/dd/yyyy with dd always being 01, ACTUAL_MONTH(float)for all QTRs based on current YEARMONTH selected and one year prior,TARGET_MONTH(float) for all QTRs based on YEARMONTH selected, and PROJECTION_MONTH(float) for all QTRs based YEARMONTH selected.

YEARMONTH (PK, Datetime)

Should look like this if any YearMonth in 2010 was selected
                   Q1 Q2 Q3 Q4
09-Actual      xxx xxx xxx xxx
10-Actual      xxx xxx xxx xxx
10-Target      xxx xxx xxx xxx
10-Projection xxx xxx xxx xxx

SharePoint List Query Filter


I am writting an SSRS report where I am querying a SharePoint list and trying to filter the data through the CAML query.  In BIDS, the query returns data, but the filter where clause doesn't seem to be getting recognized as I get all of the data back.  The where clause does seems to work in the U2U CAML Query Builder tool.

Can anyone please give advice as to why my where clause isn't working?


   <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
         <Parameter Name="listName">
                   <DefaultValue>Subscribe - Sales by Product Group - Wheel</DefaultValue>

filter condition on dates report builder 1.0


Hello I am using reort builder 1.0 . I am connecting to oracle database using a report model

I would like to get data from the tables.. the condition in pl/sql looks like this how can we convert it into .. report builder.. functions or should i create query in report model itself and use it in report builder




Thanks in advance..

SQL Query for Date Check User Dates



I am using two columns in one table such as StartDate,Enddate.

In asp.net Form two text boxes there. One is StartDate other one EndDate..

When user Click Submit button the Two dates is compare above DataFiled's...


-------- Data Base Values ----------------

StartDate            EndDate


10/09/2010         14/09/2010

16/09/2010         28/09/2010



user send Start Date ' 27/09/2010' and EndDate '30/09/2010'


Now the Query Check Dates Between Userdates avialable in Database....


What I ll do?????


(Note : Opertion only allow the Dates Not Exists in Database Dates)



Is there a way to query entity based on multiple filter criteria? WCF Data Services, Linq to Entiti


Instead of:

DW_CMSOPEN dwc = new DW_CMSOPEN(new Uri("http://acctdev02/WCFDataService/EmployeeService.svc"));

dwc.Credentials = System.Net.CredentialCache.DefaultCredentials;

var employees = from emp in dwc.Employees 
             where emp.DEPT == "123"
             select emp;

I'd like the linq query to resemble:

var employees = from emp in dwc.Employees
              where emp.DEPT // in {"123", "456", etc}
              select emp;

query wich returns records for each day between start date from table 1 and mutation dates in table



I have to tables with dates, table Employments with all employements of all employees, with start date and end date, and a table Mutations with mutations (e.g in salary) with start dates.

Now I try to write a query which returns a record for each day an employee is in employment, with the correct salary. SO at first, it should return all days between the employment start date and the first mutation date, then the number of days between the first mutation date and the second mutation date etcetc, and at last the number of days between the last mutation date and the employment end date. The number of mutations varies for each employment, and employees cna have multiple employments (history, so not at the same time) in the employments table.

How to do this?

Select Query For Dates comparision


Data is like this in one table.Those are dates but values are VARCHAR datatype



How to write a select query by passing  two parameters Dates between @StartDate and @EndDate . But From Front end values are passing like below for example

01-Jan-2010 and 31-Dec-2010

How to write a select which consider dates in above format and gives correct results.




Strange behaviour taxonomy filter in content query webpart on anonymous website


We are experiencing strange behaviour on a publishing facing website implemented in SharePoint 2010.

The story
We use a (default) Content Query Webpart to show related information in relation with the current page. The filter is using a field value (UserFieldValue) of the current page and filters other pages which have the same tags. The fields, on the current page and the related pages, are taxonomy field. Everything works fine!

We published the pages, looked at the website as an anonymous user and everything was still going smoothly. We went home, slepts an hour of 6, waked up. Proudly checked our beatifull new website :). Ahhhhh, what is this.. all content query webpart with the filters on the taxonomy field returned exactly nil.

We logged in to our website, checked if the pages where published, and well, they are... Just published them again. And everything looked fine again.... but not for long. Exactly the next day (... app pool recycle ...) the same problem ...

Logged in again to our website, logged out...  problem solved.... till the next apppool recycle.

My conclusion
Until the firrst authenticated visit to the page the taxonomy filter on the content query webpart silenty fails.

As a "temporary" workarround we have created a sc

Sql query from 4 different dates


its easy to combine and collect data between two dates but my problem is quet difficult and amazing as well. i have to collect data from 4 differet days  which are in sequence means first from 6am to 6pm second day 6am to 6pm  thirdday from 1800 to forthday 6am and from forth day 1800 to fifthday 6am meam collection of data for one shift. presenly   i am calculating data from this query but only for one day either day or night. and seperatly. but on four pages but i want to collect all out put on one page i know its not difficult for my expert brothers who will hepl me indeed.....................eric

my presend query is this.................


 @ShiftDate Datetime,
 @Shift_D_4_Day_N_4_Night char(1),
 @Shift_0_1_29 int
-- @EndDate Datetime

SELECT     TOP (100) PERCENT COUNT(*) AS Quantity, dbo.System.Type, dbo.Room.RoomID, dbo.GetLineLU(RoomID)As RoomWrkIDID, dbo.GetDayDate(dbo.Room.Datestamp) As "Day_Date", dbo.GetShift(dbo.Room.Datestamp) AS "Date|D/N"
FROM         dbo.Room INNER JOIN
                      dbo.System ON dbo.R

Having issues with Query parameters not returning my filter value when specifying >=


SSRS 2008 w SP2
VB.Net to pass the parameters
Report Viewer on a form setup for Server Report

I am currently creating a SSRS Report and having problems with my query filters. Basically I am trying to pass 2 values to filter (there are actually 4 range values that can be used to filter the report, all of which work the same way). The parameter values are passed to the server report through an exe with a seperate front end form for the user to specifiy their filters. The report has a Text Query for the Dataset using ODBC connection and unnamed parameters. For the Range parameter I am doing as "WHERE ord_no >= param.from AND ord_no <= param.to" in the dataset query. The paramaters are setup and in the correct order. The problem is that whatever "param.from" value I use, It never shows in the report. When I catch the query from Profiler and run it in Sql Management Studio, it returns the results correctly.

For example:
My filter range would be for orders number 60 through 62; When displaying the SSRS report, orders number 61 and 62 show, but 60 does not (it seems to always return the results as if it was WHERE ord_no > param.from AND ord_no <= param.to)... If I catch the query from Profiler and run it Management Studio, the results include orders 60, 61, and 62 correctly like I would expect to see on display o

TSQL filter query.



I have a dataset which I need to filter. However everything I have tried failed.
This is the dataset:
ID                 Quantity            StageNo            MovedOn
187209             -1                     20                     2010-11-04 11:41:25.173
187210             -1                     20                     2010-11-04 11:41:25.173
187211             -1                     20                &nb

Content Query Web Part - Filter Contents of Document Library



I would like to display selective content of document library using content query web part. My document library has multiple folders. I would like to have the content query web part display the contents of one particular folder. And also another content query should display the contents based on a column in the document library. I don't see these options when customizing the query for the content query web part. If this is not possible by default, what is the potentail alternative solution ?



Srikanth Reddy

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