T-SQL (SS2K8) :: Keep A Running Total Of OnHandQty Minus OrderQty
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
ADVERTISEMENT
Jul 18, 2014
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,
[code]....
View 9 Replies
View Related
Aug 29, 2015
I have a field in a table which was VERY poorly designed, but that is a matter for another day. Long story short, this field contains, in most instances, where the sale was obtained, the sales man name, and a comment about the sale. A few records have garbage data in the field as the salesman name was not obtained so we want to attribute the info to 'unknown'Is it possible in SQL Server 2008 to write a query that will display the saleinfo for each salesman then a total row under the salesman? Something similar to this
Internet Mark .... statistics here
Phone Mark - applied for credit .... statistics here
Phone Mark - customer referral ..... statistics here
Marks Sales Totals .... statistics here
Next salesman data would go here
but break that down by each salesman and attribute the garbage data like 85623, albaca, racava to salesman 'Unknown'..This is garbage data that should suffice to achieve my desired end result. I know this is unable to occur with a simple select. I even tried a few CTE queries but couldn't get the syntax accurate due to saleinfo basically being a catch all column I was unsure of how to only extrapolate the data I needed
Create Table sales
(
saleid int
,saleinfo varchar(200)
,salestatus varchar(200)
)
Insert Into sales VALUES
(1, 'Phone Mark customer referral', 'Done'), (2, 'Phone Mark customer referral ', 'Done'), (3, 'Phone Mark - applied for credit', 'Holding Pattern')
,(4, 'Internet Mark', 'Done'), (5, 'Internet Mark', 'Holding Pattern'), (6, 'Internet Mark', 'Holding Pattern')
,(7, 'Phone Stan', 'Holding Pattern'), (8, 'Phone Stan', 'Done'), (9, 'Phone Stan', 'Holding Pattern')
,(10, 'Internet Stan - lives to far to drive', 'Done'), (11, 'Internet Stan', 'Done'), (12, 'Phone Vic', 'Done')
,(13, 'Phone Vic', 'Holding Pattern'), (14, '85623', 'Done'), (15, 'albaca', 'Done'), (16, 'racava', 'Done')
View 6 Replies
View Related
Mar 7, 2014
Here is example table and data
CREATE TABLE [dbo].[sales](
[date_value] [datetime]NOT NULL,
[monthly_total] [int] NOT NULL
)
insert into sales ( date_value, monthly_total)
[code]...
How can I create a query to get below result?
YearMonthMonthlySaleAccumulated Total
2012Jan110 110
2012Feb130 240
2012Mar30 270
2012Apr60 330
2012May10 340
[code]...
View 6 Replies
View Related
Sep 15, 2014
select col1,count(*) from client1..table1 group by col1
union
select col1,count(*) from client2..table1 group by col1
union
select col1,count(*) from client3..table1 group by col1
The results yields
33915
3405
3412
I am trying to get the following result but can't figure out how to get the total in the end.
33915
3405
3412
Total 22
View 2 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
Mar 10, 2014
My Table struct
create table cust
(
cust_id int ,
city varchar(20),
pincode int,
sales int,
latitude float,
longitude float
[code]....
like i wanna display each pincodes how to make a code?
View 6 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
Jun 17, 2014
I have a requirement to calculate the total outage time, based on logged fault tickets, of network nodes. Basically, multiple tickets may be raised for a single node and those tickets could overlap or sequence over a given period; the task here is to calculate the total time (hh:mm) of the outage in the period.
Ex:
3 tickets raised for a node outage over, say, a 48 hour period. Ticket 1 (spanning a total of 5 hours) overlaps with ticket 2 (spans 3 hours) by 1 hour; ticket 3 starts 5 hours after ticket 2 and spans 1 hour. Total outage time on the tickets is 7hrs + 1hr (T1+T2 minus the 1hr overlap) and the full time of T3.
In summary, it's calculating the total ticket time, allowing for overlaps of tickets, etc.
View 3 Replies
View Related
Jun 24, 2014
I have a field Char(10) named bank, and another one Char(15) named namebank.
I need to create a TSQL query, but i need to preserve the white space on my first field named bank and concatenate with the other namebank, this last i dont need white space.
B001 Bank 123
How can accomplish this query.
View 6 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
Oct 30, 2014
I have a SP SPone. i have optimized that and kept it as SPone_Optimized. i would like to test the both SP's execution time to find out how best the optimized one fares.
i planned to test it as follows
declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone_optimized @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'
----- for the SP that is before optimize
declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'
View 9 Replies
View Related
Feb 2, 2015
I need to calculate total discount on item in case when user has several discounts, and they each apply on discounted amount. I thought to have something like:
DECLARE @Disc float
SET @Disc = 0
SELECT @Disc = @Disc + (100 - @Disc) * Disc / 100
FROM UserDiscounts
WHERE UserID = 123
but, seems, it does not work.
Looking for single query without any loops?
View 4 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
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
Mar 30, 2015
We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.
So some sample records of a complete package would look like this:
ItemId | ParentId | Name | QtyAvailable
----------------------------------------
1 | NULL | Package A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
ItemId's 2 & 3 are items contained within the ItemId 1 package.
Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:
Name | Available Qty
--------------------------
Package A | 15
Package B | 100
How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?
View 6 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
