.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

T-SQL 2008 date range complex query?

Posted By:      Posted Date: October 26, 2010    Points: 0   Category :Sql Server

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<

View Complete Post

More Related Resource Links

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

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.




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"]);
            OleDbDataAdapter da = new OleDbDataAdapter("select * from Route_Expenses a where Entry_Date between '" +From+"' and '"+To+"'",cn);
            DataSet ds = new DataSet();
            dataGridView1.DataSource = ds.Tables[0];

 Please..its Urgent!

XPATH query for date range



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 fieldcode="26" fieldvalue="05/04/2010">

  <LF FieldName="Review Date" />


<tr2 fieldcode="27">

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


<tr2 fieldcode="28">

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


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

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


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

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


ListViewControl - how to group items by their value, for eg. date range



I have a table containing courses, columns are 

Name - "Yoga Classes"

DateStart - "2/23/2010"

DateEnd - "2/24/2010"

I am using ListView Control to bind it, is there a way to display them and group them by Month? 

for eg.


Piano Class (10th Jan 2010 - 11th Jan 2010)
Guitar Class (10th Jan 2010 - 11th Jan 2010)


Yoga Class (15th Feb 2010 - 16th Feb 2010)

Thanks for help.

SSRS 2008 date validation

Hello, Perhaps somone can shed some light on this. I want to make sure the user doesn't set a Startdate > EndDate  in a report. I've done some thing like this: Go to the Report Properties under the Report menu and place this code to the Code Tab. Public Function CheckDate(SDate as Date, EDate as Date) as Integer Dim msg as String msg = "" If (SDate > EDate) Then msg="Start Date should not be later than End Date" End If If msg <> "" Then MsgBox(msg, 16, "Parameter Validation Error") Err.Raise(6,Report) 'Raise an overflow End If End Function Then go to the Report Parameters and Add named it XXX with the datatype is string. Checked the Hidden and Allow blank value ckeckboxes. In Available Values choose Non-Queried and from Default Values choose Non-Queried in right side of textbox then press the FX button then paste this code. =CODE.CheckDate( .Value) But I'm having problems with the =CODE.CheckDate( .Value)  syntax. SSRS errors out. Any suggestions/insights? --joe

Oracle Linked serve Query performance in 2000 vs 2008 R2 64 Bit

Hi everyone We have started to migrate one of our reporting systems from Sql 2000 to Sql 2008 R2.  One of the steps has been to test the perforance of certain Oracle linked server queries between each server.  We are finding on average 3 fold better perforance stats (in terms of query completion time) on the old server.  This should obviously not be the case.  The new server has signifantly more CPU/Memory/IO resources to play with, and it is 64 bit (not to mention its new!).  Here's what I got so far: Old server: SQL 2000 on W2000 both fully patched.  Old Dell Dual core with 3 GB of Ram running on two soft IDE Mirrored drives (yes I know... it sucks).  It connecting via the MS OLE DB provider for Oracle (9i client) New server: ESX VMware Server with 2 CPU's assigned, 8 GB of ram connected to large HP SAN.  CPU, ram and IO's have all been ruled out as the problem.  We've tried varying network cards with different results so we havent ruled that out yet.  Its connecting via the Oracle provider for OLE DB (11G 64 bit client) The linked server is an Oracle 9i fully patched server.  All three are on the same network backbone. Running a simple select * query on both servers returns the same number of rows (~76 000) .  It takes ~1:20 on the new server and ~0:20 on the old server. In looking at the wait stati

Query on XML datatype in SQL Server 2008

Hi All, In table I have column with XML datatype. I can able to read the below XML tag. SET   @Title = CAST(@XMLContent.query('data(root/chemicalName)') AS VARCHAR(50)) <root><chemicalName>Sulphuric acid</chemicalName></root> but how can I read (in SQL Server 2008) with multiple rows in it, and how to know the list of child nodes and data of it dynamically? <Root> <Row> < CurrencyCode>GBP</CurrencyCode> < CurrencyName>POUND (STERLING)</CurrencyName> </ Row> <Row> < CurrencyCode>INR</CurrencyCode> < CurrencyName>Rupee</CurrencyName> </ Row></

Need help in Date Range

HI Guys I need help to write a query I have a table Employee: empIDEmployeeName Task StartDt                                EndDt     1      xxx                  1    1950-01-01 00:00:00.000 1979-12-31 00:00:00.000     2     yyy                  2    1980-01-01 00:00:00.000 2009-06-30 00:00:00.000     3      aaa                 3    2009-06-30 00:00:00.000 2010-01-31 00:00:00.000     4     bbb                  4    2010-02-01 00:00:00.000 null when I pass one variable with date like 20070101 I need check the date ranges and get the exact record(this case 2nd records)

Need help in Date Query

HI Guys I have date like 2008-10-31 16:36:567 I want to convert like 20081031 00:00:000 for this I'm using convert(datetime,(convert(varchar(8),replace('2008-10-31 16:36:567','-','')))) is there any option to do this?

SSRS 2008 R2 - Query builder freezes and needs to be shut down Report Builder 3 when attempting quie

