.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

Active Service Requests based on start and end date, where date is null or in range

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


I have FACT and DATE dimension

ServiceRequestFactID          StartDateId           EndDateId

100              20090401              20090501
101              20090501              NULL
102              20100301              20090401

       DateID              FYear     FQuarter     FPeriod
       20080401          2009        Q1            01
       20080429          2009        Q1            02


Some service requests do not have end date, which means that they have service request all the time.

Date Hierarchy:   FYear -> FQuarter -> FPeriod. (Note: the hierarchy is not till the date level)


MDX Help: How to calculate the acti

View Complete Post

More Related Resource Links

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

How do i access Active Directory Start Access and End Access Date


How do i access Active Directory  Start Access Date and End Access Date from C#.NET (what properties am i supposed to look for??)

Thanks in advance for the help


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.

Filtering/selecting rows based on date


I have a gridview, based on thsi datasource:


<asp:SqlDataSource ID="SqlDataSourceAllLoads" runat="server" 
        ConnectionString="<%$ ConnectionStrings:tplatz_dk_dbConnectionString %>"               
        SelectCommand="SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, tblDelivered.delivered_Date)) AS delivered_Date, tblDelivered.delivered_LoadNo, tblDelivered.delivered_TrailerNo, ISNULL(tblDeliveredInfo.deliveryInfo_FirmaNavn, N'?') AS Expr1 FROM tblDelivered FULL OUTER JOIN tblDeliveredInfo ON tblDelivered.delivered_LoadNo = tblDeliveredInfo.deliveryInfo_LoadNr ORDER BY delivered_Date DESC">

Using a dropdownlist, I can filter (by changing datasource) the results based on customer names in a dropdownlist. This datasource, working perfectly, looks like this:


<asp:SqlDataSource ID="sqldataSourceKundeLoads" runat="server" 
        ConnectionString="<%$ ConnectionStrings:tplatz_dk_dbConnectionString %>" 
        SelectCommand="SELECT  DATEADD(day, 0, DATEDIFF(day, 0, tblDelivered.delivered_Date)) AS delivered_Date, tblDelivered.delivered_LoadNo, tblDelivered.delivered_TrailerNo, ISNULL(tblDeliveredInfo.deliveryInfo_FirmaNavn, N'?&#

Deserialization issue with returned from web service

Hi, I have a VS 2008 C#  client using a proxy generated by the Service Reference tool from a schema for a (prob Java?) ASMX web service.  I got serialisation errors when I tried connecting to the service around<xs:date> date fields, and on inspecting the incoming XML discovered the date format was yyyy-mm-dd hh:mm:ss.sss.   So 2 questions really.. 1) Is the schema I've been supplied with incorrect? 2)Is there a way to work around this, apart from manually editing the schema to xs:datetime and regenerating the classes?   Thanks MJ

Filter items with a date column with parameters start date and end date on a sharepoint list?

If I have alist with a view that has the columns Title, Status and Status Date and the view has some items. How Can I add a filter with two parameters, start date and end date, to only show records with status date equal to or greater than parameter start date and status date is less than or equal to parameter end date? This is a sharepoint online site and I can't create and deploy custom code, can use SPD though. Do I need to use SPD or is this something I can do in the list settings? Thanks in advance.   Edit: I had a look here http://www.endusersharepoint.com/2009/09/29/sharepoint-date-filter-filtering-a-list-by-greater-than-or-equal-to-date/ but I don't have the Date Filter web part.

Updating a List item field value in a SPD Workflow is setting Date fields to null

Hello everybody,I faced a very weird behavior while creating a workflow in SPD against a Document Library.Here is the case:1- Create a document library in sharepoint site.2- Add a custom column of type DATE.3- Upload any document to the library4- Edit the item metadata and set the custom date field to any date you want.5- Now using sharepoint designer, create a very simple Workflow with only one action that updates the current item "Title" field to any value. (Note: In my case I set the workflow to start manually and not automatically)6- Save the workflow and then start it on the uploaded item.7- Now check the value of the date column after the workflow is finished.8- You will see that the Date field is Empty... (very weird)Notes:   1-This behavior will not appear on a standard list. it is only appearing in a Document library.  2- I am using WSS 3.0 SP2 + MOSS 2007 SP2. Also SharePoint designer 2007 SP2Did anyone face this situation and does it have a fix, or am I doing something wrong here... Please help.Regards,Maheed

