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

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

running total 5 million rows

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

What's the most efficient way to implement this for a table with 5 million rows?

I'm introducing a new field named "CumulativePayments" it should be populated using this formula:

CumulativePayments = Payment on current row + SUM(Payment) (for all previous rows where A+B+C+D+E is the same as on the current row)

A,B,C,D,E are all varchar fields

Payment and CumulativePayment are MONEY type

there is an identity column (id) that can be used to identify previous rows i.e. id is less than current row id

View Complete Post

More Related Resource Links

Show grand total running total in GridView Footer

In many scenarios we need to display Running total as well as Grand total in GridView footer. In this post i will try to explain in an easy way that how we can display running total & Grand total in a GridView footer combindly. To explain this solution using an example here i use a sales order report. The report contains all sales order amount in a tabular way. I will use a GridView to display sales order amount and use GridView footer to display Running total & Grand total. Let we have a customer table with id and name column plus an orders table with OrderID,CustomerID,OrderDate & Amount. Now our goal is to show all customers order with amount as well as page wise running total & grand total. Note that running total is necessary when you enable paging in a GridView where as Grand total is always you can consider.

Plot Running total charts from SSAS Cube

Environment: I am using SQL Server 2008, I have installed SSIS, SSAS and SSRS. Objective: Create a Effort V/S Defect plot in SSRS 2008 Approach: 1. Created the SSAS cubes with Effort and Defects data by timeline (rollup of data based on Week ending date) 2. Created SSRS project and linked the dataset to SSAS cube. But i am unable to plot a Effort (X-Axis) v/s Defects (Y-Axis). As soon as i drag the measures into the query designer in dataset properties, the effort rollup will break. Following is the data that has been created in SSAS. I need to plot a graph of Hours v/s defects (Running total). Please guide me on how to plot the graph using SSRS 2008 from the already created SSAS cubes Week Of Hours Defects 10/24/2009 8   11/7/2009 63   11/14/2009 68   11/21/2009 80.5   11/28/2009 139   12/5/2009 221.25   12/12/2009 131.75   12/19/2009 124.5   12/26/2009 61.5   1/2/2010 73   1/9/2010 153.5   1/16/2010 149.5   1/23/2010 196   1/30/2010 163   2/6/2010 155.5   2/13/2010 178   2/20/2010 138   2/27/2010 161.5   3/6/2010 189   3/13/2010 191.9   3/20/2010 240.5 9 3/27/2010 260.2 5 4/3/2010 214.5 13 4/10/2010 274.6 24 4/17/2010 200.5 15 4/24/2010 227 9 5/1/2010 237.05 18 5/8/2010 190.5 12 5/15/2010 156.25 6 5/22/2010

running total question

I've NET_SALES Table. it's very large table.   I'm selecting couple of fields to cmplete my task.   I need to rank all invoice total by customer for every sales rep.   So sales rep ABC will have 50 custoemrs. and 1 sales rep can sell more than 1 time to 1 customer. so for ABC, MonthToDate_Rank for custoemr AAA will be 1 and total will be 1000$                  MonthToDate_Rank for custoemr AAB will be 2 and total will be 900$  For Rep ABC1 MonthToDate_Rank for custoemr DEF will be 1 and total will be 1000$                  MonthToDate_Rank for custoemr DEG will be 2 and total will be 900$   I can do it with using ROW_NUMBER() OVER(partition by rep_name order by sum(total_sale) and I've where condition which will select values only for current month. I also calculate the same with QuarterToDate_Rank, YearToDate_Rank. but all are in different rows.   Now, there is new requirement that I've to calclate everything in 1 row. So ABC rep will have customer AAA with MTD_Rank =1 , QTD_Rank = 24 , YTD_Rank = 1000   I've to calcualate everything together. Any ideas?

SSAS for heavy network data to serve around 12-15 million rows per day


I am a fresher with the analytics.

Presently, working on a system which tracks the data from Network giants like Bing, Google, Yahoo, Facebook etc. depending upon the profile of customers using our system.

So we our tracking data of around 5-6 million rows in a day. Apart from that, dimensions like Search Keywords and all also contain data of around 50-60 million of rows. Although, daily addition will be low in dimension tables, but it will keep on increasing as customers will increase. Tracking data is expected to grow beyond 15 million.

To provide analytics, I must have minimum of 3 months of data in a cube. To provide trends, it should increase to around 1-4 years (in our future road map).

Presently, I am suffering with fatal issues while processing a cube, like, "The operation cannot be completed because the memory quota estimate xxxx exceeds the available system memory yyyy" - while processing dimensions

