.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

SQL Select Next 5 Working Days

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
Hi There, Is there any way that using SQL, you can return, in numeric's (1-31) - the numbers of the next 5 working days? I am trying to create a calendar, and I am struggling to get just working days in to my results. For example - if today is Thursday 15th Sept - the next 5 working numbers will be 15,16,19,20,21 Thanks Matt

View Complete Post

More Related Resource Links

select records within 30 days of previous records

I have a table with a list of patient id's and there begin date and end dates as follows: ID     begin_date  end_date 1234 10/09/2009 10/16/2009 1234 12/16/2009 12/22/2009 1234 12/28/2009 12/30/2009 1234 01/13/2010 01/19/2010 1234 04/29/2010 05/05/2010 what i need to do is select the records that have an end_date within 30 days of a begin_date. So in the above example, i would need to get the following records: ID     begin_date   end_date 1234 12/28/2009   12/30/2009 1234 01/13/2010   01/19/2010 Because 12/28/2009 is 30 days within 12/22/2009 (previous end_date) and 01/13/2010 is within 30 days of 12/30/2009 (previous end_date) and 12/22/2009 (2nd previous end_date). is there a good way to do this using T-SQL?

Select Top X not working

hello,ive a problem with ms access query; im trying to solect top X records and it fails returning > X each time. without the TOP keyword it would filter a lot more records, with TOP keyword it alwasy select more than required; im selecting 4 fields and performing value check on 3 of them, ive noticed that if i limit to different values of TOP it works in some cases while doesnt in others; it seems that it select TOP X records with specific value in the last field grouping the results until it select X records; here is an exaple:id     vi_se  ri_no  coeffaf418    16    27    0.592592592592593de788    18    31    0.580645161290323al452    22    38    0.578947368421053du771    14    25    0.56ns011    15    27    0.555555555555556iv729    15    27    0.555555555555556ge103    15    27    0.555555555555556in this case the effective records are 7 however if you group by coeff they are 5 as requested by the TOP keyword, this is the exact query result, there is no grouping performed on coeff field, in addition in this case the last three valu

Working on a SELECT Output

Hi Please can anyone help me. I've just joined two table together. Next I would like to output the SUM of one of the columns. I am almost a complete beginner so please could anyone give me some pointers on how to do this. Thank you Matthew

Select all articles that have participated in a merge replication in the last n days/weeks

I have a merge replication set up in MS Server 2000. Some of the published articles are not used. I am trying to write a query that will select these articles as well as the time when they have been last used in the replication. I was know that in the distribution database there is a table [MSmerge_history] however I cannot figure out how to join it to MSarticles. Any help will be much appreciated! Regards, Pencho

MOSS 2007 Calculate Due Date including working hours and working days

Hi, I've searched many forums for an answer to my problem but with very limited success. Basically, I've created a Sharepoint list where I would like to track a resolution of incidents versus SLA timings. I have a column with a receiving date, which is a date when the request was submitted and another column with priority code where I have 3 possible values: low (5 working days to close the request), medium (3 days) and high (1 day). What I would like to do is to automatically calculate due date for solving the incident based on the receiving date and the priority code. The thing is that now it is getting more complicated since I need to exclude saturdays and sundays from calculation, as well as national holidays. Then I would like to take into account working hours (from 8am to 4pm), for example if an incident with medium priority was submitted on Friday 5pm, then it would have its due date set to Wednesday 4pm (the request was sent after working hours so we have 3 full working days to complete it) Not sure if it is feasible to achieve using formulas, maybe some other way?

Re: Get Working Days


I am developing a leave application which must work like this. I would create a simple scenario to represent this.

I want to get the leave ending date after deducting 'Fridays' and 'Saturdays' in between the leave start and end date,including additional calender holidays added to a separate calender table, and if the leave ending date is suppose a Friday, the ending date must be Friday itself.

For example:

Scenario 1 

Leave Starting Date:  07 October 2010

No: of days requested: 6 Days

Output: Leave StartDate = 07th October 2010, Leave EndDate = 14th October 2010

