Out of this table what I need to do is calculate the average value for each same [Name]. As you can see from the sample set above, the Diameter1 has 2 entries value hich gets store at different time of course.
So I I take the example of Diameter1 I need to calculate and display in a field the average results.
The result would be
Name Average
=====================
Diameter1 .....
Diameter2 ....
Temperature2 ....
Temperature1 ....
How can I perform this ?
Or could it be better to get a view of the table above which gets display as follow :
I am trying to create a SSRS report that needs to show average for both parent and child rows. In the example below i need to show an average of ErrorRecAge column for Company, then for cic_Group and one grand total/avg. How can this be done from single query (as below) and using SSRS built in functions/codes? I am also curious to know if this can be done from TSQL directly (ex. something similar to running sum/total). I appreciate the help.
Select
CustomerCorrection.Id As ParentId,
cc_company As cc_company2,
ccError.Id As ErrorId,
COALESCE(cic_Group,'Other Errors') As cic_Group,
COALESCE(cic_Code,'Unknown') As ErrorCode,COALESCE(cic_Description,'Unknown') As ErrorDescription,
DateDiff(hh,cc_entrydt,getdate())/24.00 AS ErrorRecAge
Im trying to get the average Fuel Consumption for A Manufacturer that produces two or more cars, so far ive only been able to find all manufacturers Average Fuel consumption.
Heres what I have so far
Select aManufacturer.MName, avg(FuelCons) From aCar Join aBuilts On aBuilts.CName = aCar.CName Join aManufacturer On aBuilts.MName = aManufacturer.MName Group by aManufacturer.MName
This produces nearly all I want only I need to be able to get only the Manufacturers who produce two or more Cars, ive tried implementing a few Count statements but nothings working, any ideas?
I need to calculate average of each student and then get the highest.
My try:
Code: SELECT Students.StudentId,Students.StudentName,AVG(Grades.Grade) AS avg_grade FROM Students s JOIN Grades g ON Grades.StudentId =Students.StudentId GROUP BY Students.StudentId, Students.StudentName
ORDER BY avg_grade LIMIT 1 FROM Students;
I encounter problem with this code, maybe it's Completely wrong...
I am having tables with 12 columns as jan,feb,.......dec.
Now I want to calculate average salary for each record, but condition is that if any month salary is zero then that column also exclude from average calculation.
For example : if jan and feb column values are zero then i want to calculate (mar+apr+...+dec)/10.
Eg: I have productivity records of each day and each employee. I need to calculate avg of each last level employee productivity by monthly. Again Last level employee productivity avg must be added up with their immediate head. But, when I define a Measure Item as avg in the cube, it sums all the values of lost level employees & head and divides with number of records (normal avg).
My requirement is calculating each head avg by sum of each last level employee avgs / no of employees. If head having value, he too will be added. Again Head’s Avg will be added up immediate head.
The following calculation gives average value at each level.
Average of Level 3(E4) = > (4+3+7+6)/4 = 5 hrs/day< = (E6+E7+E8+E4)/4
Average of Level 2(E2) = > (5+3+5)/3= 4.333 < =avg(Level3(E4))+avg(Level3(E5)))/2
Average of Level 1(E1) = > (4.333+4+1)/3 = 3.111 < = avg(Level3(E2))+avg(Level3(E3)))/2
Formula for average of level : : (Sum of Children value + Head Value of Corresponding children) / (No.of Children +1)
I want to calculate average of each employee as well as average of each level in cube (SQL Server Analysis Services).
I am having trouble with calculating AVG in a matrix. I have done the subtotal but I would like to know how to calculate the Average. I would also like AVA below the Total.
Link : [URL] ....
I have also added an image oh how the matrix looks like.
How you would calculate the average read/write latency experienced by a SQL Server instance during a specific time window in order to monitor this for multiple instances. From this MSDN blog, I know that you have to take multiple samples and do some calculations to get the correct latency.
[URL] ...
However, the SQLServer:Resource Pool Stats object tracks these numbers per resource pool and we want to get one number for the whole server. Since there can be a different base value for each resource pool, you can't simply sum the numerator values together. Here's some sample data from a server that illustrates the problem.
object_name counter_name instance_name cntr_value cntr_type SQLServer:Resource Pool Stats Avg Disk Read IO (ms) default 307318919 1073874176 SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base default 25546724 1073939712 SQLServer:Resource Pool Stats Avg Disk Read IO (ms) internal 2045730 1073874176 SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base internal 208270 1073939712
I'm thinking I would need to do some sort of weighted average, but I'm not sure if that will result in the correct value. Here's the formula I am thinking about using currently before doing the calculation over time
I have got this matrix and I am trying to calculate the average amount of working days in a month. At the moment, I have divided the total number of jobs by 21 for every month which is a hard coded value. However, I am not sure how to retrieve this value dynamically. Is there any formula that can find out the working days?
heyas, i wanna know how can i select only entries with at least 2 rows? What i mean is that i want to make a select * from table1 where columnA = 'Something' order by columnB
but addicionally i want it to return only register that happen at least 2 times for columnB
I have a table with four columns: id, value1, value2, and name. I need to select all duplicate rows with the same name in the name field. Once selected I need to average the value1 and value2 rows with each other. Then I would like to delete the duplicate rows and replace with the new averaged values. Should I first move the duplicate rows to a temp table and then average? Any help would be appreciated.
1. Is it possible to use the subtotal as a field for calculating values? 2. Can I add another row to the subtotal area, having two subtotal rows?
I need to achieve the following output:
Months 1 2 3 101200 Cust1 2008 50 40
2007 45 45 Subtotal +5 -5 102300 Cust2 2008 70 80
Subtotal 0 0
What I want is to use the subtotal as a variance-field for the two selected years by the user. It could happen that a customer doesn't generate turnover in one year, then the subtotal has to be 0 or 100 or whatever.
If my suggestions aren't applicable then I'd like to know If there is another possibility to display the desired results.
I have three columns in my table with the following datatypes:
Date - DateTime Height - Decimal Change_From_last_Height - Decimal
I am using "SQL Server 2005 Express Edition". I'm fairly new to SQL and would greatly appreciate any help or advice I can get.
The "date" column increases by an extra day in every new row and I then enter the new height of the plant. What I want to know is how I can get SQL server express to automatically enter the difference in height between the current row's height and that of the previous row.
Is it possible to automate the entry in the Change_From_Last_Height column in SQL?
Put another way, I know how to find the difference between two values in the same row but different columns, but how do I calculate the difference between values in adjacent Rows (ie. Rows next to each other)?
Hi,I'm am looking for a little help. I need to create a SQL view whichjoins a few tables, and I need to return an average for a particularcolumn where a second duplicate ID exists...Heres an example of how the results could be returned...ID | Name | Order No. | Value---+------+-----------+---------5 | test | 1234 | 35 | test2| 1234 | 45 | test3| 1234 | 35 | void | 1235 | 55 | void2| 1235 | 65 | void3| 1235 | 55 | void4| 1235 | 7ID is my main join which joins the tablesName is a unique nameOrder No is the same for the different names, I only need to return onerow with this order no, and the first name (the rest are irrelevant)Value is the field which I wish to return as an average of all 3, 4 orhowever many rows is returned and share the same order no. This iswhere I get totally lost as I am pretty new to SQL. Can anyone provideany help on how I would go about limiting this query to the uniqueorder no's and returning the average of the value field, and I can takeit from there with my own tables.Thanks for your helpstr8
I have two tables that look like this (below). One tells me the name of my product, the Amazon Category it is in & the amount that I want to sell it for. The other tells me the Category & the fee for that category. So far so good. Though it gets tricky in the sense that some categories have two tiers. So in Electronics, the fee for $0.00 - $100.00 is 15%. But from $100 and up it is 8%.
Since it has two columns & both of the new columns pertain to the fee of my product, I can't figure out how to use both at once. For my $599.99 example it would be ($100 * 0.15) + ($499.99 * 0.08) = $55.00. Would I pivot the data? If not, how would I group it to be considered together?
Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:
The results were, respectively: 701,68; 2120,60...; -669,441; and finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:
test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17 test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...
So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.
I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.
If I wanted to search for Jobs as a particular status (e.g. 0130) and wanted to keep the jobs at this status until it has reached 0500, 0125, or 0900 in it's subsequent status log entry, how can I write the SQL for it to achieve it?
I have the following SQL which searches for the Jobs at 0130, but don't know how to develop it further to search on the requirement above.
------ SQL ------- SELECT job.job_number, (SELECT MAX(jsl.job_log_number) FROM job_status_log jsl WHERE job.job_number = jsl.job_number AND jsl.status_code = '0130') as Last_Early_Warning_Status_Entry
[code].....
In the job_status_log table above, there is a job_log_number field which increments by 1 when there is a new status log entry.
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged... I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25. The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement. ?confused?
Here's the original query. accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '
SELECT name, country_cd from weather_station where platformId=&&platformId;
SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS FROM daily WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime GROUP BY to_char(datetime,'MM') ORDER BY to_char(datetime,'MM');
I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem:
id metric_id metric_type_id metric_name 1 80 2 Fiscal Enterprise Value Historic Year 1 2 81 2 Fiscal Enterprise Value Current Fiscal Year 3 82 2 Fiscal Enterprise value Forward Fiscal year 1 4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2 5 101 3 Calendar Enterprise value Historic Year 1 6 102 3 Calendar Enterprise Value Current Fiscal Year 5 103 3 Calendar Enterprise value Forward Year 1 6 104 3 Calendar Enterprise Value Forward Year 2
Table Name: metric_type_details
id metric_type_id metric_type_name 1 1 Raw 2 2 Fiscal 3 3 Calendar 4 4 Calculated
The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder:
Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81.
Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82,
metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally
metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use).
As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes:
-- Dummy year variable to make it easier to use MONTH() function -- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc... DECLARE @DUMMY_YEAR VARCHAR(4) SET @DUMMY_YEAR = 1900;
with temp(security_id, metric_id, value) as ( select ml.security_id, ml.metric_id, ml.value from metrics_ladder ml where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...) -- only consider securities with fiscal year end not equal to december and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec') ) insert into @calendar_averages select temppivot.security_id -- Net Income ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103] ,NULL as [104] -- Share Holders Equity ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107] ,NULL as [108] -- Capex -- Sales -- Accounts payable etc... .. .. from temp pivot ( sum(value) for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...) ) as temppivot inner join company_details cd on temppivot.security_id = cd.security_id
********* END SQL *********
The result then needs to be unpivoted and stored in metrics_ladder.
And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET.
Hi, I am searching for the most easy SQL solution:
Lets say i have 500000 rows of cars in one table with 30000 different car ID's. This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?
I have a table which stores phone numbers of a customer in a table.
Say this table is as below
CustomerName - PhoneNumber Customer 1 - Phone number 1 Customer 2 - Phone number 2 Customer 2 - Phone number 3 Customer 3 - Phone number 4
What would be the best approach to prevent adding another entry against Customer 2. I should be able to add new customers and add multiple phone number against all other Customers. The restriction should be only against Customer 2.
I am trying to pull the last three entries from a table in my database but I am having trouble writing the correct query. The database has multiple entries for each item in my database but I want to pull just the last three for each item. I have tried the TOP function with the items ordered in descending order but that only works when I define each item seperatley. I need to know how to pull the last three entries for every item I have in the table.
Without technical information and DB design, I wish to know the maximum entries in MS SQL in million. I guest for access are a limit of 2 million and a 2GB DB. How much for MS SQL in max. entries and how much in GB?
I'm extracting data from a log (log_history) of patients where nurses perform various actions on a call, such as assessing and reassessing, despatching etc. This is the script:
Select L.URN, LH.THE_TIMESTAMP, LH.ACTION_TYPE, LH.ACTION_BY, LH.ACTION_REQD, LH.NOTE, em.position_type_ref From LOG L Join Log_history LH on (L.URN = LH.LOG_URN) left outer join employee em on (em.code = LH.action_by) Where (L.Taken_at >= :DateFrom and L.Taken_at <= :DateTo) and (LH.ACTION_TYPE = 'D') and (em.position_type_ref ='NU')
Order By L.URN ASC, LH.THE_TIMESTAMP DESC
The result I get shows duplicate 'timestamp' entries and I only want to return unique timestamp entries. Does anyone have any ideas. I'm self taught and have hit a wall
I need help desperately. The following is an example of the data that I have.
I have two tables one name Customers and the other named Orders. The tables have the following attributes
CUSTOMER CustomerNum, CustomerName, OrderNum
ORDER OrderNum, OrderDesc, CustomerNum
Bare in mind that the CustomerNum can have multiple customers attached to it, example a whole family (I know bad database design but it's too late to change) Let's use the following info for the tables respectively
The query i'm using is select Customer.CustomerNum, Customer.CustomerName, Orders.OrderDesc from CUSTOMER, ORDERS where CUSTOMER.CustomerNum = ORDER.CustomerNum
I have an application that allows the user to enter data into a table. There are multiple users so I put in some code that, I thought, would keep 2 users from creating a new record at the same time. The IDs for the records are identical and this is causing a problem.
The IDs are in the format of ####-mmyy. at the start of each month the #### part goes back to 1.
We tried a test today where we had 2 users click on the New button at exactly the same time. The IDs that were created were identical. Is there anyway on the database that I can prevent this from happening?
Here is how I create the new record id:
I get the MAX(ID) from the table I add 1 to the ID and then insert a new record with the new ID into the table.
Hi all can some one there please let me know how to configure error logs. what i observe on some of the servers error log got entries for all conenction, database backup, logbackup etc..... but on some there r entries only about database backup.