Sort WSS 3.0 Calendar by Start Date

I have read a few posts about people not being able to sort by start date on the calendar.  Was wondering if anyone has this figured out to sort by start date correctly with re-occuring appointments?  I have a code snippet from another post but do not know how to format it correctly as I am a new sharepoint user.  If someone can help with that that is also appreciated.  Thanks.  Code Below:  I basically have no idea to find the info requested below <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script> <script> //today's date var today = new Date(); //get the Date var myDate = today.getDate(); //get the Month var myMonth = today.getMonth()+1; //get the Year var myYear=today.getYear(); //put date into a mm/dd/yyyy format var myDate = myMonth + "/" + myDate + "/" + myYear; //If this is on a webparts page, get the WP div id.  If it's on a list page, you don't need the webzone div ID. //Get the table and table class (usually it's .listviewtable, but if you've customized a webpart, sometimes it's .ms-summarycustombody.  //I used Todd Bleeker's "Style Under Cursor WebPart" to find the CSS class. $(function(){ $('div#WebPartWPQ3 table.ms-listviewtable tr:not(:contains("' + myDate 

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)

Not Null Date Default

I'm one of the people that try to avoid nulls when ever possible, usually using defaults.  This is especially important since my apps do a lot of data exchange with external entities often involving flat files.  A flat file can't distinguish between a null or an empty value so to ensure consistancy between data types in both systems I try to make sure every field has some value.  (E.g., If I have a null or empty string in a char field type a CSV flatfile shows ',,' for both.  The target system might interpret this as an empty string or a null; I have no direct control.  This could result in a null in one system and '' in another losing consistancy.  For charactor and number types this is rarely a problem if I communicate that there are no null fields in the data, but for dates it often becomes an issue. Is there a industry standard approach to managing "empty dates" so I can make date fields not null and empty? Thanks

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

Alert based on date

I have a list of SSL certificates and their expiration dates.  I created a column that calculates the date 45 days before the expiration and then a view that only shows those items where the (expiration - 45) > Today. I then set an alert for any changes to items in that view.  However, I don't get an alert for items that pop into the view...I guess because the item itself did not change.  Is there a way to send an alert based on date?  My SharePoint enviroment is locked down so I can't create custom workflows or connect via Designer.

Search based on creation date

Hello All, I would like to ask which managed property I should use for searching based on creation date. It seems I can't find a managed property to do this. I created my own managed property with the right crawled properties, which works. But I think this should be something out of the box.  Please help, thank you  Anna

Dynamic list item title based on date created & regional settings?

I have a list of things that doesn't *need* a title.  However, because sharepoint uses Title for links, RSS etc  I tried generating a title in an event handler.   This would work great if I wasn't basing it on a date field: public override void ItemAdding(SPItemEventProperties properties) { DateTime dt = Convert.ToDateTime(properties.AfterProperties["TestDate"]); properties.AfterProperties["Title"] = dt.ToShortDateString(); base.ItemAdding(properties); } In testing, this sets the title to "8/9/2010" when I selected 8/10/2010 in the date picker.  I figure this is because of the regional settings of my test user.  I can probably correct the title value for 'test user' but that won't fix 'test user in mongolia' who would still see two different values. So What I would like is to have the Title always display the value MyDate in the uesr's locale.  So the field needs to be dynamic not only at edit/update time, but at display time. Does anybody know if this can be accomplished, and more specifically how?

how to force null in date field - access 2007 ?

hi i have table that contain date field. how i can force null or empty value on this field ? thank's in advance

How I select Start of week is Friday from date of Year ?

Hello everyone, I am tring to solve How i select Start date is Friday from week? Details for which i want to show I have dtmDate column in a query. from that date i display week of that date as per using (ww,dtmDate) as dtmWeek, and parameters are StartDate & EndDate Suppose I select StartDate as 1/1/2007 & End Date as 12/31/2007   I  do not want to show Output as: Weeks                      Column1          Column2 1/1/2007 (Thuesday)       5                     6 1/2/2007 (Wed)               4                     2 | | 1/5/2007  (Friday)            1                      8 1/6/2007 (Saturday)        7                     

MDX Date Range calculated member

Hi, I have [Sales Amount] and [Date]. How can I create a calculated member that will aggregate only the values from [Sales Amount] where month([Date]) is 'Jan' or 'Feb' and year is CurrentYear ? Then 'Mar' & 'Apr' and so on.... Thank you. D.
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