.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


Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
We have the following query:   SELECT H.VEHICLE_ID,                 COUNT(ISNULL(H.HISTORY_ID, 0))            FROM VEHICLES V LEFT OUTER JOIN VEHICLE_HISTORY H              ON V.VEHICLE_ID = H.VEHICLE_ID           WHERE H.SPEED >= 65             AND CONVERT(DATE, H.GPS_TIME) = CONVERT(DATE, DATEADD(DAY, -1, GETDATE()))        GROUP BY H.VEHICLE_ID;   Unfortunately, if there inst a record for a vehicle for the current day in VEHICLE_HISTORY, or there inst a record over 65 in VEHICLE_HISTORY, the query simply does not include the result. How can we include all results (i.e. return 0)? What we want is the number of rows returned to match the number of rows in the vehicles table. I thought doing the LEFT OUTER JOIN would take care of this, and return a result for all vehicles.   Thanks.

View Complete Post

More Related Resource Links

Count Query Execution


I have a class that being used to connect with the DB. Now I want to count how many times each web request executes the queries, but I've no idea where to store the counted value. I mean, Session wont, ViewState wont work as site also have webservices. What else I can use ?

Dynamic query for Null field checking

Hi I want dynamiclly find the table to null values finding.. Please give me quickly   Regards Ram

Dynamic query for Null field checking

Hi I want dynamically check the data Formation query in SQL server please  provide me the query

tfs mdx query(cumulative count)