Please guide with some good thought process how to manage such a huge data and provide analytics over it. As well throw some light over the approach to follow for testing cubes and data loading packages of ssis.

Please provide the links of articles/videos/blogs & name of books to discuss and decide design the analytics for data-extensive systems. Any kind of reading mater

Add Total Problem while grouping and limiting number of rows in a page



I have a report in which first column is CITY and consists of 5 data (5 cities),all other columns are grouped w.r.t CITY, i.e in one city there can be 100s of other column data, last column of report is INCOME. If I right click on [INCOME] field and select ADD TOTAL then it gives citywise total, but I limited only 10 records per page and pagebreak  option is selected at end of the CITY group. When perform the same operation now it gives pagewise sum not the groupwise, please rectify my approach if I am wrong else suggest the solution.

Speed up Running Total MDX calculated measure?


Hi all, I'm using the follow mdx to keep a running total of the Period Balance measure in my cube:

SUM({[Due Date].[Date].CurrentMember.Level.Item(0):[Due Date].[Date].CurrentMember}, [Measures].[Period Balance])

It works great, however it's really slow as the amount of data displayed increases. I can't use a MTD or YTD because the users may be analyzing data that overlaps years. Any way I can speed this up?

Thanks in advance.

Would SSIS be faster than OPENQUERY? (Over 100 Million rows)


I have a stored procedure taking over 2.5 hours (still running) getting a huge table from one sql server to another across a robust network. I don't know why it is taking so long or if there is a better way, like SSIS. I did try SSIS and it would just stop after bringing over about 2/3 of the records. I have tried this in several scenarios. The huge tables quit with over half the records transferred. Hence I tried this, but it hasn't finished yet. I need a good, reliable way to transfer huge tables. My T-SQL code is below. If SSIS is the way to go, please try to give me some tips on configuring it. Thank you.

CREATE PROCEDURE [dbo].[Populate_SoftwareInventory_Table] AS

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[staging].[SoftwareInventory]') AND type in (N'U'))

Adding Dynamic Rows in ASP.NET GridView Control with TextBoxes and with Delete functionality

In my previous examples, I have demonstrated on how to add dynamic rows in GridView control with TextBoxes and how to save the values into the database. Now, seems that most of the developers are asking if how to add a delete functionality with it. So in this example, I'm going to show on how to delete a certain row in the dynamic GridView with TextBoxes.

jQuery: Highlight Gridview Rows with Checkbox

Im learning and experimenting with jQuery and ASP.NET. Below is a simple solution I created to highlight rows in a gridview by clicking on a checkbox next to the row.

Gridview setup:

Highlighting Rows with TextBox OnFocus

In another article I explained that how you can make the rows of the GridView control clickable and highlight them when they are clicked. In this article I will explain that how you can highlight the GridView rows when you focus on the TextBox which is contained inside the GridView control.

jQuery: Highlight Gridview Rows with Checkbox

Im learning and experimenting with jQuery and ASP.NET. Below is a simple solution I created to highlight rows in a gridview by clicking on a checkbox next to the row.

jQuery: Styling DropDownList Alternating Items (Rows)

In the following example, I will show how you can style any asp:dropdownlist very easily.

The example demonstrates how you can change alternating row items background color, using the fadeTo() to add a nice touch in the dropdownlist. You can change alt rows with a couple lines.

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 format and update GridView and DataGrid rows using JQuery

The behavior described in this question is as expected. When you set text of a cell in grid, it directly affects HTML that is going to be rendered. When you set text value of a cell, it means that you are setting innerText of the cell. The column that GridView creates for command fields (Edit, Delete and Select) are a (anchor) or button elements. So you can see what will happen if you set text value in that cell. It will wipe out those link or button controls and replace them with simple text string.

Using jQuery to Filter Table Rows

The project is using the .net GridView control, so I had limited control over the output HTML code. Still, I think this code can work for most tables. One thing to notice: you should use the class "filterable" on your table or on one of its parents for the code to work.
First, we need a text box:

Insert Rows with a GridView

In ASP.NET version 1.x, I used the footer on the DataGrid control to insert new rows by placing a series of TextBox, DropDownLists, CheckBoxes etc. controls on it and then handling a save button. This also works with the GridView in version 2.0 but with one major exception: if your data source is empty, the GridView will hide your footer (and header) and only display the EmptyDataText.

Problem to group radio button across gridview rows

In general no one can easily group or make a single selection from radio button list inside gridview rows. There are a lot of way to make a single or unique selection from list. Here i want to share how i can address this problem in the most easiest way. Googling the problem most of the cases i saw the grouping in horizontal way. Thats why i tried to group the radio buttons in vertical manner. Let i have a requirement like below:
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