.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

MDX Query Measure with time period

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :Sql Server
Hi! How to set up the period in MDX? I need the measure what would be seted up by default 4, 29… days from today. For axample Amount from current day (02.09.2010) till 4 days from today is 06.09.2010. As Result I have to get a report: Buy form Vendor Amount today 02.09.2010 Amount +4days 02-06.09.2010 Amount +29days 02.09-30.09.2010 A 10000 250000 333333 B 150000 222222 555555 C 666666 444444 1222222 Sincerely, Milena

View Complete Post

More Related Resource Links

SQL Server Query request for start and end period of time


Hi all

I have event table where I need to select records between days. my statements look like

  CONVERT(char(8),Event_Table.Event_time,112) BETWEEN 'request start date' AND 'request end date'

The Event_time is DateTime format.


Now everything look fine except from if I need the statement to request the date between as Date_start and Date_End any time it run. The idea is to request a for start and end period of date any time the scrpt run. the script will be run as DOS batch file. 

Slow page load during a list query one time during the day


We have a monitoring tool set to check to see if the home pages for our 3 web apps load in under 60 seconds every 10 minutes.  All 3 web apps load in under 3 seconds on every 10 minute check except for one exception.  One check every day one of the web app's home page takes longer than 60 seconds to load.  This happens at 11:45PM when there is very little user usage on the environment.  The characteristics of this page are as follows: The only thing on the page is a list view web part which was added by the browser.  The page has not been customized with SPD or code at all.  The list that it pulls is a simple links list that has 281 items on it.  The view pulls all 281 items and displays them in sets of 100.  I cannot find any associated event in the server events and/or SharePoint ULS logs nor are there any daily sharepoint timer jobs running at that time.  Our full index happens at midnight with incremental happening hourly.  Our enviroment is 2 WFEs, 1 App/indexer and we have a separate SQL cluster backend.  

Could someone lead me possibly in the direction I should take next in my troubleshooting?   

Query Time out error?

Iam getting the querey time out error, when processing dimension in SSAS? The error Details is: OLE DB or ODBC error: Query timeout expired; HYT00; Unknown token received from SQL Server; HY000.\ can any one help me on this....how to resolve this?

SQLDataAdapter/SQLDataReader takes lot of time for executing a query

All, I have a webpage with 6 drop down lists. User can start with selecting any drop down list first, and then proceed in any order.  1. Assume when the 1st dropdown is selected first, rest 5 drop downs (except 1st) are filtered/updated based on the selected value in the 1st dropdown. 2. Assume user selects the 4th dropdown second, rest 5 drop downs (except 4th) will be filtered/updated but the condition would be based on both 1st drop down and 4th drop down. But while filtering the first drop down, only the 4th drop down is used as filter (self-filter will be ignored). The code works fine but I see a very bad performance hit in time when the user selects the fourth dropdown after selecting three drop downs. I tried using both SQLDataAdapter and SQLDataReader but neither used to solve the issue. The code takes a lot of time on one line in either of the code (which is highlighted below). When I run the query in SQLServer Mgmt Studio, the query completes in a few seconds.Using SQLDataReader: SqlConnection conn = new SqlConnection(CONNECTION); SqlCommand ObjCmd = new SqlCommand(query, conn); ObjCmd.CommandTimeout = 600; conn.Open(); SqlDataReader daSelTools = ObjCmd.ExecuteReader(); //This line takes a lot of time ddl.Items.Clear(); // Call Read before accessing data. while (daSelTools.Read()) { ddl.Items.Add(new ListItem(String.Format("{0}",daSelTools[0])));

The semaphore time-out period has expired

Hi, We have a job that does use linked server to copy some data from a MS SQL 2000 database and insert it into a database which runs in MS SQL Server 2005.This job is configured in the 2005 server. Offlate we started encountering the error OLE DB provider "SQLNCLI" for linked server "RSSDR" returned message "Communication link failure". [SQLSTATE 01000] Error Number::121 Error Message::TCP Provider: The semaphore time-out period has expired. Initially thought its a network issue but network team have confirmed there were no network issue at the specified time.Is it something related to network adapters,card or some other firware. Any help would be highly appreciated. Best Regards, Zainu    

Get Query execution time

