Accumulating Rolling Total
Oct 9, 2006
I'm trying to create an accumulating field based on a set of records. I need to fill in daily amount balances that accumulates on a daily basis. But I can't seem to figure out how to create a total for the daily dates and have it add on additional amounts if needed.
Here's some sample data:
5 6 20 1 200.00 5/5/20000
5 6 20 1 -149.00 5/8/2000
5 6 20 1 100.00 5/10/2000
Now I already have a table with the dates created via a stored procedure. I have a set of dates from 5/5/2000 to 5/8/2000. So that results set should look like this:
5 6 20 1 200.00 5/5/2000
5 6 20 1 200.00 5/6/20000
5 6 20 1 200.00 5/7/2000
5 6 20 1 51.00 5/8/2000
5 6 20 1 51.00 5/9/2000
5 6 20 1 151.00 5/10/2000
....
I'm trying to creating a rolling sum that accumulates the amount field for each daily record and if a new amount is listed, then roll that amount into the total. If you have any suggestions about how to perform this rolling total via TSQL or SSIS, I would greatly appreciate it.
Thanks
Greg
View 1 Replies
ADVERTISEMENT
Jun 3, 2004
I am trying to create a procedure which will calculate the total tuition
This process involves 3 tables.
Contract table has tuition information which is all $100 (set price).
Discount table has discount type and discount percentage (ex. 0.3) on each discount type.
ContractDiscount table have contract number and discount number to connect both tables
I think I need to create a loop since some contract gets more than one discount.
I have to calculate and get result nee to be like this
total_tuition = (tuition - discountPer * tuition) - this has to be a loop condition
Do you have any suggestion ?
Thanks
View 3 Replies
View Related
Dec 1, 2014
We have an inventory of devices we service and wish to show the total numbers of active devices per month going 12 months back. How would I go about this?
View 2 Replies
View Related
May 13, 2008
Dear All
I have the following table structure and following data
create table emp(empid char(4),empname char(10),empsal money,empsalPercent money,empsalRuningTot money)
empid empname empsal empsalpercente001 tom 20.00 8.69e002 *** 40.00 17.39e003 harry 15.00 6.52e004 masum 25.00 10.86e005 rana 30.00 13.04e006 dipak 60.00 26.08e007 rana 40.00 17.39
now would like to make the following
1) want to sort first emp table based on empsalPercent field on descending order physicallyProb : am only able to select the same thru descending order but am unable to make it physical shortingselect * from emp order by empsalPercent desc
2) after make descending order for empsalpercent I want to make RunningTotal for the EmpsalPercent column
pls helpsamir
View 3 Replies
View Related
Nov 5, 2012
I'm in need of creating a rolling sum to find the sum of 3 previous months gas production. Below is an example of the basic data I will be using to find the sum.
OperatorIDRRCID YearReported MonthReported YearCode DateReported GasProduction
71054993207201222012022012-02-01 89
71054993207201212012012012-01-01 721
710549932072011122011122011-12-01 353
710549932072011112011112011-11-01 409
710549932072011102011102011-10-01 544
71054993207201192011092011-09-01 707
71054993207201182011082011-08-01 506
71054993207201172011072011-07-01 536
71054993207201162011062011-06-01 472
I want the sum of the Year & Month of 2012-2 to be the sum of the gas production for 2012-1, 2011-12, and 2011-11.
View 1 Replies
View Related
Apr 14, 2006
I've got a table that contains a column of accumulating uptime datathat looks similar to this:239.13239.21239.30239.38239.46239.550.350.440.530.600.680.780.850.93I need to SUM the data up the point where the data gets reset (the nextrow is less than the preceeding row). Then I start the SUM again untildata gets reset.Thanks in advance for help!
View 20 Replies
View Related
Jul 21, 2015
I have an issue accumulating a value based on certain conditions:
I need to sum up the days column as you can see table below. The sum is based on the StartNewFlag.
When the flag is 1, I need to start a new sum from zero, else I need to accumulate to the previous rows.
CREATE TABLE #foo
(
ItemID int,
StartDate datetime,
EndDate datetime,
StartNewFlag bit,
[Code] ....
My Results must look like one of the following tables (either one would work, however the second one is preferred)
Result 1:
ItemID StartDate EndDate StartNewFlag Days TotalDays
1 2015-07-01 00:00:00.000 2015-07-05 00:00:00.000 1 4 5
1 2015-07-08 00:00:00.000 2015-07-09 00:00:00.000 0 1 5
1 2015-07-11 00:00:00.000 2015-07-15 00:00:00.000 1 4 11
1 2015-07-18 00:00:00.000 2015-07-21 00:00:00.000 0 3 11
1 2015-07-22 00:00:00.000 2015-07-26 00:00:00.000 0 4 11
OR
Result 2:
ItemID EndDate TotalDays
1 2015-07-09 00:00:00.000 5
1 2015-07-26 00:00:00.000 11
View 5 Replies
View Related
Apr 3, 2008
Hi,
I have a table called Daily.
It has 5 columns, "Testers ","Activity ", "GivenHours ","UsedHours " and " Delta ".
This table will be regularly updated every 12 hours.
Now I need to Accumulate the data from GivenHours and UsedHours with respect to the testers and insert the accumulated data Into a new table called "Weekly".
The accumulation should last only for 7 days and then it should be reset again to accumulate new data for the new week.
Weekly table should have 5 columns "Testers ","Activity ", "TotalGivenHours ", "TotalUsedHours " and " Percentage ".
Percentage is ( TotalUsedHours / TotalGivenHours ) *100.
Can anyone tell me how to do this?
View 2 Replies
View Related
Apr 13, 2007
Hi all,
I found that my testing server is accumulating shapshot folder in repldata. Everytime, we refresh the tables (snapshot publication), a new folder created without the old folders. And I found that there is at most 1 snapshot folder remain in production server. Any parameter to adjust the retention period of the snapshot folder? Thanks in advance
View 4 Replies
View Related
Nov 10, 2004
Hi all,
I'm in a situation where I need to accumulate values from several columns in each of the rows returned in a result set and to then display each accumulation value for each row at the end of each row.
For example, if I had the following...
------------------------------------------------------
name - car - van - bus - total
------------------------------------------------------
Terry - 55 - 34 - 12 - 101
John - 01 - 23 - 05 - 029
etc etc
------------------------------------------------------
Where 'total' column is an accumuliation of the 'car', 'van', and 'bus' columns. The values for the columns 'car', 'van', and 'bus' are all values that have been derived from COUNT function. So what I want is to somehow genarate the values for the 'total' column (car + van + bus).
Now I have tried the following SQL, but to no avail
Code:
SELECT pveh.csign, pveh.name1 AS [Driver], COUNT(pveh.name1) AS Car, COUNT(pveh.name2) AS Van, SUM(COUNT(pveh.name1) + SUM(COUNT(pveh.name2))
FROM planvehicles AS pveh
... and I also tried...
SELECT pveh.csign, pveh.name1 AS [Driver], COUNT(pveh.name1) AS Car, COUNT(pveh.name2) AS Van, SUM(Car + Van)
FROM planvehicles AS pveh
Thanks
Tryst
View 12 Replies
View Related
Apr 21, 2006
hi all,
Im running a snapshot replication on table.
first, i gonna copy the entire table with a regional filter other than date
second i'm going to replicate a days worth of data by using both the regional filter and date filter (getdate) to capture a days wordth of data.
the publication are working fine.
my question is how am i going to accumulated the result of
the filtered snaphot replication in a single table in my db.
many thanks,
joey
View 1 Replies
View Related
Jun 9, 2004
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.
TIA
View 5 Replies
View Related
Feb 26, 2014
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,
[Code] .....
View 3 Replies
View Related
Jul 20, 2005
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
View 4 Replies
View Related
Oct 26, 2015
For some reason my Add Total is grey out, when i tried to add grand total using some expression.
I have two row & two column groups?
Is there any alternative or how can i enable add total? using expression..as you can see in my Attached Image
I'm using iff condition in my expression..Â
View 15 Replies
View Related
Jun 28, 2015
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.
CREATE TABLE sales
(date_created date,
sales decimal (19,2))
INSERT INTO sales (date_created, sales)
VALUES ('6-23-15', '20000.00'),
('6-24-15', '22000.00'),
('6-25-15', '25000.00'),
('6-26-15', '28500.00'),
('6-27-15', '32000.00')
View 9 Replies
View Related
Jul 26, 2013
My table contains 1000 records,
I need to know the total record count with the below paging query
SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
How to get?
View 2 Replies
View Related
Apr 28, 2006
Hi, can anyone help?
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.
Can anyone help, thanks
View 7 Replies
View Related
Jul 20, 2005
Anyone has a "one sql statement" to get the total spaceused and totalspace allocated of an instance ? ie same as sum of relevance fieldsfrom sp_spaceused for each database in an instance, that works accrossversion of mssql from 6 onward.ThanksKD
View 1 Replies
View Related
Mar 27, 2001
If I issue -
update x set y = 'z'
is there a way to undone/roll-back what was done in SQL 7 ?
Thanks very much.
-FB
View 2 Replies
View Related
Sep 8, 2004
I have created a DTS Package that does the following:
1 Delete all data from table 1 (SQL Task)
2 Import Data from .csv file into table 1 (Data import)
3 insert records from table 1 into table 2 when they dont exist in table 2 (SQl Task)
This all works fine, but now i want a rollback function in step 1 and 2.
So when Step 1 is finished and something goes wrong i want the deleted data back.
Can somebody help me?
Thanks in advance.
View 1 Replies
View Related
Feb 19, 2004
One of our engineers here by mistake deleted some very important data without any begin trans block and we need the data back very badly.. We have not closed the session as of now.. The engineer was logged in as sa to the DB.
Can someone please help soon on urgent basis...
View 6 Replies
View Related
Jun 23, 2008
Hi,
I'm writing a query that will be calculating a number of business performance measures including the following...
Average Stock:
This is calculated by taking the actual stock value of the product category per month and dividing it by 13 if we have over 12 month's history, or if a new product, then by the number of months we have had it in stock for.
Cost of Goods Sold Annualised:
This is an average figure for the year of the sold landed costs for the product category over a 13 month period if we have over 12 months history. If it's a new product, then it's for the number of months we have had stock.
My question is, how do I tell the query to go back 13 months from the current month?
View 1 Replies
View Related
Feb 16, 2006
I'm performing a stored proc that has 4 inserts. I only want the inserts to complete as a batch. If one fails, I want to rollback the whole transaction. Does anyone know the syntax?? :)
View 4 Replies
View Related
Mar 3, 2006
I want to insure that each of my insert statements in a stored proc are rolled back if any of the inserts fail. I already have the below statement with error handling but is this correct? It seems to me that all the steps should be made part of an entire transaction so if one part fails then it all fails. Can someone help me w/ the syntax of this??
CREATE PROCEDURE Addrecords AS
--USERS
INSERT INTO [Production].[dbo].[USERS]([LastName], [UserName], [EmailAddress], [Address1], [WorkPhone], [Company], [CompanyWebsite], [pword], [IsAdmin], [IsRestricted],[AdvertiserAccountID])
SELECT dbo.fn_ReplaceTags (convert (varchar (8000),Advertisername)), [AdvertiserEmail], [AdvertiserEmail],[AdvertiserAddress], [AdvertiserPhone], [AdvertiserCompany], [AdvertiserURL], [AccountNumber],'3',0, [AccountNumber]
FROM production
WHERE not exists (select * from users Where users.Username = temp.AdvertiserEmail)
AND validAD=1
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
--PROPERTY
INSERT INTO [Production].[dbo].[Property]([ListDate],[CommunityName],[TowerName],[PhaseName],[Unit], [Address1], [City], [State], [Zip],[IsActive],[AdPrintId])
SELECT [FirstInsertDate],[PropertyBuilding],[PropertyStreetAddress],PropertyCity + ' ' + PropertyState + ' ' + PropertyZipCode as PhaseName,[PropertyUnitNumber],[PropertyStreetAddress],[PropertyCity], [PropertyState], [PropertyZipCode],'0',[AdPrintId]
FROM [Production].[dbo].[Temp]
WHERE AdvertiserEmail IS NOT NULL
AND validAD=1
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
View 2 Replies
View Related
Feb 12, 2007
Hi,
trying to develop a report that shows the sum total for each month during a specified date range.
I have parameters asking for the start date and end date of the date range, the report needs to show the months in between this date range.
One of the fields in the dataset is durationminutes which I need to sum for each month then divide by 60 to show hours. I then need to show the total of each month in a bar graph (with each month along the x axis).
If anyone can shed some light on how I am going to do this it would be greatly appreciated.
Thanks.
View 5 Replies
View Related
Oct 3, 2007
Hi All,
I wanna pull a 12 month rolling data and donno how to do that, can anybody help ?
Problem:
lets say if i run the query today i should get data between oct-3-2006 and oct-3-2007, my main colums is monthyear which is date datatype.
Appreciate your help.
Thanks
Chinna
View 1 Replies
View Related
Nov 10, 2006
I'm trying to figure out how to this do with TSQL and preferablythrough joins rather than by using cursors or temp tables. I havemultiple time spans for multiple individuals that I would like torollup together where they don't actually have a gap in coverage.ID StartDate EndDateZ000001 01/01/1996 9/30/1996Z000001 10/01/1996 12/31/1998Z000001 01/01/1999 04/30/1999Z000001 01/01/2000 12/31/2000Z000001 01/01/2001 12/31/2001I would like to be able to "roll these up" in order to reflect theactual time frames so that it would like this:ID StartDate EndDateZ000001 01/01/1996 4/30/1999Z000001 01/01/2000 12/31/20014/30/1999 to 01/01/2000 is, of course, a legitimate break where therewas a date difference of more than one day.Thanks in advance for any ideas.
View 7 Replies
View Related
Mar 19, 2007
I have a stored procedure that calls another stored procedure with thefirst stored procedure opening a transaction:BEGINSET XACT_ABORT ONBEGIN TRANSACTIONdoes various updates/insertscalls 2nd stored procedure to proccess updates/inserts common to manyother stored proceduresdoes more various updates/insertscommitENDThe problem I'm having is that within the 2nd stored procedure is thatif it encounters an error, it does not roll back the entiretransaction and I finish up with missing records in the database. Amusing this in the 2nd stored procedure:if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendWhat could the problem be? From what I've read, it seems as thoughyou can't have an open transaction within one sp that calls another spand it maintains the same transactoin? Is this corrrect?I tired the following too, and I still couldn't get it to work. Anyideas anyone?************ sp 1 ***********Declare @AddressError char(3)SET XACT_ABORT ONBEGIN TRANSACTIONexec Sp2@AddressError OUTPUT,@variable1,@variable2,etc. etc************** sp 2 *****************@AddressError char(3) OUTPUT,if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendSET XACT_ABORT ONBEGIN TRANSACTIONprocess updates/insertsSet @AddressError = 'no'Commit******** back to sp 1************If @AddressError <'no'BEGINrollback transactionENDcontinue doing updates/insertscommit
View 1 Replies
View Related
Jun 15, 2007
Is database mirroring compatible between SQL Server 2005 (Standard Edition) SP1 and SP2? I currently run SP1, and will shortly be introducing a failover partner. I'd like to have the failover partner installed as SP2, start the mirroring, manually failover, then upgrade the existing server to SP2 as well.
Is this a supported upgrade method, or will I need to upgrade the existing server to SP2 before setting up database mirroring? The SP2 installation instructions and readme file don't appear to mention mirroring at all (at least not that I can find).
Many thanks for any assistance.
View 4 Replies
View Related
Jun 16, 2007
Hi,
I have a case where I read from SQL Server DB and write to a flat file.
I have one Data Flow Task inside which I have a OLEDB source component that feeds rows to a script component that writes to a flat file. I have set the txn attributes for the container to "Required" and "Read committed" . But I find that rows are written to flat file even when I throw an exception from my script component. Question is how do I prevent rows from being written to the flat file if error/exception happens. I want the whole process to be in a single transaction.
Thanks
View 3 Replies
View Related
Sep 4, 2001
I had a need to update a column in a table with a calculated value based on the value of the preceeding value in the table (which in turn was based on the preceeding value, etc...).
Although this can easily solved via a cursor, I was able to solve this with a single set statement that first updates a variable (which can be part of a calculation using the 'current' row) and using the knowledge that SET statements are applied 'in order' (top-down).
Most importantly: the technique relies on my observation that the SET statement processes a table in clustered index sequence, or load sequence in a heap table.
I've included a very simple example of this technique below.
I'm curious if anyone is using this (undocumented?) technique and if my assumptions are valid (I'm using this on much larger tables with more complex calculations).
EXAMPLE:
CREATE TABLE #tmp (
num int IDENTITY(1,1) NOT NULL,
val int NULL)
-- load 100 test rows
DECLARE @x int
SET @X = 1
WHILE @X <= 100
BEGIN
INSERT #tmp (val) VALUES (NULL)
SET @X=@X+1
END
DECLARE @i int
SET @i = 1
UPDATE #tmp -- this is a -very- simple example of technique!
SET @i=num+@i, -- @i now has current totaled value + current-row int value
val=@i -- val now stores current subtotal
View 1 Replies
View Related
Feb 15, 2006
We have installed SQL Server 2000 Service Pack 4 recently and have had various issues with some of our Stored Procedures. (which we didn't have with SP 3)
We are looking at our options regarding rolling back to Service Pack 3.
Can this be done ?
&
If so, is there a method documented on how to do this ?
Any advice would be appreciated.
View 3 Replies
View Related
