Calculate Cumulative Amount From Table
Sep 2, 2014
I need to calculate cum amount from the following table.
CREATE TABLE #TotalRevenue_Investments
( [Month] INT,[Year] INT,TotalRevenue INT,Descr VARCHAR(100),Company VARCHAR(100))
INSERT INTO #TotalRevenue_Investments
( Month ,
Year ,
TotalRevenue ,
[Code] ....
AND so ON ..
I need the OUTPUT AS FOR example
SELECT 1 AS Month,2014 AS Year,12 AS cumAmt,'Late Sales' AS Descr,'US Late Sales' AS Company
View 3 Replies
ADVERTISEMENT
Aug 2, 2014
How to calculate the cumulative of months.
Jan FebMar AprMay JunJul AugSep Oct Nov Dec
321 394571 577 617 692924 944956 1010 1308 1686
if i execute my query in this month(getdate) then it should sum from Jan to Aug Similarly i execute same query it should sum from Jan to Sept So on.
View 1 Replies
View Related
Feb 6, 2015
I want to show cumulative numbers, but don't know how to calculate them.
Here is an example of the source and the wanted result:
Source:
[Week] [Count]
1 15
2 5
3 6
4 10
(until 52)
Result:
[Week] [Count]
1 15
2 20
3 26
4 36
Is this possible, and how?
TestData:
USE TestDb /*SqlServer 2005*/
CREATE TABLE Test(
[Week] [int] NOT NULL,
[Count] [int] NOT NULL
)
GO
INSERT INTO Test ([Week], [Count]) VALUES (1, 15)
INSERT INTO Test ([Week], [Count]) VALUES (2, 5)
INSERT INTO Test ([Week], [Count]) VALUES (3, 6)
INSERT INTO Test ([Week], [Count]) VALUES (4, 10)
View 8 Replies
View Related
Apr 10, 2014
I have a query that calculate the total amount of order details based on a particular order:
Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID
My question is what if I wanted to create a formula to something like:
UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount
Do I need to create a function for that? Also is it possible to have m y query as a table variable?
View 7 Replies
View Related
Aug 29, 2007
For Eg.
I have a table like gias given below:
Name Amount
------------------------------
aaa 10
bbb 20
and I want an output like one given below on running SQL query or stored procedure
Name Amount cumulative amount
---------------------------------------------------------------------
aaa 10 10
bbb 20 30
can anyone plz help me on this
View 28 Replies
View Related
Dec 18, 2013
How can I update a table to have a running cumulative sum?
For Example:
Update Table1 Set Cumulative_Sum = Row_Sum + Previous_Row_Sum
It should look something like this:
Row_Sum Cumulative_Sum
1 1
2 3
3 6
4 10
View 6 Replies
View Related
Nov 13, 2007
Hello,
One more question about this Custom Calendar table I'm creating. I have a column called "IsWorkdays" which indicates if the day represented by a row is a workday or not. For our purposes, I also need to create a row that accumulates those numbers by month. So, if it is the 3rd workday of the month, this column would have a 3. This is beyond my current T-SQL ability. Does anyone know how to do this?
Thanks a lot,
Andy
SQL version: 2005, Standard edition.
View 1 Replies
View Related
Sep 10, 2012
I have a table with AmountSold and AmountLeftWith. I have to buy from the customers until the amount bought =250,000.
The max that user can buy is 250,000 so customers 1-3 get left with 0 (AmountLeftWith ) and customer 4 with 577 (AmountLeftWith ) after the update as user couldn't buy the entire amount as it would have exceeded 250,000. Preferably the query should stop afterwards and not proceed to check the other customers.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#tmpCustomerAmount','U') IS NOT NULL
DROP TABLE #tmpCustomerAmount
CREATE TABLE #tmpCustomerAmount (
[id] [int] IDENTITY(1,1) NOT NULL,
[Code] ......
View 3 Replies
View Related
Mar 29, 2008
Hi Guys,
Does anyone have a good example how to subtract an amount from an sql table?
At present i have a table such as this
id
View 2 Replies
View Related
Aug 1, 2006
Hi
Just a quick question taht I hope someone can answer, What it the total amount of trigger per table?
I understand that it is better to have as least as possible for performance
Thanks Rich
View 5 Replies
View Related
Mar 18, 2008
I'm creating a temporary table in a Sql 2005 stored procedure that contains the transaction amount entered in a period <= the period the user enters.
I can return that amount in my result set. But I also need to separate out by account the amounts just in the period = the period the user enters. There can be many entries or no entries in any period. I populate the temporary table this way:
SELECT
t.gl7accountsid,
a.accountnumber,
a.description,
a.category,
t.POSTDATE,
t.poststatus,
t.TRANSACTIONTYPE,
t.AMOUNT,
case
when t.transactiontype=2 then amount * (-1)
else amount
end as transamount,
t.ENCUMBRANCESTATUS,
t.gl7fiscalperiodsid
FROM
UrsinusCollege.dbo.gl7accounts a
join
ursinuscollege.dbo.gl7transactions t on
a.gl7accountsid=t.gl7accountsid
where
(t.gl7fiscalperiodsid >= 97
And
t.gl7fiscalperiodsid<=@FiscalPeriod_identifier)
And poststatus in (2,3)
and left(a.accountnumber,5) between '2-110' and '2-999'
And right(a.accountnumber,4) > 7149
And not(right(a.accountnumber,4)) in ('7171','7897')
order by a.accountnumber
Later I create a temporary table that contains budget information. I join these 2 temporary tables to produce my result set. But I don't know how to get the information for just one period. For example, if the user enters 99 as the FiscalPeriod_identifier, I need a separate field that contains only those amounts(if any) that were entered for each account in Period 99.
Can anyone help? It may be that I am not seeing the forest for the trees, but I can't figure it out.
Thanks very much.
Sue
View 6 Replies
View Related
May 18, 2001
I need to delete data from a particular table which has more than half a million records. The data needs to be deleted is more than 200,000 records from the table. What is the best way to delete the data from the table other than importing into a temporary table and performing the same operation?
Let me know if the strategy to be followed is okay.
1. Drop all the triggers
2. Drop all the indexes
3. Write a procedure with a loop setting ROWCOUNT to 1000 and delete the records. ( since if I try to delete all the rows it will give timeout error )
The above procedure will delete 1000 records for each batch inside the loop till it wipes out all the data for the specified condition.
4. Recreate Indexes and Triggers.
Please let me know if there are any other optimal solution.
Thanx,
Zombie
View 2 Replies
View Related
Oct 31, 2006
Hi all,
I have a 2 main tables in my system. One is main table has ticket information location and so on. And detail table which hold all actions on that record with multiple money fields (its around 5 fields). it goes trough some crazy calculations on the detail table and get 5 results for each ticket. And this calculation is done everytime the ticket is looked at.
There is around 10 detail records per 1 main record. and the main table is around 7 mil. records
Now i got 2 options. I can create a trigger that does the calculation and update the main table show the results from there. This is going to affect all updates deletes and inserts.
OR
I can leave as it is so only when they view the ticket it does the calculation. Dont ask me how often they view it i dont have a clue.( if there is any suggestion how to get the count i could try to do it. )
:confused:
Suggestions
(My attitude is going towards the trigger way but 100 percent sure)
View 4 Replies
View Related
Jun 20, 2007
I have a table with 35,000 records in it. I want to update a value in column A for only the first 5000 records, leaving the value in Column A for the remaining 30,000 records as it is now. What would be the command I would use to update Column A for the first 5000 records.
Thanks,
View 4 Replies
View Related
Feb 28, 2008
I am wondering if it€™s possible to lock a SQL table for specific amount of time, say 5 min.
There is a particular 'Phone' table on the database that should never get locked. Yet, during the development stages we have noticed that the table gets locked at time. The issue since has been resolved to the best of our ability, but, there is still a vague chance that the table can get locked due to the multiple jobs that query the table, when we go live.
If such a situation occurs, we just want to be able flip the switch that will send the server to the mirror mode and the previously mirrored database to become the principal.
So, I just want to recreate a situation by voluntarily locking the table.
Any help in this regard will be appreciated.
View 6 Replies
View Related
May 20, 2012
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).
View 4 Replies
View Related
Feb 1, 2008
A friend reminded me of a problem we tried to solve a few years ago and were unsuccessful. Below is a copy of the email he sent me. We would very much appreciate any ideas from the community. Thanks!Lets start with a simple schema where you have 4
tables:
View 3 Replies
View Related
Dec 21, 2005
In
one of our forth coming projects, with ASP.Net/C#/MSSQL Server, We have
to deal with a Business table having about 15 millions of records. We
want to know, that which methodologies should we adopt, both regarding
front end and back end perspective, so the site could give optimised
performance. Also in place of a Dedicated Server, the Hosting Company
provides MSDE (that come with .net). Will this create any problem with
this project, that have such a huge table? Should we go for some
advanced database technique, such as, Clustering, Spliting Tables, etc.
Followings are the fields that the business table contains:
ID, Category ID (which comes from a Category table, each business is
under a category), BusinessName, SignupDate, Address1, Address2, Phone
Number,
Hours Of Operation, Years in Business, LicenseNumber, DiscountCoupon, Website
View 3 Replies
View Related
Jul 20, 2005
I have a combo box where users select the customer name and can eithergo to the customer's info or open a list of the customer's orders.The RowSource for the combo box was a simple pass-through query:SELECT DISTINCT [Customer ID], [Company Name], [contact name],City,Region FROM Customers ORDER BY Customers.[Company Name];This was working fine until a couple of weeks ago. Now wheneversomeone has the form open, this statement locks the entire Customerstable.I thought a pass-through query was read-only, so how does this do atable lock?I changed the code to an unbound rowsource that asks for input of thefirst few characters first, then uses this SQL statement as therowsource:SELECT [Customer ID], [Company Name], [contact name],City, Region Fromdbo_Customers WHERE [Company Name] like '" & txtInput & "*' ORDER BY[Company Name];This helps, but if someone types only one letter, it could still bepulling a few thousand records and cause a table lock.What is the best way to populate a large combo box? I have too muchdata for the ADODB recordset to use the .AddItem methodI was trying to figure out how to use an ADODB connection, so that Ican make it read-only to eliminate the locking, but I'm striking outon my own.Any ideas would be appreciated.Roy(Using Access 2003 MDB with SQL Server 2000 back end)
View 2 Replies
View Related
Aug 10, 2006
Hi all,
I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .
I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.
I kindly request for help.
Thanx in advance.
Ronnie
View 4 Replies
View Related
Oct 10, 2001
Hi all!
How can I calculate the combined table size for the following:
The Destination and the ShipmentWages tables on a database of a courier service have approximately 10,000 rows and 15,000 rows, respectively. The average row size of the Destination table is 4KB and that of the ShipmentWages table is 3KB.
Thanks,
ndba
View 1 Replies
View Related
Mar 13, 2014
create table cust_details
(
id int ,
city varchar(20),
cust_name varchar(20),
sales int
)
insert into cust_details
values('1','Tamilnadu','101','500'),
values('2','Tamilnadu','102','300'),
values('3','Pondi','103','200'),
values('4','Pondi','104','100')
My expecting o/p:
-----------------
city customer sales
tamilnadu 101 500
102 300
------
total 800
-------
or
customer sales
101 500
102 300
total 800
Like I wanna display separate location?
View 2 Replies
View Related
Jan 29, 2008
this is the table i have created .
CREATE TABLE [dbo].[Table] (
[BCode] [varchar] (3) ,
[RefNo] [varchar] (12) ,
[RType] [varchar] (2) ,
[TheirRef] [varchar] (50) ,
[Amount] [money] NULL ,
[AccountNo] [varchar] (20) NULL ,
[EnteredDate] [datetime] NULL ,
[EnteredBy] [varchar] (6) ,
[IsTrue] [varchar] (1) ,
[RDate] [datetime] NULL ,
[Details] [varchar] (255) ,
[PostBy] [varchar] (6) ,
[SeqNo] [int] IDENTITY (1, 1) ,
[ThisCode] [varchar] (3) ,
[VDate] [datetime] NULL ,
[Id] [varchar] (20) ,
[Flag] [varchar] (1) ,
[TDate] [datetime] NULL ,
[Type] [char] (1) ,
[SerialNo] [int] NULL
) ON [PRIMARY]
GO
if i insert a single row in a table with values :
INSERT INTO [Table]
SELECT '100','1','DD',1000000,'999999000',
'2008-01-29','sa','F','2008-01-29','PARTIAL DATA','11502',
'101','2008-01-29','ABC1111111','T','2008-01-29','S','11204'
EXECUTE sp_spaceused 'Table'
name | rows |reserved | data |index_size | unused
[Table] | 1 |16KB | 8KB | 8KB | 0KB
i couldnot understand
i) how the system calculate this 16 KB and 8 KB ?
ii) if i changed the VARCHAR(255) to VARCHAR(50) ,will there be difference in size of the table ,if so then how ?
iii) how can i see the change in size of table by reducing the size of datatype ?
can anybody help me ?
View 3 Replies
View Related
Mar 10, 2008
I'm trying to grab the Total for each "brand" in my table group (this is not in a matrix).
Period 1 Period 2 Period 3
Sensible Choice 2007 3843 4020 4746
2008 1830 3352 1025
Total: 1830
Maxi 2007 124388 132248 162429
2008 127729 194458 13732
Total: 127729
I want to make the total the same amount as the 2008 total per period. (everything in yellow is the group 1 (outer group and the red is the inner group. When i tried to get the value for the year 2008 and put it in the total field, i put Last(Fields!PeriodTotal.value), but it doesnt work, because sometimes it gives me the 2007 total, and sometimes it gives me the 2008 total. I dont know why. Any suggestions on how i can get the 2008 value everytime.
View 1 Replies
View Related
Apr 29, 2015
Now a sample table is included and an expected result list. I also added a piece of the sql to obtain the results.
The remaining question is how to calculate the total break time in minutes. The first two columns of the results are already in the code.
Sample of table clock
employee check_in check_out
----------- ---------------- ----------------
1 08:00:00.0000000 11:00:00.0000000
2 08:30:00.0000000 12:14:00.0000000
2 12:25:00.0000000 16:00:00.0000000
1 11:30:00.0000000 14:00:00.0000000
sample of table breakt
startt endt
---------------- ----------------
09:45:00.0000000 10:00:00.0000000
12:00:00.0000000 12:30:00.0000000
The result I am searching for:
| Employee | Timemin| Breakmin|
+----------+--------+---------+
| 1 | 180 | 15 |
| 2 | 224 | 19 |
| 2 | 215 | 5 |
| 1 | 150 | 30 |
+----------+--------+---------+
DROP TABLE breakt;
CREATE TABLE breakt(
startt TIME
, endt TIME
);
INSERT INTO breakt(startt,endt) VALUES ('09:45:00','10:00:00');
[Code] .....
View 3 Replies
View Related
Jan 17, 2007
I am building a customerlist within the customer sales of a period. I have a dataset with two tables: "customer" and "customer_ledger_entry".
In the report I will present the customer number, the customer name and the sales. The problem is the sales value is not available as a field, but I have to calculate this value from the "customer_ledger_entry" table. In this table are several entries (invoices, credit notes, etc.)
How to calculate the values from a underlying table?
View 6 Replies
View Related
Mar 28, 2008
I have a sum , which is filtered within its table group, and then i have a fields in the footer. How would i aggregate these too together and use the result in another row within the footer.
I tried entering this:
=SUM(Fields!Period_1.Value,"table2_Period") / Max(Fields!Goal_1.Value)
but i get an error:
[rsInvalidAggregateScope] The Value expression for the textbox €˜textbox129€™ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
Build complete -- 1 errors, 0 warnings
what am i doing wrong, please help!
View 1 Replies
View Related
May 9, 2014
I am a student, and I am so confused by SQL code that calculates incomes of my contract in a year or each month of year.
I have 3 tables:
lodgings_Contract:
id_contract indentity primary,
id_person int,
id_room varchar(4),
day_begin datetime,
day_end datetime,
day_register datetime
money_per_month money
electric:
id_electric indentity primary key,
id_room varchar(4),
number_first int,
number_last int,
Sum_Number int,
money_electric money,
status bit
Water:
id_Water indentity primary key,
id_room varchar(4),
number_first int,
number_last int,
Sum_Number int,
money_water money,
status bit
Now what I want to do are statistics on how much money I got in a year or month. Here is my code to calculate incomes of year.
Select Year(day_register) as 'Year'
, Sum(money_per_month * month(day_end-day_register)) + sum(b.money_electric+c.money_water) as 'Incomes'
From lodgings_Contract a
, electric b
, Water c
Where a.id_room = b.id_room
And a.id_room = c.id_room
And b.status = 1
And c.status = 1
Group by Year(day_register)
View 4 Replies
View Related
Jul 11, 2014
I've the table like
create table accutn_det
(
fs_locn char(50),
fs_accno varchar(100),
fs_cost_center varchar(100),
fs_tran_type char(50)
[Code] .....
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?
View 9 Replies
View Related
Nov 4, 2015
I tried using the page below but wasn't able to quite properly adapt it to my scenario
[URL]
I have two UNRELATED (can't be related for reasons outside the scope of this, other relationships already exist) tables.
Each table does, however, contain a WorkerID:
WorkerTimesheets:
WorkerID, HoursLogged, Date
3,10,2015-05-05
3,6,2015-05-10
4,8,2015-05-01
Then the unrelated table "ConstructionSites"
SiteID, SiteManagerID, SiteOpen,SiteClosed
A5, 3,2015-01-01,BLANK
What I want to do is be able to show a measure that SUMS the number of hours logged by anyone who is a SiteManager from the ConstructionSites table.
I wanted to do a SUMX of WorkerTimesheets against HoursLogged, but FILTER against WorkerTimesheets[WorkerID] = ConstructionSites[SiteManagerID] so only workers who are also SiteManager would be counted.However, I can't seem to get that to resolve it always throws an error along the lines that it can't determine context.
View 3 Replies
View Related
Sep 4, 2015
I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.
Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
How can i do that in a query?
View 5 Replies
View Related
Sep 30, 2014
What would be the most straight forword to Calculate 90 days and 3 Years ago from an Effective Date in a table?
as in
SELECT EffectiveDate
from FL.CEFHistory
I need to return the effective date - 90 days and 1 year from that.
[URL]
View 6 Replies
View Related
Nov 9, 2015
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
[Code] ....
View 6 Replies
View Related
