SQL Server 2012 :: Running Total On 2 Columns
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
ADVERTISEMENT
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
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
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
Sep 14, 2015
I have update schema and I upload image with my desire result.
CREATE TABLE #NONAMETABLE(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int
[Code] .....
View 7 Replies
View Related
Jun 7, 2015
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
[Code] ....
Please see attachment to view output.
View 7 Replies
View Related
Mar 10, 2014
I'm using SQL 2012 express.. and just recently learned how to code.
I wrote a query and keep receiving this error...
Error converting data type varchar to float.
here's the query code
SELECT SUM(cast(lc as float))
FROM [dbo].[LaborCosts]
WHERE ppty = 'ga'
AND PL = 'allctd ktchn expns'
AND ACCT like 'payroll%'
I am trying to sum up the values in column LC, and realized I have unnecessary quotations marks. How can I eliminate the quotations from the column, and only query the numerical values?
View 2 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
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
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
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
Dec 18, 2013
I have a table with the Group name and Total Count by group . I need to add a 'Total' and summation of all counts at the end .
CREATE TABLE #TempTable
(GroupName VARCHAR(10),NumberOfCases INT
INSERT INTO #Temp Table (GroupName,NumberOfCases)
VALUES ('Grp A',10)
INSERT INTO #Temp Table (GroupName,NumberOfCases)
VALUES ('Grp B',20)
[code]....
View 5 Replies
View Related
Mar 21, 2014
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:
DealerID RepairID
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
2 11
2 12
2 13
Table 2:
RepairID Tire
1 225/65R17 102T
2 225/65R17 102T
3 225/65R17 102T
4 235/60R18 102V
5 235/60R18 102V
6 235/60R18 102V
7 235/60R18 102V
8 205/55R16 89H
9 205/70R15 89H
13 225/65R17 102T
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.
Desired Output:
DealerIDTire RepairsOfThisTireRepairCount PercentOfTotalRepairOrders
1 235/60R18 102V4 10 40
1 225/65R17 102T3 10 30
1 205/55R16 89H1 10 10
2 225/65R17 102T1 3 33
The equation I am given to calculate the top tires per dealer is as follows: Total # of tires for the size / Total # of repair orders per dealer.
Here is what I have so far though I think I might have to rewrite all of it.
SELECT
DealerID ,
COUNT(RepairID)
INTO #TotalDealerRepairOrders
FROM
dbo.Table1
GROUP BY
DealerID
SELECT
[code].....
View 9 Replies
View Related
Nov 25, 2014
SQL query to calculate the total on-peak and off-values for a month as well as the Max/highest on-peak/offPeak hourly value for that month.
On a daily basis i store the hourly values of the meter in a SQL table.
On-Peak
Summer: Apr-Oct hours(7-22) on weekdays (M-F)
Winter: Nov-Mar hours(8-23) on weekdays (M-F)
off-Peak
Summer: Apr-Oct hours(0-6,23,24); Weekends (Saturday & sunday) ; all public holidays during those months as to be considered as off peak
Winter: Nov-Mar hours(0-7,24);Weekends (Saturday & sunday); all public holidays during those months as to be considered as off peak
Here is the DB Table Structure:
Column Name & Data Types
HourId - Uniqueidentifier
CustomerName - nvarchar(50)
Readingdate - datetime
IntegratedHour - TinyInt
Load - decimal(18,4)
Generation - Decimal (18,4)
LastModified - Datetime
ModifiedBy - nvarchar(50)
View 9 Replies
View Related
Jan 21, 2014
How do I add column names as Total and SubTotal for NULL values.
SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)
[code]....
View 9 Replies
View Related
Mar 21, 2015
I have the table below and like to create a view to show the no of days the property was vacant or void and rent loss per month. The below explanation will describe output required
For example we have a property (house/unit/apartment) and the tenant vacates on 06/09/2014. Lets say we fill the property back on 15/10/2014. From this we know the property was empty or void for 39 days. Now we need to calculate the rent loss. Based on the Market Rent of the property we can get this. Lets say the market rent for our property is $349/pw. So the rent loss for 39 days is 349/7*39 = $1944.43/-.
Now the tricky part and what im trying to achieve. Since the property was void or empty between 2 months, I want to know how many days the property was empty in the first month and the rent loss in that month and how many days the property was empty in the second month and the rent loss incurred in that month. Most of the properties are filled in the same month and only in few cases the property is empty between two months.
As shown below we are splitting the period 06/09/2014 - 15/10/2014 and then calculating the void days and rent loss per month
Period No of Void Days Rent Loss
06/09/2014 - 30/09/2014 24 349/7*24 = 1196.57
01/10/2014 - 15/10/2014 15 349/7*15 = 747.85
I have uploaded a screenshot of how the result on this link: [URL] ....
Declare @void Table
(
PropCode VARCHAR(10)
,VoidStartDate date
,LetDate date
,Market_Rent Money
[Code].....
View 4 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
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 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
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
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
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