Hi, In SSRS, using Report Builder 3.0 (SSRS R2), after choosing a Report Model as the report's embedded data source, launched "Query Deisinger…" from GUI to build data source in report.  First 1-2 attempts at querying underlying data source (Oracle back-end), are successful, but later attempts freeze application to the point where application freezes and overall Report Builder application needs to be forcibly shut-down. This system freeze has occurred when referencing tables containing a very small number of records as well as larger data sets.  Suspect that application freezing may be caused by timing-out of connection to back-end database. but I am not sure. Any suggestion to solve this issue? Thanks   Environment - Distributed deployment Server1(report_srv) --Windows server 2003R2 64bit, Installed sql server 2008R2 reporting service, configuration tools Server2 (report_db_srv) --windows server 2003r2 64bit, sql server 2005 for report server database

Question Regarding Date Range

I have a requirement to confirm how many employees were 'active' on a given date.  The employee table which I'm using contains the following fields (among others):  1.   Row_Start_Date (when an employee started with the company) 2.  Row_End_Date (when an employee terminated with the company (if applicable)) What would be the best way to design this?  For example, let's say an employee started on 1/1/2010 (Row_Start_Date) and is still employeed (Row_End_Date is null).  If an analyst wants to see how many employees were active on 5/1/2010, how do I design it so that this employee is counted.  If I build a date dimension off of Row_Start_Date, then this employee would be reflected on 1/1/2010, but not on 5/1/2010.  Hopefully this makes sense, and I appreciate any assistance/suggestions in advance... KoleKRS

BIDS Query Designer very slow in VS 2008

I am in the process of upgrading my Report Server projects from 2005 to 2008.  These reports use an Analysis Services 2005 data source and have queries that are anywhere from 20 to 100 lines of MDX.  After upgrading a report, when I open or save a dataset in the Query Designer, it takes up to 20 seconds.  In 2005, this would happen almost instantly. Running SQL Server Profiler, I see that there is an extensive number (600+) of Discover events going on with the Analysis Services server while the Query Designer loads. My version of Visual Studio is 9.0.30729.1 SP and I am running Windows XP SP2. Thanks, Mitch

SQL server 2008 - Unable to tune a query with DTA

We are on:Verison: SQL 2008 [10.0.2531] on windows server 2008. Both are enterprise editions.When I highlight the query and right-click, then select Analyze Query in Database Engine Tuning Advisor, then click "Start Analysis",  I get the following error in the "Message" column of "Progress" tab of DTA: Cannot initialize tuning. [.Net SqlClient Data Provider] If I click on this, it displays this message: "Cannot find either column 'msdb' or the user-defined function or aggregate 'msdb.dbo.fn_DTA_unquote_dbname', or the name is ambiguous. Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count=0, current count=1. (Microsoft SQL Server Error: 4121)Please let me know if you guys have any ideas on this error. Thanks,Sam. 

MDX Query - SQL 2008

Hello All, I'm trying to write a MDX for a Custom Rollup (it could be a calculated member too). The Hierarchy looks like below:   NAME                                       LEVEL BELUX HYPERION…       –          Reporting Unit Adjustment/BELUX…    –          Unit Type BEL/LUX/PSF…              –          Legal Entity BEL AS/ BEL IS…           –          Department     327AAN/327ACM…        –          Production Unit     Following is my requirement: -       Convert Hours to Days for a specific Business Rule -       Any Members of ‘BEL’ Legal Entity and ‘BEL’ itself in the BELUX Unit Type should be divided by 7.6. Hence 1 Day

SQL server management studio 2008 freezes when running a query to Grid result

Hello, I've got problems when I run a Query to a grid result using SQL server management studio 2008 under W7 pro. When I execute the query, the first part of the grid returns, but then it freezes. The query should present a double resultset and contains approx 6100 records, devided over 2 grids. Strange enough, the query runs fine when I export to TXT or to a file. It seems though that the problem is building the grid. When it occurs I cannot end the application bij shutting down de SSMS window, but have to kill the Ssms.exe. In task manager I see only 25% CPU usage and approx 20% memory usage. Nothing exiting there I guess. Suggestions anybody? Microsoft SQL Server Management Studio      10.0.2531.0 Microsoft Analysis Services Client Tools      10.0.3798.0 Microsoft Data Access Components (MDAC)      6.1.7600.16385 Microsoft MSXML      3.0 4.0 5.0 6.0 Microsoft Internet Explorer      8.0.7600.16385 Microsoft .NET Framework      2.0.50727.4952 Operating System      6.0.6001 regards, Pim

Export Data to Excel Based on Date Range

I have data from multiple tables that I would like to display in a gridview via a stored procedure.  I need to select the data based on a date range and display it.  Once displayed I need to include a button that would allow the user to export the gridview data to an excel spreadsheet.  Does anyone have an out of the box solution or know of a tutorial I can use to accomplish this feat?  I am currently using 2.0 for this project. I am newish to .net. Thanks in advance. Here is my stored procedure that calls the data from multiple tables.USE [DATABASENAME] GO /****** Object: StoredProcedure [dbo].[GetUsersAll] Script Date: 09/03/2010 08:20:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetUsersAll] AS SELECT aspnet_Users.UserName, aspnet_Membership.Email, Team.Name, MemberTeamRole.InsertDate, MemberTeamRole.ManagerApprovedDate, MemberTeamRole.RegistrarApprovedDate, MemberTeamRole.RoleName, MemberInfo.firstname, MemberInfo.lastname FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId INNER JOIN MemberInfo ON aspnet_Users.UserId = MemberInfo.memberid LEFT OUTER JOIN MemberTeamRole ON aspnet_Membership.UserId = MemberTeamRole
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