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

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

need a help on T-SQL query to calculate number of instances in a month

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


     I need to write a T-SQL query where i need to get number of bugs logged in our database per month. and i am using the below query for that

Select count(ID) as [No. of Bugs], (datename(

View Complete Post

More Related Resource Links

Query the names and number of instances on a different server



I was wondering is it possible to use a query on one instance that (perhaps using linked servers) quieries another server to ask what instances are installed on it. 

Is this possible in SQL Server or do I need to use an external method?


function to calculate number of business days in a month for a given date


hi ,

Is there any function to calculate number of business days in a month for a particular date.  my input would be just a single date



Number of query values and destination fields are not the same

If RadioButton1.Checked = True Then rbtn = 1 Else rbtn = 2 End If ' Our insert command strSQLQuery = "INSERT INTO tblJobs " _ & "(filingDate, filingDay, reportDate, reportTime, company, jobsite, location, duration, DrugTestReq, foreman, instructions, jobSteward, nPlumbers, nFitters, nWelders, nTradesmen, nPlAppr, nFitAppr, nServTech, nHelpers) " _ & "VALUES (@DTValue, @TextDay, @DTValue2, @TextValue, @TextValue2,@TextValue3,@TextValue4,@TextValue5,@rbutton,@TextValue6.@TextValue7,@TextValue8,@NumVal,@NumVal2,@NumVal3,@NumVal4,@NumVal5,@NumVal6,@NumVal7,@NumVal8)" ' Create new command object passing it our SQL insert ' and telling it which connection to use. objCommand = New OleDbCommand(strSQLQuery, objConnection) ' Add parameters that our SQL command takes: 'radio button 'OleDbType. objCommand.Parameters.Add(New OleDbParameter("@rbutton", OleDbType.LongVarChar)) '- objCommand.Parameters.Add(New OleDbParameter("@DTValue", OleDbType.Date)) objCommand.Parameters.Add(New OleDbParameter("@TextDay", OleDbType.LongVarChar)) objCommand.Parameters.Add(New OleDbParameter("@DTValue2", OleDbType.Date)) objCommand.

Analysis Service Oracle Number inconsistent Data Type for TABLE or Named Query

Dear Gurus, I'd VERY OLD PROBLEM. And I believe it addressed since 2006. When I design DataSource Views from Oracle Data Source. I found it return different oracle number data type for TABLE or NAMED QUERY   Provider Data Type Column Data Type Data Source View Data Type Oracle OLE DB Provider (OraOLEDB.Oracle.1) Table Number System.Int64   View Number System.Decimal   Named Querey Number System.Decimal Microsoft OLE DB Provider for Oracle (MSDAORA.1) Table Number System.Double   View Number System.Double   Named Query   1 System.Int64   Named Query   1.1234 System.Int64 Althought I know I can fix IT via MANUALLY EDIT DATASOURCE VIEW XML SOURCE. But I don't think this is a better solution. Is anybody have ideas ?  Wilson

How to calculate a SQL Server performance of a query based upon table schema, table size, and availa

Hi What is the best way to calculate (without actual access to a SQL Server) the processing speed of a query (self-inner-join) based upon table schema, table size, and hardware (CPU, RAM, Drives)? ThanksThanks Jeff in Seattle

Month Name & Number

SELECT distinct [month_name],DatePart(mm,cast(substring(month_name,1,3)+'1900' as DateTime)) as val1FROM [month_day]    this works fine but i need months like August to be returned as 08 and not 8 ie i want them to be returned as two digit number   Pls Help Thanks Michelle    

Display the Sum of hours and Month Year using sql Query

Hi,  I have,hours(in varchar),Id in table tbl_x ,i need the sum of hours, monthyear(eg. mar 2010)for the last three months.But with two separate queries i am getting these results.but actually i need in the following format   Month/Year      TotalHours-----------------    ----------------Mar 2010            0400Apr 2010             0450-----------------------------------to get month/Year i am using this querySELECT right(convert(varchar, fromdate, 106), 8) from tbl_x where id='11101' for the sum of hours SELECT SUM(CAST(hours AS INT))AS TOTALHRS FROM tbl_x WHERE id='11101'

How can I calculate the Number of Weekends between two dates

I need to be able to figure out the number of Saturdays and the number of Sundays between two dates.  I know that I can do this by looping through the series of dates between the start date and end date.  Years ago I saw a piece of code that an ex-co worker wrote that accomplished this task using a formula.  The formula was extemely effecient.  Unfortunately I no longer have access to this code.  Does anyone have an algorithm that will perform this calculation that I can make use of?  Thanks

How can I calculate the Number of days between two dates Monday to Friday


    I am using following code for Robert's Posted Code (Url:http://forums.asp.net/t/1142378.aspx) But this is for only calculate weekends saturday and sunday can any one tell me how 
I get Number of days between given two dates Monday to Friday.

private const int MONDAY = 1; private const int TUESDAY = 2; private const int WEDNESDAY = 3; private const int THURSDAY = 4; private const int FRIDAY = 5; private const int SATURDAY = 6; private const int SUNDAY = 7; /// <summary> /// Observe that the method calculates by including the start day, but excludes the end day /// </summary> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> private void GetWeekendDaysBetween(DateTime startDate, DateTime endDate,out int saturdays,out int sundays) { saturdays = -1; sundays = -1; if (endDate < startDate) return; TimeSpan timeBetween = endDate.Subtract(startDate); int weekendsBetween = timeBetween.Days / 7; sundays =

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.




MDX query - row number



I would like to have an incremental number for each row of data. I would get as output a column where the first row would have the value 1 and the last row would have the total of rows. Any ideas about how could I do this?
I'm using SSAS 2000, so I can't use the function CurrentOrdinal .

Thanks in advance for any help.

Sort query results by the number of characters matched



I have a query that takes a multi-word search string and parses it to spit out a LIKE (with AND or OR) condition that I then insert into my dynamic query.

Example of my parsing:

    'cheese chocolate cake'))),
                                ' ', ' þ')
                                , 'þ ',
                                    ''), ' þ'
                    , 'þ'),
                    '%'' OR CakeName LIKE ''%') + '%'')' 

