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


Top 5 Contributors of the Month
satyapriyanayak
SP
Sambanthamoorthy
Swati Kadam
rosgeppe
Post New Web Links

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

Posted By:      Posted Date: September 21, 2010    Points: 0   Category :Sql Server
 

Hi,

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...

 

SELECT

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

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


View Complete Post


More Related Resource Links

Query acting weird.. larger date range works in 1min 26sec and a smaller range say 1 month - 3 month

  

I have no clue why my query is acting weird

If i try to run it for 1/1/2010 9/30/2010 the query takes around 1min 26 sec and  return around a million rows

and If i run for 8/1/2010 to 8/31/2010 it takes forever to it...

basically i am getting data from 5 tables and putting it in a temp table and then updating that temp table 2-3 times with some information and then displaying it.

I am stumped as to why it works fast for a longer date range and runs the  snail for a small period of time..

I am in the verge of pulling my hair and going crazy..

any help will be appreciated.

 

Thanks

Karen


Range dimension - possible with Relative Time Periods?

  
Hi All,

I have a Date_Range dimension with members 7 days, 30 days and 90 days only.
Although all reporting needs could be well address using existing date dimension and
MDX filter or range operator I need a physical Date_Range dimension.

It seems possible to address my requirement (implementing Date_Range dimension) by Handling Relative Time Periods .
The blog post (by Chris) is rather old and I did not find updates on it or recent similar articles.

Please share your thoughts on how best to address this requirement.

Thanks,

Prakash Gautam

problem with date range between select Query in data adapter

  

hi all,

can any one help me out...why am getting "data type mis match error"

 DateTime dFromDate = dt_From.Value;
            dt_From.Value = DateTime.Parse(dFromDate.ToShortDateString());
            string From = dFromDate.ToShortDateString();

            DateTime dToDate = dt_To.Value;
            dt_To.Value = DateTime.Parse(dToDate.ToShortDateString());
            string To = dToDate.ToShortDateString();
            OleDbConnection cn = new OleDbConnection(ConfigurationSettings.AppSettings["connec"]);
            cn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("select * from Route_Expenses a where Entry_Date between '" +From+"' and '"+To+"'",cn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];


 Please..its Urgent!


XPATH query for date range

  

Hi,

I am pulling the data into a column as XML data type and I want to query within the below XML that is returned.

I want to construct XPATH that allows me to search for a date range where Field Name = Review Date. I want records to be returned whenever the Review Date falls within a date range that I am passing from the UI

<tr2 fieldcode="23" fieldvalue="Periodic Review">

  <LF FieldName="Request Type" />

</tr2>

<tr2 fieldcode="26" fieldvalue="05/04/2010">

  <LF FieldName="Review Date" />

</tr2>

<tr2 fieldcode="27">

  <LF FieldName="Total Number of Accounts" />

</tr2>

<tr2 fieldcode="28">

  <LF FieldName="Total Number of Roles" />

</tr2>

<tr2 fieldcode="27" fieldvalue="28">

  <LF FieldName="Total Number of Accounts" />

</tr2>

<tr2 fieldcode="28" fieldvalue="252">

  <LF FieldName="Total Number of Roles" />

</tr2>


Simple CAML query without Time in the date

  

Hi,

I have written following CAML query.

string.Format("<OrderBy><FieldRef Name='Title' Ascending='False' />

<FieldRef Name='Priority' Ascending='False' />

<FieldRef Name='Status' Ascending='False' />

<FieldRef Name='PercentComplete' Ascending='False' /><FieldRef Name='AssignedTo' Ascending='False' /><FieldRef Name='TaskGroup' Ascending='False' /><FieldRef Name='Body' Ascending='False' /><FieldRef Name='StartDate' Ascending='False' />

T-SQL 2008 date range complex query?

  

Hi, I have SQL 2008 table like

SiteID      TotalBeds     AdmissionDate      DischargeDate

01             10             2010-08-01            NULL
01             10             2010-08-02            2010-08-05
01             10             2010-08-02            NULL
01             10             2010-08-02            NULL
01             10             2010-08-02            NULL<

Seperate DateTime into Date column and Time column in query

  

This is probably a simple question but here it goes.

First off let me state that the naming of these columns is not by my choice nor can I change them.  We purchased some software that automatically does the naming conventions for it's fields (Sample1).

I have a table with 2 columns in it:

Sample1                      Time is in GMT Time

Time           DateTime, PK        

LMP             Float

Table 2:

ABC_Price                   (Day, Hour) is in EST Time

Day               SmallDateTime   PK

Hour              TinyInt       PK

MW                Decimal(7,3)

Price              Money

 

What I need to do is split the Ti

Date/Time query on aspnet_membership

  

I am trying to write a query that returns me everyone that registered between Oct 24 and Oct 31.

the create date field id datatype   "datetime" 

2010-10-31 20:31:47.000

 

 


Date and Time Functions in SQLSERVER

  
Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
4 GETDATE and GETUTCDATE Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

Data Types - Date and Time in SqlServer

  
Date and time values can be stored with either the DATETIME or SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. The DATETIME data type can hold values from January 1st of 1753 to December 31st of 9999. The time is stored to the 1 three hundredths of a second and each value takes up 8 bytes of storage. The SMALLDATETIME data type can hold values between January 1st 1900 and June 6th of 2079. The time is tracked to the minute and each value takes up 4 bytes of storage. The majority of business applications can live happily with SMALLDATETIME, however, if you are in an environment where each second matters or you need to make estimates to the distant future (or past) then you have to resort to DATETIME. If you fail to specify the time when inserting a value into a DATETIME or SMALLDATETIME column, a default of midnight is used. If you fail to specify the date portion the default of January 1, 1900 is used.

Built-in Functions - Date and Time Functions

  
Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

Essential SQL Server Date, Time and DateTime Functions

  
The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

Date/Time Conversions Using SQL Server

  
There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Date and Time Data Types and Functions

  
The following sections in this topic provide an overview of all Transact-SQL date and time data types and functions. For information and examples that are common to date and time data types and functions
Categories: 
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