Decrement Total In One Table Based On Value In Another
Oct 17, 2014
I have an procedure that uses a cursor to decrement a total in one table based on value in another. I am trying to rewrite this to be set based but having some issues getting the correct values.
one table will have a total amount available and the other table will have line items for the amount to decrement (think amount left on gift card). If the total amount remaining is less than the line item amount then line item amount should be set to total amount available with total amount set to zero.
this last part is where I am having trouble.
Here is example table
CREATE TABLE qamt(id INT, amt INT);
CREATE TABLE qtot(id INT, tot INT);
INSERT INTO qamt
VALUES(1,10),
(1,20),
(1,30),
(2,90),
[Code] ....
In the end I would want to update the qamt and qtot tables. I basically want to accomplish a quirky update across multiple tables.
I want to create a report which should be looked like this.
Members Joined
Members Borrower Depositor
Members Joined Yesterday 8 4 4
Members Joined Last 7 days 39 20 19
Members Joined Last 30 Days 243 120 122
Members Borrower Depositor
2007 Members Joined to Date
1,052 510 542
Q1-07
415 200 215
Q2-07
445 210 235
Q3-07 (To date)
192 100 92
All Members Joined
1,052 510 542
In the data base, there are some tables called members, loans and deposits containing members names, joined date , etc.
Example:
MembersTable MemberName MemberID etc
LoansTable MemberName CommencedDate
DepositorTable MemberName CommencedDate
I assume that I should be able to create a report using matix format. But I want to clarify few things before I start. How do I calculate nu members? Data base tables contains only names of the members but not numbers. Other thing is how can I group members by perticular time periods as shown above? Do I need to use parameters? Also In the table, members can be borrowers or a depositors? How can clarify this one?
Please Can anyone give an idea? How to start and which direction I should go etc?
I have a set of revenue records where there is a UNIT column and a REVCHARGE column. What I need to do is breakout the records into single records where the unit count is > 1 and calc the actual charge:
Ex:
Units REVCHG FIELD_A FIELD_B ..... 3 3.00 ABCD EFGH
What I need to do it select the top 80 percent of records per group based on the group total. To be clear I am not trying to just grab the top x percent of rows.
Table 1 has the total number of repair orders per dealer. This can be obtained by simply grouping on DealerID and counting the number of RepairIDs.
Table 2 has information on some of the repair orders and it is needed to select the top 80% of tire sizes. This table will be joined to Table 1 and grouped by DealerID and Tire.
I am trying to calculate the the running total but also tried to reset to reset to zero based on a value of a column.
here I am trying to calculate the value of CalcVal column based on column Flag value...actually it is running total but it reset to zero if Flag value is 0.
Here is the example of data along with required column
I would like to get some advises on how creating some kind of reports with Report builder
Lets say i have these two tables with these columns
Table Product
ProductName
Table ProductInstance
Product ID CreationDate CompletedDate
I would like to be able to create with report builder a report that looks this
Start Date : 2008/10/01 - End Date : 2008/10/30
Total Total Total In Out OutStanding ProductName1 10 0 3 ProductName2 5 5 2 ProductName3 8 8 5 ProductName4 12 5 6
Total In : total number of product created between StartDate and EndDate Total Out : Total number of product completed between Start Date and End Date Total OutStanding : Total number of product that have not yet been completed.
Any suggestion and advise on how to achieve this ?
In report builder 3.0 I have row groups. I want a total at the end of each row but I want the total to be broken down by 3 columns based on 3 possible values of a field in the dataset. The report expands as the date range entered is increased. I want the total of clinic id + service id + program id + protocol id + appointment date but I want the total column to be broken down by appts that have shown or not shown or canceled. Â
See screenshots below for seeing how I have it configured. Is this possible? I have tried every combination of possibilities but I keep getting the row total in each of the 3 columns comprised of the total column.
and
The results look like:Â
The last Total column displays the entire row count NOT separated by the show, no show, and cancel status'.  I have tried filters and different expressions but keep getting the same output. Is this even possible?
I am creating a query that shows the consumption of stock against Manf Orders (M/O) and struggling on the last hurdle. I am having difficulties calculating a running total based on an Opening Balance. The first line returns the correct results but the following lines do not. I have tried other variants of the "Over Partition" but still no joy?
SELECT CASE WHEN ROWNUMBER > 1 THEN '' ELSE A.Component END AS Component , CASE WHEN ROWNUMBER > 1 THEN '' ELSE A.SKU
Table B: Name Data UserA xxx UserB asdasd UserB ewrsad UserC dsafasc UserA sdf UserB dfvr4
I want to count the total entries in Table B for every user in Table A. The output would be:
Name Count UserA 2 UserB 3 UserC 1
I can use a Select Count statement, but I will have to make a SQL call for every user in Table A. Also, Table A is dynamic, so the users are always changing. Can this be incorporated into one SQL call to count the total rows in Table B for each user in Table A?
Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.
Very new to SQL and trying to get this query to run. I need to sum the total trips and total values as separate columns by day to insert them into another table.....
My code is as follows;
Insert Into [dbo].[CombinedTripTotalsDaily] ( Year, Month, Week, DayNo, Day, Trip_Date,
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.
Hi, I am getting this error when I try to run a script to create a table. The error reads as below
The total row size (12488) for table exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.
Can somebody tell me what I have done wrong? thanks zoey
Hi, i have an assets table with a purchase cost column and an updates table with an update cost column.
is there any way for me to add a total cost column to the assets table that will automagically add the purchase cost to the sum of the updates costs for each asset so that when queried, the total cost column can be returned with no calculation in the client?
am i looking at creating a view with the calculation in it?
If so, how do i define the total cost column please?
I have a report that looks like below. It's grouped by Product then by Year. I want to include within the Product grouping and item called "All products". If I swapped order of Grouping to Year then Product, I could simply add a SUM in the group header, but the users don't want it displayed that way round. Any suggestions for "All products" ? Thanks Richard
I have created a report as shown below using OLTP tables.
No Description CA PA Variance
10101 Bank of Tampa 388353.69 208048.92 87%
10104 Wachovia Operating 275933.37 - 0%
10105 Wachovia Payroll 217.95 - 0%
10201 Accounts Receivable 16735.60 34829.14 -52%
20002 Account Payable -175919.72 -106018.68 66%
Now I want to insert total in the middle of the table, like I want to display: Total of CA when No in (10101,10104,10105) as 'Total Cash' then I want to display total of 'Total Cash' + 'Accounts Receivable'(10201) as 'Cash & Receivables'
I have a view that contains loan numbers and their current balances. I have been assigned to a task of inserting the first 30 million dollars worth of loans from that view into a table. Is there a way to take a running sum of the loans I am inserting into the table and stop inserting when that sum hits 30 million?
I have created a Report using Visual studio-the report displays a subreport within it.
On the Subjective Report I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value), and I have named this text box €™SubRepM1€™ The name of the subreport is €˜subreport1'.
On my Main Report, again I have 12 values for each month of the year. For the first month the value is =sum(Fields! Month_1.Value)*-1, and I have named this text box 'MainRepM1' The name of the main report is 'GMSHA Budget Adjustment Differentials'
The report displays both of the subreport and main report values but I now need to total these values together for each month in order to produce a grand total.
I have tried using the following to add the totals for Month 1 together, =subreport1.Report.SubRepM1 + MainRepM1 but this does not work and I get the following error message €˜The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.
I feel that it should be a simple matter of adding the two sets of values together but I€™m having major problems trying to get these totals to work.
Have a table where I need to update a field with a running total.
Table OrderRebate I need to calculate a running total on ext_rebate for each order number. So I need the running total to reset to zero when the order number changes.
I've got as far with the code below but it never resets. Does a running total for every record.
DECLARE @runningtotal decimal(16, 6) SET @runningtotal = 0 UPDATE OrderRebate SET @runningtotal = RunningRebateAmt = @runningtotal + ext_rebate WHERE ord_no = ord_no
I want to add a new column in a table. In this column I want to include a Total Value. This Total Value exists already for dates after Sept 23, 2013. For dates before Sept 23, 2013 the total can be calculated using the following math:
Total = Total(t-1) - (Resource_Name1-Resource_Name1).
The Resource_Name column contains the Total field (after Sept 23) as well as the Resource_Name1 field. There is a third column called Direction which contains the values injection or withdrawal. The Resource_Name1 differes in value depending on whether it is injection or withdrawal. How do I create a new column with Totals for the full data set?
Like all location details stored from all months in these table
here Dr=debit,Cr=Credit Formula= 'Dr-Cr' to find the salary wavges of amount
so i made the query to find the amount for may
select fs_locn, fs_accno, amount=sum(case when fs_accno like 'E%' and fs_tran_type='Dr' then fs_amount when fs_accno like 'E%' and fs_tran_type='Cr' then fs_amount * -1 end ) from accutn_det where fs_trans_date between '01-may-2014' and '31-may-2014' groupby fs_locn,fs_accno
now i need the sum values of all costcenter for the particular account.how to do that?
I would like to know the total length of data type in a table. I ran the following query. Will this give me the correct information? I also ran sp_columns <table name> and it too give the length. But There is a difference in the numbers. Am I doing something wrong and which is the correct the query or sp_column.
select sum(length) from syscolumns where id in (select id from sysobjects where name = 'XYZABC')
I have a question regarding the total count of the table rows
Select count (name) from test. Lets say I have got 200 count. And Select count (lastname) from test1.I have got 200.And this counts I should store it in "There are nn name items awaiting your attention and nn pending lastname's awaiting your approval".
I need to show the total amount of rows in a specific table?
The query is as follows:
As part of the planning process to expand the database that supports Northwind operations, the IT manager would like to know how many rows are currently in specific tables so that he can conduct capacity planning.
The results needed include two columns, TableName( containing all the tables in the database and Rows, which contain the total amount of all the rows per table).