Run-Time Running Total *(SOLVED)
May 14, 2008
I am re-writing a old query that update's it's value based on values at run time. Is there anyway to accomplish the following query where column s1.AI referances the values that were updated during run-time.
I want to avoid a Loop (i.e. cursor or a CTE).
Please help!
Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))
Insert Into @Stage
Select
convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,1 as BenefitInterestID,1701.00 as amount,79.605 as InterestAmount ,0.1000 as Interest,0.0000 as ai
Union all
select '2007-12-01 00:00:00.000',2,172.80,7.92,0.0500,0
Union all
select '2008-12-01 00:00:00.000',4,0.00,0.00,0.0700,0
UPDATEs1
SETs1.ai = s1.Interest * coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0)
FROM@Stage AS s1
select * from @Stage
My desired Results are
2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700143.5300
But I am geting
2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700137.2928
Due to as you can see on row 3 that the amount does not factor in the previous Years ai column when I sum, due to the update takes place at runtime.
Please help!
View 15 Replies
ADVERTISEMENT
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
Mar 8, 2006
Help can anyone see anything wrong with this I am trying to get a running total to show on my form for the users to see, as a team member makes a payment they should be able to see the remaining amount
SELECT SUM(DISTINCT TotalPayments) AS Payment, SUM(AmountDue) AS Due, SUM(FinePayment) AS Fine, [TM #]
FROM dbo.FinePayments_Tbl
GROUP BY [TM #]
HAVING ([TM #] = 9282)
View 2 Replies
View Related
Jul 24, 2006
Good day,
I have been trying to figure out how to get a running total using my sql statement and just can't.
My current table has 2 fields:
Value
Name
Then there are a whole bunch of entries:
31 Jon
23 George
3 Bob
4 Jon
9 Jon
98 Bob
What I would like is from my outputed SQL is something like:
23 Geogre 23
43 Jon 66
101 Bob 167
Can anyone help me, this is really confusing for me and need it to work. I am not able to change the table layout in anyway what so ever and only have the 2 fields, Value Name to use.
Please help me
View 14 Replies
View Related
Mar 25, 2008
I'm using the following codein a MSSQL/PHP intranet project for paging through records:
DECLARE
@PageSize INT,
@PageNumber INT,
@FirstRow INT,
@LastRow INT
SELECT@PageSize = $pageLength,
@PageNumber = $pageNumber;
SELECT@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
@LastRow = (@PageNumber - 1) * @PageSize + @PageSize;
WITH results AS
( SELECT
t1.Mortgage_Number,
t1.Advance_Number,
t1.Effective_Date,
t1.Due_Date,
t1.Transaction_Type,
t1.Posting_Type,
t1.Transaction_Amount,
t1.Account_Source,
t1.Sub_Posting_Type,
CASE
WHEN t2.description IS NOT NULL
THEN t2.description
ELSE t1.Transaction_Description
END AS Transaction_Description,
ROW_NUMBER() OVER (ORDER BY [Effective_Date] $direction) AS RowNumber
FROM format_transactions AS t1
LEFT JOIN sd_posting_types AS t2
ON t1.Transaction_Type = t2.Transaction_Type
AND t1.Posting_Type = t2.Posting_Type
AND t1.Sub_Posting_Type = t2.Sub_Posting_Type
AND t1.Account_Source = t2.Account_Type
WHERE (Mortgage_Number = '$mortgage' $additional_date_params)
)
SELECT
Mortgage_Number,
Advance_Number,
Effective_Date,
Due_Date,
Transaction_Type,
Posting_Type,
Transaction_Amount,
Account_Source,
Sub_Posting_Type,
Transaction_Description
FROM results
WHERE RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY [Effective_Date] $direction
View 7 Replies
View Related
Aug 28, 2005
Hi there. As you will see from my questions, I am an SQL newb. Idabble but never get to spend enough time to get proficient so base anyfeeedback on that basis please. This is all theoretical information atthis point so I am also going to post this in a MySQL related group. Iwill create some designs and post back to the group if I get anyfeedback I can use.Problem:I would like to be able to keep a running percentage total in a fieldassociated with my users. In order to calculate the totals, I willparsing a text file with entries from my users in it. The parser (AWKetc) will search the file for specific text, compare it to informationin another file and output some entries into a csv file which cansubsequently be imported into the database.The users make posts that are considered good and bad and the ratingpercentage must be based on that. For example, if a user makes 10posts in a day, and 4 of them are considered 'bad' by my criterion, therating should reflect a score of 60% for that day.However, the rating is an ongoing value that will be adjusted daily andI must maintain a running total against all previous posts. So, letssay on day two the same user posts 10 more times and 3 are 'bad', Imust adjust his score to reflect a total percentage rating which wouldthen be 20 posts with 7 being bad for an overall rating of 65% etc.My question is, how should I go about recording and calculating allthis information?Here are my thoughts. I have a users table with a field calledsomething like 'Rating' which stores the overall value (65% etc). Thisvalue would have to be calculated from fields in another table like'Posts' which records each post in 'Good' and "Bad' fields thatincrement. The Good and Bad fields would be incremented (populated)from the text that gets imported etc.Looking for thoughts from experienced db designers please. Thanks alot in advance for any responses.
View 5 Replies
View Related
Mar 28, 2007
I have a running total (RunningValue) in one of the column, in my report. I want another running total of that existing running total. This is not allowed in SQL Reporting Services (Aggregate inside an aggregate function is not allowed).
How do I do that? Is it possible to use Visual Basic...using Code function? I'm not familiar with VB syntaxes. Would someone please help me with this... how to pass values to VB code... do the calculation... and display the answer in another column in the report.
Is there any other way of doing it (without using VB)?
View 1 Replies
View Related
Apr 28, 2004
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?
TIA
Fatherjack
View 3 Replies
View Related
Apr 16, 2006
I have two tables. ID table that has these fields
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
View 1 Replies
View Related
Jun 2, 2015
get Running Total in SQL Server.I found that new OVER clause.
SELECT
CompanyId
,IMNId
,RowNumber
,Sum(Qty) OVER(ORDER BY CompanyId, IMoneyNetId, RowNumber ROWS UNBOUNDED PRECEDING) As CumulativeShareQty2
From#ReportData
WhereSequenceNumber <> 3
Group by CompanyId
,IMNId
,RowNumber
But somehow i get below error, when i try to run above query:
Msg 8120, Level 16, State 1, Line 957
Column '#ReportData.Qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
View 7 Replies
View Related
May 28, 2008
Using a trigger that when a record is inserted it calculates a running total after the insert. Code below:
CREATE TRIGGER [UPDATEOrderRebatewithRunningTotal] ON dbo.OrderRebate
AFTER INSERT
AS
DECLARE@RunningTotal DECIMAL(16, 6),
@OrderNumber VARCHAR(8)
UPDATEOrderRebate
SET@RunningTotal =CASE
WHEN @OrderNumber IS NULL THEN Ext_Rebate
WHEN Ord_No <> @OrderNumber THEN Ext_Rebate
ELSE round((@RunningTotal + Ext_Rebate),2)
END,
RunningRebateAmt = @RunningTotal,
@OrderNumber = Ord_No
My problem is that it is not calculating the last record.
Order is entered with 4 items I get the following.
Ord_no ext_rebate RunningRebateAmt
102 2.095 2.095
102 1.744 3.84
102 2.41 6.25
102 .0415 NULL
View 2 Replies
View Related
Apr 16, 2006
I have two tables. ID table that has these fields
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
View 6 Replies
View Related
May 28, 2008
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
View 3 Replies
View Related
Dec 5, 2006
I want to write a stored procedure to add all of the flying time on a given airplane for each flight until now and then reset the total when it gets to a new airplane. I want to be able to pull from the table all flying time from a given date for a given airplane.
For example:
AircraftNo Date Flight_Hrs Total
38 3/1/06 1 4.5
38 5/10/06 1.5 3.5
38 7/10/06 2 2
100 8/19/03 4 8.5
100 10/04/04 3 4.5
100 9/17/06 1.5 1.5
I pulled the following code from the internet but I'm getting errors.
select AircraftNo
,Actual_departing_date,Flight_Hours
,sum(Flight_hrs) over partition by AircraftNo
order by Date
rows between current row and unbounded following) total
from (Select Distinct
F.AircraftNo
,F.Date
,C.Flight_Hrs
From GDB_01_4_Test.dbo.Flight_Log F,
GDB_01_4_Test.dbo.Flight_Cycle_Count C
Where F.Doc_No = C.Flight_log_Doc_No
Am I doing something wrong. I'm getting the following syntax errors (Using SQL 2000):
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'over'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'Flight_log_Doc_No'.
View 5 Replies
View Related
Dec 17, 2007
Hi
I have a report that calculates shipping quantities based on customer orders, and color codes them based on whether or not we have stock for a full shipment (green), have partial stock for a shipment (blue), or if we have none (red). Every week we get customer orders and build accordingly.
I have this all worked out except for parts with multiple ship to locations. RS seems to parse the entire row of the first ShipTo, then the second ShipTo. I'm using the running total function to calculate if there is a need or not. Anybody have any ideas?
Here is an example of the output I am getting. On the 17th, the 360 should be in green not red. I double checked the grouping, and it is on part number. This is for a matrix.
http://img519.imageshack.us/img519/8650/productionqg0.png
View 1 Replies
View Related
Oct 21, 2015
I need to get a cumulative total for row by row basis. I need this grouped on name, id, year and month. ID is not a auto incremented number. ID is a unique number same as name. The out put I need is as below,
Name ID Year Month Value RunningValue
XX 11 2013 Jan 25 25
xx 11 2013 Feb 50 75
yy 22 2015 Jan 100 100
yy 22 2015 Mar 200 300
How I could get this query written? I am unable to use SQL Server 2012 version syntaxes. Writing a cursor would slow the process down because there is a large data set.
View 12 Replies
View Related
Apr 15, 2008
Hi guysI'm mulling over the best way to do something and would like your input. Forgive me if this is a bit 101 - I haven't ever had to do this in SQL before!Fairly standard set up - Hierarchy table modelling the structure of an organisation.Related table associating members of staff to the hierarchy.I want to return all levels of the hierarchy and for each level I would like to know the total number of people in the level (so for a division it would be the sum of all people in the child teams).Parameters - This table will be modelling many organisations' structures - I cannot guarentee anything like "there will never be more than n levels". As such - I would strongly prefer to have something that is iterative recursive. I can change the schema to suit the method I use if necessary. Database is not transactional - I am not concerned about updating speed. SQL Server 2K5.I've tried CTE but it turns out you cannot use group by in CTEs (even in derived tables). I have not yet tried feeding it a view or similar.I have not tried nested sets, materialised paths, accumulator table - I thought I would see if there is something obvious before I start piddling around with those.Ta!
View 11 Replies
View Related
Jul 25, 2014
Table A has day to day transactions, Table B has beginning balance. I'd like to get a running total balance day to day. Really what I want to do is use the previous days total to add the current days transaction to, but I don't know how to do it. The basic layout is below, but as you can see, I'm not getting the totals correct.
create table #current(acctNum int,
dates date,
transtype char(10),
amt INT
)
insert into #current(acctNum, dates, transtype, amt)
[Code] .....
View 4 Replies
View Related
Mar 18, 2015
I have a data table in below format and the need the desired output in 2nd table format
TABLE1
RANKBOOLEANREVENUE
1TRUE100
2FALSE150
3FALSE200
4FALSE250
5FALSE300
[code]....
Desired Output to be:
RANKBOOLEANREVENUERUNNINGTOTAL
1TRUE 100250
2FALSE150400
3FALSE200600
4FALSE250850
5FALSE3001150
[code]....
View 3 Replies
View Related
Apr 30, 2008
SQL Server 2005.
Schema is not fixed at this stage - small project, schema can be adapted if neccessary.
This needs to be produced in SQL Server - there is no client application.
Hi
I am really struggling to write the SQL for the following requirement and have got to the stage where I think I need someone to give me some pointers. I can't believe this is a novel requirement but I haven't had to do this before nor can find anything similar when googling.
I have a hierarchy detailing an organisation structure. Eventually the table will store many structures and these may one day be "pseudo structures" to enable really granular reporting. As such I cannot really say "there will never be more than N levels to this hierarchy". Currently I'm using a standard adjacency design. From now on I'll call the items in the table "business units".
There is a further table with a related FK declared which associates people with business units.
Requirement: Return all business units and cumulative total total of employees for each of these units (i.e. the total for each business unit will be the number of people in that unit plus the sum of the people in all the child units).
I've asked this question on dbforums but ended up a blind alley despite Peter's attempt to help. I'm starting again from scratch but feel free to review the (now dead) thread here:
http://www.dbforums.com/showthread.php?t=1629366
To summarise - I tried the CTE route but of course this evaluates the hierarchy from the top down. To fulfill the requirement, the bottom of the structure needs to be the starting point. I also tried reversing the hierarchy so that children, rather than the parent, are stored but my query fails if there are 3+ levels.
Code to reproduce problem (note - I expect that I will need to get the employee count per business unit prior to working with the hierarchy so I have flattened the schema to reflect this - I think I can handle the SQL to get the non-cumulative count per business unit ):
DECLARE @table TABLE
(
unit_codeTINYINT
, parent_codeTINYINT
, headcountINT
, PRIMARY KEY CLUSTERED (unit_code)
)
INSERT INTO @table (unit_code, parent_code, headcount)
SELECT 1, 4, 10 UNION ALL
SELECT 2, 4, 130 UNION ALL
SELECT 3, 10, 93 UNION ALL
SELECT 4, 10, 7 UNION ALL
SELECT 10, NULL, 1
Required output:
unit_codecumulative_headcount
------------------------------------
110
2130
393
4147
10241
Many thanks
View 5 Replies
View Related
Jul 20, 2005
in my procedure, I want to count the number of rows that have erroredduring an insert statement - each row is evaluated using a cursor, soI am processing one row at a time for the insert. My total count tobe displayed is inside the cursor, but after the last fetch is called.Wouldn't this display the last count? The problem is that the count isalways 1. Can anyone help?here is my code,.... cursor fetchbegin ... cursorif error then:beginINSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,CUSTINDICATOR, DT_LOADED)VALUES(@ERRORNUM, @ERRORDESC,@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY,@STATE, @POSTALCODE, @CONTACT, @PHONE, @SALESREPCODE,@PRICELEVEL, @TERMSCODE, @DISCPERCENT, @TAXCODE,@USERCOMMENT, @CURRENCY, @EMAILADDRESS, @CUSTOMERGROUP,@CUSTINDICATOR, @DTLOADED)SET @ERRORCNT = @ERRORCNT + 1END --error--FETCH NEXT FROM CERNO_US INTO@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY, @STATE,@POSTALCODE, @CONTACT,@PHONE,@SALESREPCODE, @PRICELEVEL,@TERMSCODE,@DISCPERCENT, @TAXCODE, @USERCOMMENT, @CURRENCY,@EMAILADDRESS,@CUSTOMERGROUP, @CUSTINDICATOR, @DTLOADED--IF @ERRORCNT > 0INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,STATUS)VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @ERRORCNT, 'FAILEDINSERT/UPDATE')END -- cursorCLOSE CERNO_USDEALLOCATE CERNO_US
View 1 Replies
View Related
May 9, 2007
I am trying to do a sum on a goal amount that is repeated for each record. But what is the the forumla to only sum on the distinct goal amount.
Example:
Month Year Goal Other Value
March 2007 500 5568
March 2007 500 5568
March 2007 500 5569
April 2007 600 5568
April 2007 700 5569
Total (I am receive)
March-April 2007 1600 5568
March-April 2007 1200 5569
Total (I excpect)
March-April 2007 1100 5568
March-April 2007 1200 5569
I haven't found anything online to help.
Thanks,
Sam
View 5 Replies
View Related
Apr 26, 2007
Hello,
How do I add unique values on the report? For example say I have this in my report:
Customer: Food Purchased: Amount:
Judy Cat Food $12
Sarah Dog Food $13.50
Diane Rabbit Food $17
Jason Dog Food $16
Tammy Dog Food $15
In the footer of the report I want to print a summary box that looks like this:
Product: Number Purchased: Total:
Cat Food 1 $12
Dog Food 3 $44.50
Rabbit Food 1 $17
How do I do this?
Thanks!
View 9 Replies
View Related
Nov 20, 2015
Is it possible to assign to a variable, then add to it later on? When I run the below, all I get is 3 rows affected I never see the value printed. What i am wanting to do is each loop sum the numbers so 2+1+3 =6 so in the end @sumofallnumbers
= 6
Create Table #Test ( randarnumbers int )
Insert Into #Test Values ('2'), ('1'), ('3')
Declare @sumofallnumbers int, @nbr int
Declare c1 Cursor For
Select randarnumbers
FROM #Test
[code]..
View 6 Replies
View Related
Mar 19, 2007
Hi,
I'm trying to do running total of the calculated field. Here is the detail of my RDL:
In a table, I have "textbox34" with the formula =count(Fields!ADF_NO.Value) in it and "textbox30" with the formula =first(Fields!TOTSTKS2.Value, "ProdServSales") - reportitems!TOTSTKS.value in it. I have divided "textbox 34" with "textbox30", and placed the results in "textbox36". Now, I want to do a cummulative total (Running total) of "textbox36", using the formula =runningvalue(reportitems!textbox36.Value, sum, nothing) in "textbox14". Its giving me an error saying:
"[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox14' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers."
Any suggestions? I appreciate your help on this.
Thanks
Perm
View 2 Replies
View Related
Dec 22, 2014
I need to calculate running total which resets when reached to 16, and also needs to calculate remaining amount as paid.
I have attached sample data, so we have two columns Earned, and Used. And we need to calculate Balance, and Paid columns.
View 4 Replies
View Related
Aug 26, 2015
I have been tasked with writing a report that shows all open orders for an item and their quantities, along with a running total of what is left in stock. We start by building these two tables:
IF OBJECT_ID('tempdb..#Orders', 'U') IS NOT NULL DROP TABLE #Orders;
IF OBJECT_ID('tempdb..#Warehouse', 'U') IS NOT NULL DROP TABLE #Warehouse;
CREATE TABLE #Orders (OrderDate DATETIME, JobNumber NVARCHAR(10), Item NVARCHAR(20), QtyOrdered NUMERIC(10, 2))
INSERT INTO #Orders SELECT '20150801', 'JOB1', 'Widget1', 5
INSERT INTO #Orders SELECT '20150802', 'JOB2', 'Widget1', 3
[code]....
View 8 Replies
View Related
Jul 20, 2005
Hi,I am trying to add a staggered running total and average to a queryreturning quarterly CPI data. I need to add 4 quarterly data pointstogether to calculate a moving 12-month sum (YrCPI), and then tocomplicate things, calculate a moving average of the 12-month figure(AvgYrCPI).Given the sample data:CREATE TABLE [dbo].[QtrInflation] ([Qtr] [smalldatetime] NOT NULL ,[CPI] [decimal](8, 4) NOT NULL) ON [PRIMARY]GOINSERT INTO QtrInflation (Qtr, CPI)SELECT '1960-03-01', 0.7500 UNIONSELECT '1960-06-01', 1.4800 UNIONSELECT '1960-09-01', 1.4600 UNIONSELECT '1960-12-01', 0.7200 UNIONSELECT '1961-03-01', 0.7100 UNIONSELECT '1961-06-01', 0.7100 UNIONSELECT '1961-09-01',-0.7000 UNIONSELECT '1961-12-01', 0.0000 UNIONSELECT '1962-03-01', 0.0000 UNIONSELECT '1962-06-01', 0.0000 UNIONSELECT '1962-09-01', 0.0000 UNIONSELECT '1962-12-01', 0.0000 UNIONSELECT '1963-03-01', 0.0000 UNIONSELECT '1963-06-01', 0.0000 UNIONSELECT '1963-09-01', 0.7100 UNIONSELECT '1963-12-01', 0.0000 UNIONSELECT '1964-03-01', 0.7000 UNIONSELECT '1964-06-01', 0.7000 UNIONSELECT '1964-09-01', 1.3900 UNIONSELECT '1964-12-01', 0.6800 UNIONSELECT '1965-03-01', 0.6800 UNIONSELECT '1965-06-01', 1.3500 UNIONSELECT '1965-09-01', 0.6700 UNIONSELECT '1965-12-01', 1.3200I am trying to return the following results:Qtr CPI YrCPI AvgYrCPI-------- ----- ----- --------1-Jun-60 1.481-Sep-60 1.461-Dec-60 0.721-Mar-61 0.71 4.371-Jun-61 0.71 3.601-Sep-61 -0.70 1.441-Dec-61 0.00 0.72 2.531-Mar-62 0.00 0.01 1.441-Jun-62 0.00 -0.70 0.371-Sep-62 0.00 0.00 0.011-Dec-62 0.00 0.00 -0.171-Mar-63 0.00 0.00 -0.181-Jun-63 0.00 0.00 0.001-Sep-63 0.71 0.71 0.181-Dec-63 0.00 0.71 0.361-Mar-64 0.70 1.41 0.711-Jun-64 0.70 2.11 1.241-Sep-64 1.39 2.79 1.761-Dec-64 0.68 3.47 2.451-Mar-65 0.68 3.45 2.961-Jun-65 1.35 4.10 3.451-Sep-65 0.67 3.38 3.601-Dec-65 1.32 4.02 3.74Note, 4 data points are required to calculate a moving sum of CPI(YrCPI) and 4 calculate YrCPI figures are required calculate theannual average of YrCPI (AvgYrCPI), giving a staggered effect to thefirst 7 resultsThis sad effort is about as far as I've got:SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPIFROM QtrInflation IJOIN (SELECT TOP 4 Qtr, CPIFROM QtrInflation) SON S.Qtr <= I.QtrGROUP BY I.Qtr, I.CPIORDER BY I.Qtr ASCCan anyone suggest how do achieve this result without having to resortto cursors?Thanks,Stephen
View 5 Replies
View Related
Nov 9, 2015
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
MonthId Flag CalcVal
201401 1 1
201402 1 2
201403 1 3
201404 1 4
201405 1 5
and so on .........
View 8 Replies
View Related
May 28, 2015
I want to get a running total. But its a bit tricky. I have data depending on ID, YEAR, Month, Value like the following.
ID, Year, Month, Value
36002084 2014 Jan 8391
36002084 2014 Jan 0
36002084 2014 Jan 3440
36002084 2014 Jan 0
36002084 2014 Jan 548
36002084 2014 Jan 109
36002084 2014 Jan 125
I wrote the Query below
select
[UEP ID],[Year],[Month],sum([Total VtM EUR]) 'Running Total'
from
RegData
where
[UEP ID]=36002084 and [Year]=2014
group by [UEP ID],[Year],[Month]
I got this output
36002084 2014 Apr 887620
36002084 2014 Aug 789440
36002084 2014 Dec 528453
36002084 2014 Feb 606627
36002084 2014 Jan 331613
36002084 2014 Jul 681314
36002084 2014 Jun 330384
36002084 2014 Mar 1044301
36002084 2014 May 671818
36002084 2014 Nov 465576
36002084 2014 Oct 590147
36002084 2014 Sep 527861
Now I need to get a output like this,
36002084 2014 Jan 331613
36002084 2014 Feb 938240
A running total depending on the ID, Year and Month. Although I showed this for two steps I need to get it from one query.
Question is - To get a running total based on ID, Year, Month. If I do the normal group by and sum query I could get it for all the months for each id. But need to then add January value to February and updated February value.
View 10 Replies
View Related
May 22, 2015
Let's have a table to show what I mean ...
As you can see I have added only one contract (for the sake of simplicity). At any given date I want to calculate the running total for a contract (all contracts), but the aggregation must take the contract state into consideration.
You can see my expected result for queries 1 - 3. That means, with the actual state ('B') I want to aggregate all values regardless of their state in the past.
For query 2 the state is 'A' ... I hope you can follow.
Now my aim is to aggregate all contract states for any given date with the right value. The past states of each contract are not relevant. And of course future ones (with respect to the query date) shall also be irrelevant.
Maybe the solution is a combination of 'LastNonEmpty' and SUM/PERIODSTODATE ...
View 5 Replies
View Related
Jul 27, 2014
I want to create the following scenario. I have a table that stores employees working on projects and their project hours by week, but now I also need a running total per week for each of those projects. For example take a look below:
EmployeeID, Project, Sunday, Monday, Tuesday,....Saturday, ProjectHours, TotalProjectHoursPerWeek(this is the column I am trying to derive), FiscalWeek
101, ProjectABC, 5,5,5,...5, 20, 40,25
102, ProjectXYZ 4,4,4,....4, 20 ,40,25
103,ProjectQWE, 2,2,2,...2, 8, 32,26
104, ProjectPOP, 6,6,6,...6, 24, 32,26
What I have tried so far:
Correlated Subquery:
SELECT EmployeeID,Project, Sunday, Monday,....Saturday, ProjectHours, SELECT(SUM(ProjectHours) FROM dbo.TableABC ap GROUP BY FiscalWeek),
FROM
dbo.TableABC a
I got this to work one time before, but now I am getting the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
View 2 Replies
View Related
Oct 23, 2015
I have a table called PJLabDet.Total_Hrs which is the sum of hours of 7 days.
I would like to get a running total for this field which would reset when my group (called PJLabDat.pjt_entity) changes.
View 2 Replies
View Related