I need to find out the query execution time from the front end .Where should I insert the code for that.Please help.. I am using the bleow query: OracleConnection con = new OracleConnection(ConnStr); con.Open();  OracleCommand cmd = new OracleCommand("Stored_Proc",con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(); .................... ................ OracleDataAdapter oda = new OracleDataAdapter (cmd);  

SPD to escalate workflows over a period of time

Hello,   I am hoping that you can help me, I have designed an InfoPath form that I am using for an expenses approval, but I would like to set it so if the approver has an Out of Office, the workflow is submitted to a different user. And if the workflow exceeds a specific time range then the workflow is escalated.   I am using SPD to create these workflows, rather than the in built MOSS workflows.   You're help, as always, is greatly appreciated.   Kind Regards, Dayna

Why the Same sql query taking much more time in other server.

Hi All,   I have a sql query.. which is running absoulutely fine in the production environment. I restored the Prod DB to another server.. same hardware and software configuration as Prod server. But in that server the same query taking ____ lot of time.. What could be the reason ??SD

Convert the Sql query to LINQ .. can anyone please help me I need it ASAP as no time to learn and im

 SELECT DISTINCT HP.PartNumber, HP.MIRevision, HPEXData.InstructionImage, EXAttr.PlacementImage from dbo.HardwareParts HP   INNER JOIN dbo.HardwarePartEXData HPEXData     ON HP.HardwarePartID = HPEXData.HardwarePartID   LEFT JOIN dbo.EXMIAttributes EXAttr    ON EXAttr.EXTypeID = HPEXData.EXTypeID   WHERE    HP.PartNumber = @PartNumber     AND REPLACE(SUBSTRING(HP.MIRevision, 1, 13),'.','') >= REPLACE(SUBSTRING(ISNULL(@PartRevision,''), 1, 13),'.','')    AND ISNULL(HP.ProjectID,'') = ISNULL(@SINumber,'')    AND (@ChassisIdentifier IS NULL OR HPEXData.EXMI_Type NOT IN ('Add In Card','Storage') OR EXAttr.ChassisIdentifier = @ChassisIdentifier)   END;anji

Make a T-SQL Query Sleep for a certain amount of time

At times, in test scenarios, you may want to emulate that a transaction or a T-SQL query takes 'x' amount of time. Well that is possible (although not with precision) in SQL Server (2005/2008) using the WAITFOR command.

How to measure WCF start up time ? ( WCF service .NET 3.5 hosted in IIS6 under win 2003 )

Hi ,   I have relative big and complex WCF (.net 3.5)service hosted in IIS 6 .  When IIS worker goes down , it takes a lot of time for service to wake up.  My question is how can I measure what exactly takes time ? Or its just service compilation time and nothing could be done here ? If I know what takes time I will try to optimize it .    Thanks. 

Getting Query Time Out Problem in Particular Dimension?

We are getting query time out problem in particular dimension in SSAS. can any one help on this?  

Returning Customer Count of all categories in each time period



Lets say I have the following Table:

CustomerNumber | Date | MovieCategory
1 |2010-01-01|A
1 |2010-02-01|A
1 |2010-03-01|A
1 |2010-04-01|A
1 |2010-05-01|A
1 |2010-06-01|D
1 |2010-07-01|A
2 |2010-01-01|A
2 |2010-02-01|A
2 |2010-03-01|D
2 |2010-04-01|D
2 |2010-05-01|D
2 |2010-06-01|C
2 |2010-07-01|C
3 |2010-01-01|B
3 |2010-02-01|B
3 |2010-03-01|B
3 |2010-04-01|C
3 |2010-05-01|C

What I want to do is count the number of customers for each movie type in each date. Now I started with the following simple query

Select Date, MovieCategory, Count(CustomerNumber)
From AboveTable
Group By Date, MovieCategory
Order by Date, MovieCategory

which would be okay, but if no customer has a MovieCategory of say 'B' on a particular date then the results of the above query won't return a row that has

Date| B| 0     (date here could be any date).


which is really what I need. Is there a good way to

T-SQL query, average of daily time periods over a date range



I'm building a report in Crystal Reports using a SQL command against a T-SQL 2005 telephony database.

I need to be able to run the query across a given datetime range, 6 months for example and bring back a 5 day display (Mon-Fri) with a group for every 15 minute interval in each day.

The group figures need to contain an average of the amount of calls presented for each 15 minute interval on any day across the whole datetime range, so for example the Monday 10:00 - 10:15 figure would be an average of all calls presented in every 10:00-10:15 range on each of the Mondays that fall within the datetime range.

I've got a query built now that gives total presented figures grouped by these intervals across one week but I can't figure out how to do this average function across a range.

Does anyone have any idea how I'd go about accomplishing this? I'm pretty new to SQL but keen to learn so any pointers on functions to research etc would be very much appreciated.

Thanks alot in advance, Andy.

Query for detail view across one week included below for table/fields etc...



count(DISTINCT Calls.SessionID) as Presented, min(Calls.startDateTime) as DateTime

INNER JOIN QueueDetail
ON Calls.sessionID =  Queues.sessionID
AND Calls.sessionSeqNum =  QueueDet

Measure Ajax call duration till page is rendered (including screen paint time)



I am using an ASP.Net AJAX application and I need to measure the performance of the AJAX call from the time it is invoked till page is rendered, including screen paint time.

I am using an update panel.

The screen paint time is critical since i want to record the time taken to render the output as well.


Query execution time from 1 second in SQL 2000 to 20 Seconds in SQL 2008 R2


When I ran this query from SQL 2000 it takes 1 second. now in SQL 2008 R2  the same query is 20 times slower.

This is the query and the numer of records and the time in SQL 2008 R2:

SELECT * FROM dbo.CxP_Saldos 
0 seconds, 923 Rows
SELECT * FROM S_Conta.Cuentas
0 seconds, 662 Rows
SELECT * FROM S_Conta.Cuentas c INNER JOIN dbo.CxP_Saldos s ON c.id = s.CtaProv
18 seconds, 923 Rows

how can I submit the actual execution Plan?

How to concatenate measures over time period?



For an external tool I have to prepare Sales ([Measures].[Sales]) figures for the last 5 months like this: 12345|12343|21423|12344|12343. So basically, I need one measure that displays these values concatenated. How can I create this? (In my MDX query I would have this measure on columns and i.e. another dimension like country on rows).



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