(Excluding the two holidays [8, and 9th October - Friday & Saturday]

Scenario 2

Leave Starting Date:  07 October 2010

No: of days requested: 7 Days

Output: Leave StartDate = 07th October 2010, Leave EndDate = 15th October 2010 

(Excluding the two holidays [8, and 9th October - Friday & Saturday]

Scenario 3

Leave Starting Date:  07 October 2010

No: of days requested: 8 Days

Output: Leave StartDate = 07th October 2010, Leave EndDate = 19th October 2010 

(Excluding the three holidays [8, and 9th October and 12th Assumed as a holiday from Calender Event Table]

Hope you understood the problem, and will provide a v

Select MessageID from inserted not working


I am trying to get the MessageID from the recently inserted row using the following table and stored proc. I am getting this error when I execute the SP:

(1 row(s) affected)
Msg 208, Level 16, State 1, Procedure GetNextResultMessageID, Line 8
Invalid object name 'inserted'.
(1 row(s) affected)

Is there another way I should be using the inserted table to get the MessageID for this row inserted?


    [MessageID ] [int] IDENTITY(1,1) NOT NULL,
    [MyMsg] [varchar](max) NULL,
    [MyName ] [varchar](50) NULL



USE [MyDb]


Create PROCEDURE [MySchema].[GetNextID]
@Myname varchar(50)

  Insert into MySchema.MyTable(MyMsg,MyName ) Values (' ',@Myname )
  Select MessageID from inserted

Search working for only select users in same domain


I have encountered an issue where one user is able to conduct a search and retrive a full list of results but another user in the same domain and same membership group recieves no results at all.  Our environment is complex with several domains each trusting one another.  The SharePoint build is as follows:


2 Front End Servers with Loadbalance, 1 Application Server, 1 Index Server


So far I have tested with a user that cannot get results from a terminal client, desktop cleint and VPN remote access in all cases the user is unable to return results.   This user has accounts in 2 domains and when selecting to log on as a different user in the other domain results are returned.  I beleived then that it had to be an account issue in the other domain.  However when I worked with another user who also has accounts in both domains that user was not able to return results with either account.  So I am not sure it is domain account related.


I setup a dev box in the domain and made it bare bones with no load balancer, however when I add the user account who was having issue and a search is run no results are returned. 


The search database appears fine and healty and data is populating the tables

Stored Procedure Metadata failure- Dummy select not working


I have a procedure that involves creation of temp table, inserting data into it and use this data to populate another table variable whose output forms the dataset for the OLE DB Source.

Here is the general outlay of the procedure (just a sample data) -


IF 1=0


sql select query not working


hi guys,

i have been having trouble trying to find out what is wrong with my sql select query... i wanted to select a sessionID by comparing the tourID and dates. the dates datatype at the database is datetime and the value of itemSessionDate is in string. i tried to convert itemSessionDate to date and dateAndTime but it gave me mm/dd/yyyy while the date in my database is dd/mm/yyyy hh:mm:ss. so i thought i can try using the itemSessionDate string as it has the same value as the one return from the database. but it keeps on goin to the if not reader.hasRows and quit the statement

could somebody teach me how to solve this problem....

below are my code

Private Function getItemSessionID(ByVal tourID As String, ByVal itemSessionDate As String)
        Dim sessionID As String = ""

        Dim strConn As String = ConfigurationManager.ConnectionStrings("ProrsumDBConnectionString").ConnectionString
        'create sql connection
        Dim myConn As New SqlConnection(strConn)
        'Open connection; Note: You need to open a connection to database

            Dim cmd As String = "SELECT sessionID " & _
                                " FROM TourSession " & _
                                " WHERE tourID=" & Integer.Pars

Sharepoint SPDatasource select command where clause not working for some columns



       I'm using MOSS 2007. I'm using spdatasource to bind a custom list to gridview using webuser control(webpart). spdatasource select command working for all columns except the status column. my status column is a choice- radio with the following options. Pending Review, Checked and Approved. I have checked the field internal name, every thing is correct, but only for status column filtering not happening. Gridview coming as blank

i tried using 

             <FieldRef Name='Status'/>
             <Value Type='Choice'>Pending Review</Value>

Any idea?????




SP Datasource select Command not working for Status Column



    (MOSS 2007-Webuser control Web part)

    I'm having a cutom list that i'm binding using SPDatasource to a gridview. I want to filter the list based on column values. I tried the following code

            SPList taskList = site.Lists["Accounts"];
            SPDataSource mySPDS = new SPDataSource();

         mySPDS.SelectCommand = "<View><Query><Where><Eq><FieldRef Name='Vendor_x0020_Name'/><Value Type='Text'>"+ddlVendorName.SelectedItem.Text +"</Value></Eq></Where></Query></View>";


mySPDS.SelectCommand = "<View><Query><Where><Eq><FieldRef Name='Status'/><Value Type='Choice'>" + ddlStatus.SelectedItem.Text + "</Value></Eq></Where></Query></Vi

openrowset Microsoft.ACE.OLEDB.12.0 stops working after some days



I'm using Microsoft.ACE.OLEDB.12.0, in SQL Server 2005, to read and write, using openrowset, data from/to access files.

Everything works fine for some time but then just stop works. After this, i can't do nothing with openrowset. If i make a select * from openrowset the server doesn't stop executing.

My Microsoft.ACE.OLEDB.12.0 configurations are:



exec sp_configure 'show advanced options', 1



exec sp_configure 'Ad Hoc Distributed Queries', 1



EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1


EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1


EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'

Working with SharePoint document libraries

Document libraries are collections of files that you can share with team members on a Web based on Microsoft Windows SharePoint Services. For example, you can create a library of common documents for a project, and team members can use their Web browsers to find the files, read them, and make comments. Users with Microsoft Office 2003 can check out and edit the files as if they resided on a local or network drive.

By default, your team Web site comes with a built-in document library named Shared Documents, which is listed on the Quick Launch bar as well as on the Documents and Lists page.

AJAX GridView Checkbox Select All in ASP.Net

In the previous article GridView Select Multiple Rows you learnt the multiple row selection in GridView control. Here you will get the C# code sample to select all the gridview rows using single checkbox server control. You can highlight the selected rows with different color and Font state Bold. If you want to use the selection values on different ASP.Net

Working with DropDownList and ListBox Controls in ASP.NET

"In my application i am using dropdownlist to display all categories from a table. When the user clicks this control I display all records in that category. Category table also contains column called color.

I wanted display some categories in dropdown list different color and others in different color using ASP.NET."-Anonymous

Server side validation of Fileupload control not working



I've tried this method in my code behind to try and validate that the Fileupload control has a file or not. I get the error message the the event "Server Validate" cannot be found. Can anyone tell why? Thanks.

Public Sub CustomValidator1_ServerValidate(ByVal source As Object, ByVal args As System.Web.UI.WebControls.ServerValidateEventArgs) Handles CustomValidator1.ServerValidate

' Get file name

        Dim uploadAvatar As FileUpload = DirectCast(AdvertisementForm.FindControl("uploadAvatar"), FileUpload)

        Dim UploadFileName As String = uploadAvatar.PostedFile.FileName

        If UploadFileName = "" Then
            ' There is no file selected
            args.IsValid = False
            Dim Extension As String = UploadFileName.Substring(UploadFileName.L

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