I need cumulative count of bug,risk work item per day first, i made this query WITH MEMBER [Measures].[Date Value] AS     [Date].[Date].CURRENTMEMBER.MEMBER_VALUE   MEMBER [Measures].[Active Cumulative Count] AS ([Date].[Date].CURRENTMEMBER, [Work Item].[System_State].[System_State].[Active], [Measures].[Cumulative Count])   MEMBER [Measures].[Resolved Cumulative Count] AS ([Date].[Date].CURRENTMEMBER, [Work Item].[System_State].[System_State].[Resolved], [Measures].[Cumulative Count])   MEMBER [Measures].[Closed Cumulative Count] AS ([Date].[Date].CURRENTMEMBER, [Work Item].[System_State].[System_State].[Closed], [Measures].[Cumulative Count])   SELECT {     [Measures].[Date Value],     [Measures].[Active Cumulative Count],     [Measures].[Resolved Cumulative Count],     [Measures].[Closed Cumulative Count] } ON COLUMNS, (     Filter(         [Date].[Date].[Date],         [Date].[Date].CURRENTMEMBER.MEMBER_VALUE >= CDate(@StartDateParam) AND         [Date].[Date].CURRENTMEMBER.MEMBER_VALUE <= CDate(@EndDateParam)     ) ) ON ROWS FROM [Team System] WHERE (     {[Work Item].[System_WorkItemType].&[Bug],[Work Item].[System_WorkIt

Problem with SELECT COUNT query and parameters

Hello!I have a problem with SELECT COUNT query in ASP.net. I want to create CMS with articles which have categories (which have the option to be deleted). The problem is that I want to get the number of articles within the specified category so if there aren't any articles with the specified category I can proceed with the category deletion.I have the following code:protected void Page_Load(object sender, EventArgs e) { } protected void GridViewKategorije_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "Uredi") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow odabraniRed = GridViewKategorije.Rows[index]; TableCell ClanakID = odabraniRed.Cells[2]; string ID = ClanakID.Text; Response.Redirect("/Portal/Administracija/Kategorija.aspx?idKategorija=" + ID); } else if (e.CommandName == "Obrisi") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow odabraniRed = GridViewKategorije.Rows[index]; TableCell KategorijaID = odabraniRed.Cells[2]; String connString = WebConfigurationManager.ConnectionStrings["CMS"].ToString(); SqlConnection conn = new SqlConnection(connString); conn.Open(); using (SqlC

MDX query to count non empty descendants


I have a calculated measure with the following definition,

When [Admit Date].[Date Hierarchy].currentmember.level is
        [Admit Date].[Date Hierarchy].[Quarter]
Then [Measures].[Members] /3

Instead of hard-coding the division value, I want to count how many non-empty descendants [Admit Date].[Date Hierarchy].[Quarter] has and divide the measure by that number. I wrote some Query like this, but didn't give me any result.

When [Admit Date].[Date Hierarchy].currentmember.level is
        [Admit Date].[Date Hierarchy].[Quarter]
Then [Measures].[Members] / COUNT(NONEMPTY(DESCENDANTS([Admit Date].[Date Hierarchy].[Quarter])))

How should I correct this query?




how to count transactions done on your database by T-SQL query?


Dear All,

I have multiple database and I want to know how many transaction that are happening per database using T-SQL, can anyone help me?





Karim Alaa

Showing Null value in a string expression to show fullName in T-SQL query



I have a SQL query which calculates the full name from firstname, middlename and lastname. When any of the column contains a null value the calculated value shows null.


FirstName + ' ' + MiddleName + ' '+ LastName as FullName

from <table name>

FirstName : Ruben

MiddleNAme : Null

LastName : Torres

FullName : Null

Why it does not leave the null column and have the string concatanated as :

Ruben Torres




displaying the word label in asp:label if the query is null


how to disabled the word label in my aspx page if my query is null... i dont like to show the word label in my aspx page.

im using c# .net

dynamic query with a null parameter to dont take in cosideration if null


Hi guys,

Do you know in a dynamic query how can I manage if a parameter isnull or not.

declare @businessunitidname varchar(15)
set @businessunitidname = NULL;
--print @businessunitidname

declare @eu_reporthubname varchar(15);
set @eu_reporthubname = Null;
declare @ownerid varchar(15);
set @ownerid = Null;

declare @SQL varchar(4000);

SET @SQL = ';with cte as
	select ''eu_mondayamid'' as WeekName,0 as WeekVal union all
	select ''eu_mondaypmid'',0 union all
	select ''eu_tuesdayamid'',1 union all

Outputting a Null as a '0' in an SQL Query


I've got a table (TABLE1) that looks like this:




xxxxxxxx 1 GOOD 201

xxxxxxxx 2 BAD 223

xxxxxxxx 2 GOOD 345

xxxxxxxx 3 GOOD 765

xxxxxxxx 3 UNKNOWN 201




And I would like to write a query for Crystal Reports to produce an outlike like this:


1 xx xx &n

query - generate month, year (count) for blog archive


hi guys.

from my sql blogs table, I want to generate blog archive section on my master page. like;

january, 2010 (20)

fabruary, 2010 (35)

I cannot come up with a linq to sql query on this. do you have any idea how it can be done ?

Linq to XML query doesn't show data with null values...need it to!


I've got a web service that returns xml from a linq query.

If i have fields 'name' and 'title' my linq query will return results that have data in both 'name and 'title' fields.

How can I get linq to return a query when both fields are populated, and when only the name field contains data?

Here's what I've got:

        public XmlDocument GetUsersByDate()
            StringBuilder sb = new StringBuilder();
            XmlWriter writer = XmlWriter.Create(sb);


            var SelectedUsers = (from u in db.Users
                                 select "<UserName>" + u.UserName + "</UserName><x>" + u.Title + "<x>").ToList();


Problem to write a query..using COUNT()...


I am facing problem to get desired output..

i m using query as written below.........

SELECT  studentprofile.RollNo, AttendanceMaster.ScholarID, studentprofile.StudentName 

COUNT(AttendanceMaster.AttendanceStatus) AS Expr1

FROM         AttendanceMaster AS AttendanceMaster INNER JOIN
                  studentprofile AS studentprofile ON                    AttendanceMaster.ScholarID=studentprofile.ScholarID


(studentprofile.Section = 'A')

AND (studentprofile.Class = 'Nursery')

AND (studentprofile.SchoolCategory = 'PRE SCHOOL')

AND (studentprofile.SchoolType = 'XYZ School')

AND (AttendanceMaster.AttendanceStatus = 'Present')

AND (AttendanceMaster.Date BETWEEN '11/1/2010 12:00:00 AM' AND '11/30/2010 12:00:00 AM')
GROUP BY studentprofile.RollNo,AttendanceMaster.ScholarID, studentprofile.StudentName

this query gives output propery for those students who comes(AttendanceStatus='Present') atleast ne day........but unable to show the the re

caml query - group by column and get count of records



I want to group by on column name and get the count of records returned.



SQL query nested use of count and join


Here is the problem/issue:  Table A can have multiple record associations to Table B.  Table B has a 1:1 record relationship with Table C.  Table C can have multiple record associations to Table D.  I want to find all the records in Table A that have exactly one entry in Table B, which have only one entry in Table C (which is always true as noted above), which have 0 or 1 entry in Table D.

Here is what I have now:

use Database
Select *
From TableA as TA
Where TA.MedRecID In
    select TableB.MedRecID
    from TableB,TableC
    where TableB.AccountSourceIdentifier In
        select TableC.IncidentSourceIdentifier
        from TableC, TableD
        where TableC.IncidentID In
            select TC.IncidentID
            from TableC as TC
                left outer join TableD as TD

SQL query to get count of fields



I have a table like this




Third Approval



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