Say for example that a user searches for the string "cheese chocolate cake". My parsing would result in the following string:

(c.CakeName LIKE '%CHEESE%' 
OR CakeName LIKE '%CAKE%')

What I wan to do is sort the results based on the number of characters that were actually matched. So, if c.CakeName contained the following:


  • Cheese Cake
  • Chocolate Cake
  • Chocolate Cheese Cake
  • Lemon Cake

The results would be sorted thusly:

  1. Chocolate Cheese Cake
  2. Chocolate Cake
  3. Cheese Cake
  4. Lemon Cake

Calculate database query value by using Math expression ?


I'm currently develop a monitoring and evaluation database with many indicators

For example: I have two input  indicator :  car , truck

Input_IndicatorID        quantity

Car                               3

Truck                            2

I'd like to create an output indicator have field: formular. The result of vehicle = car + truck = 5

Output_indicatorID      formular

Vehicle                         Car + truck

I don't now how to parse this string, detect parameter: car, truck  to get the answer

I search on Google and find some Math parse like:  muParser, bcnet  but it just for a string


Please help me solve this

How to get the number of running application instances?


I need a query that provides the current number of running instances of a client application.
The application is developed on VS2008 (.NET3.5, VB.NET) and accesses an Oracle 10g database via System.Data.OracleClient Here the view SYS.V_$SESSION yields all I need to get the current number of running applications (which is the same as the number of users logged in on each application instance).

I extended the application in order to access the database alternatively hosted on Sql Server 2008 R2 using System.Data.SqlClient The view sys.dm_exec_sessions seems to be an equivalent to Oracles SYS.V_$SESSION but actually it is not!:

  1. Instead of the application name, sys.dm_exec_sessions.program_name yields ".Net SqlClient Data Provider" which is useless information.
  2. After a while all session information is gone although the application is still running.

Generate a dynamic number of custom report item instances in a report


Are there any solid examples/tutorials/demos of how to dynamically generate custom report item instances in a report based on the amount of rows in a data set?

Additionally I would like to insert these custom report items in a particular order based on a field in the data set.

Any help/pointers/articles would be appreciated!

Query to Return Rolling 12 Month Totals



I have a table that looks like this:

[Date], [Person], [Value]

22/03/2008, Person A, 3.1

22/03/2008, Person B, 4.2

03/04/2008, Person B, 5.1

04/04/2008, Person B, 3.0

06/04/2008, Person C, 3.0

I need a query to return a table that shows the totals of each person's value for each month of the proceding 12 months from Date X.  So if Date X is 30/04/2008 then my result table would look like this:

[Month], [Total]

March, 7.3

April, 11.1

Thanks in advance.





sql query for month and year of date

I have a table with smallint columns for day,month,year. Now I want to select data lets say for month 10,year 2009 to month 2,year 2010. Can anyone help me in writing this query?
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