.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

tfs mdx query(cumulative count)

Posted By:      Posted Date: September 18, 2010    Points: 0   Category :Sql Server
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

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 ?


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.

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

Trouble working examples of Cumulative columns in SQL Query


I can run the following query without the built in query in the select statement and it works fine.  All i am trying to do is to create a cumulative total for each day for a specific date range.  Any ideas?


SELECT OrderHed.OrderDate,

     (SELECT SUM(OD.OrderQty * OD.ListPrice)

        FROM   MFGSYS.PUB.OrderDtl OD INNER JOIN MFGSYS.PUB.OrderHed OH ON (OD.Company=OH.Company) AND (OD.OrderNum=OH.OrderNum)

        WHERE OH.orderdate<=OrderHed.OrderDate)

 FROM   MFGSYS.PUB.OrderDtl OrderDtl INNER JOIN MFGSYS.PUB.OrderHed OrderHed ON (OrderDtl.Company=OrderHed.Company)

    AND (OrderDtl.OrderNum=OrderHed.OrderNum)

 WHERE  (OrderHed.OrderDate>={d '2010-10-01'}

    AND OrderHed.OrderDate<={d '2010-10-31'})

    AND OrderDtl.Company='HEAPG'

    AND OrderDtl.RMANum<1

 GROUP BY OrderHed.OrderDate

 ORDER BY OrderHed.OrderDate

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 ?

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



MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

sql query that don't run



I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)

Need help with a linq to sql query


I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:

Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5

Print row count on Label



I have an SP that simply performs a COUNT(*) from a table. I'm am trying to print that number in an ASP Label. It works in Query analyzer but I cant wrap my head around how to display it in a label. Any guidance much appreciated. 

Thank you. 

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