AVG CALC
Feb 10, 2006
New SQL - but certainly enjoying the experience.
I need to calculation an Average in SQL -
A view I created contains:
Item WorkOrders TotalProduced
A 10 125
B 1 10
C 3 48
Desired Results
Item AVGPRODUCED
A 12.5
B 10
C 16
The view will be used by a Crystal Report so I could calculate in Crystal - But I was wondering how to in SQL
Any help would be appreciated
View 6 Replies
Jun 29, 2007
I have a report that requires 2 "tables". The first table summarizes total
lbs by a category and then provides a company total at the end:
cat 1 75
cat 2 100
cat 3 200
-------
total 375
The second table needs to display the % of the total for each category:
cat 1 20%
cat 2 27%
cat 3 53%
-------
total 100%
How can I reference the company total for doing the calculations for the
second table? I am working with Visual Studio 2003. My dataset pulls a
file from the AS400 using an sql statement.
Thank you, PB
View 4 Replies
View Related
Apr 6, 2005
hi,
I'm trying to calculate how much unused space i have on one datafile. My main goal is to determine the max space i can save by doing a dbcc shrink.
Any help is greatly appreciated.
ThanKs in advance.
Al
View 1 Replies
View Related
Jan 24, 2006
Hi people. I hope you can help with this. It has me completely stumped.
I am trying to get SQLS 2000 to calculate days between dates, without any success at all.
The two dates are as imported from a text file, and are as follows: -
(@issueddate=) 3/01/2006 3:37:00 PM
(@completiondate=) 04-FEB-2006 18:00
I cannot do much about the date formats, as they are as received from another party, as an email attachment.
I have tried just about everything I know to get this working, even tried commenting out each line till practically nothing is left, and still get the same message. Have also deleted the procedure and recreating it.
I have tried removing "PM" from the first date; tried changing varchar to datetime; tried using 'dd' instead of 'd' in the DateDiff function
Because of the "Aborted" response in Query Analyser, nothing is returned, and yet the Query Analyser's "Execute"
Command reports it completed successfully.
Should I ignore this "Aborted" response altogether and assume that it works?
Is it a known bug in SQLS 2000?
Here is the Stored Procedure:
----------------------------
CREATE procedure proc_calcDeadline
(@issueddate varchar(30)='',@completiondate varchar(30)='',@return int=NULL output)
as
declare @Period int
--set @issueddate='3/01/2006 3:37:00 AM'
--set @completiondate='04-FEB-2006 18:00'
set @period=datediff(d,@issueddate,@completiondate)
--print 'period=='+cast(@period as varchar)
set @return=@period
return @return
GO
The DEBUG response in Query Analyser is as follows: -
"Aborted"
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
When I make changes in Query Analyser, (ie, "Alter Procedure', etc" then select "EXECUTE" option
the response is: -
"The command(s) completed successfully."
PS I am STILL unable to get fully into Query Analyser for step-by-step processing, even though I created the database on a single workstation (and therefore am the dbowner, with full Admin rights).
(Homesick for VBA territory, where I can get date calcs like this done easily!!)
Lester Vincent
Sydney
View 5 Replies
View Related
Mar 26, 2008
I use the NETWORKDAYS(start_date, end_date,holidays) function in Excel and Access regularly. I'm trying to find a similar function in TSQL to use on Server2005. NetWorkDays allows me to calculate the difference between my ticket open and close dates while excluding weekends and holidays, (we are nice to our staff and let them off).
I'm trying to find an easy way to do this in TSQL without writing a large amount of code.
I will greatly appreciate any help.
View 1 Replies
View Related
May 19, 2014
I created a dbo.Calendar table that stores dates and a work day flag (1=work day, 0=non-work day) so I can use it to calculate the next business date from a date using a function. I'm using a while group to count only the work days and a couple other internal variables but I'm not sure if I can even use them in a function.
Assuming Sats & Suns are all non-work days in April 2014, if my @WorkDays = 10 for 10 work days and my @DateFromValue - 4/1/2014, I would expect my return date to be 4/15/2014.
------ Messages after I click execute on my query window that has my function ------------------------------------------------------
Msg 444, Level 16, State 2, Procedure FGetWorkDate, Line 19
Select statements included within a function cannot return data to a client.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 20
Invalid column name 'WorkDay'.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 22
Invalid column name 'Date'.
------ my function code ----------------------------
CREATE FUNCTION [dbo].[FGetWorkDate](
@WorkDays VARCHAR(5),
@DateFromValue AS DateTime )
RETURNS DATETIME
[Code] ....
View 10 Replies
View Related
Nov 15, 2007
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)
View 1 Replies
View Related
