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');
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.
Actually i posted this problem earlier , hoping that i got the result , did not look in depth untill i realized the values i got was getting was wrong.
The Scenario is still the same.
3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above – if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007.
I am creating a Calculated Member as measure.
Basically if i select the average on 76th day of the year, it should basically be the average of 76th Day ( well offcourse whould ignore the empty cells )
For Example the result set should be very similar , It is basically doing MTD
Running balance is basically adding up , where as 3 month to date average should be average of 3 month previous average
suppose today is 75th day , so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)
The problem i have here is which i am coming close to conclusion
When i use month level it gives me average of month level , the result on month level is fine. But my requirment is to have it on date level. but how do i have rolling average of 3 month in a date level, if i do a date level with 90 days lag which is not correct which is average of 90 days from current day.
AVG([Tbl Date Key].[Report Hirerachy].CurrentMember.Lag(2):[Tbl Date Key].[Report Hirerachy].CurrentMember,[Measures].[Ab1_Avg]
)
When i drill down to date level , which would be assumed
AVG([Tbl Date Key].[Report Hirerachy].date.Lag(2):[Tbl Date Key].[Report Hirerachy].date,[Measures].[Ab1_Avg]
)
its doing a lag on 3 days lag as appose to , I need the lag on 3 months on a day which would be 90 days
should i be doing a lag on days.
The problem at 90 days lag would be every it would lag 90 days average , but what i am looking for it is when it is on the middle of the month it should be
suppose today is 15th day of month, so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)
I am storing duration of a lot of jobs in a column in a table per job. This duration is in seconds and an integer datatype.
Sample data: Job     Duration   date Job1    25      2015/9/23 Job2    30      2015/9/23 Job3    45      2015/9/23 Job4    1       2015/9/22
Now I need to get average duration per day for every month. Is this possible? I have a calendar table that has every single day month year microsecond millisecond  second minute and hour.
Example table structure: Id int, PK Name varchar AddDate smalldatetime
Sample data: Id Name AddDate 1 John 01/15/2005 2 Jane 01/18/2005 . . . 101 Jack 01/10/2006 102 Mary 02/20/2006
First, I need to find the month which has the most records, I finally produced the correct results using this query but I am not convinced it's the most efficient way, can anyone offer a comment or advice here?
select top 1 count(id), datename(mm, AddDate) mth, datepart(yy, AddDate) yr from dbo.sampletable group by datename(mm, AddDate), datepart(yy, AddDate) order by count(id) desc
Also, I'm really having trouble trying to get the overall average of records per month. Can anyone suggest a query which will produce only one number as output?
how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.
Columns are four:
username, current_tenure, move_in_date, and count.
DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)
CREATE TABLE [dbo].[countHistory]( [username] [varchar](50) NULL, [current_tenure] [int] NULL, [move_in_date] [smalldatetime] NULL, [Cnt_Lead_id] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like
Sale Profit Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007 A 100 200 300 20 25 15 B 200 250 50 30 45 19
how can i do following 3 things
1)Add Total column for Sale and Average column for Profit 2)Sort report by lastMonth of Sale (here 7/1/2007) High to low 3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red
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?
i have some classes that I want to group by month/year (note:i dont need the day of the month) how do i wirte my sql so it only gives me the dictinct groups month/year of the classes I have so that it comes out like so.. 11/2006 12/2006 1/2007 3/2007 i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value. dbo.classgiven.classdate date of the class.thank you all SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/" + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL)", conNorthwind )
Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I need this ability in several procedures and wanted to turn it into a stored procedure.IF @ViewRange = 'This Month' SELECT TOP 20 Customer.LastName AS Customer, SUM(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE (MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP)) AND (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY SUM(Sales.AmtCharge) DESC;IF @ViewRange = 'Last Month' SELECT TOP 20 Customer.LastName AS Customer, Sum(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE(MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP) - 1) And (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY Sum(Sales.AmtCharge) DESC; Any ideas?
I have the following script that calculates Sales by month and current year.
We run a Fiscal year from April 1st thru March 31st.
So April 2012 sales are considered Fiscal Year 2013.
Is there a way I can alter this script to get Fiscal Year Totals?
select ClassificationId, YEAR(inv_dt) as Year, cus_no, isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr', isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May', isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun', isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',
[Code] ....
Data returned looks like the following.
ClassificationID Year Cus_no Apr May June .... 100 2012 100 $23 $30 $400 100 2013 100 $40 $45 $600
What I would need is anything greater than or equal to April to show in the next years row.
Hello,I have two tables:[Posts] > PostId, ...[Ratings] > RatingId, PostId, RatingI want to select all posts and add a new column named AverageRating.This new column is the average of all ratings associated to that Post.If a post was not rated then its AverageRating would be NULL.How can I do this?Thanks,Miguel
I am getting the number of transactions for two different months based on dates entered from the user. I need to display the number of transactions along with the average sale. I have figured out how to get the dates and sum the transactions but I can't seem to add in the average....
I am at a loss how to calculate the group footer (Result = 30.88 ) which is equal to (96.82 + 26.70 + 0 + 0 ) / 4 . This is the result from the existing Crystal report. I am trying to convert this report to reporting services. Now I cannot nest aggregate functions and also the row number can be dynamic I am confused how to resolve it.
I was hoping someone could help me with pluging moving average into my report.
This is the template I am using for moving average: // /*Returns the average value of a member over a specified time interval.*/ CREATE MEMBER CURRENTCUBE.[MEASURES].[Moving Average] AS Avg ( [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.Lag(<<Periods to Lag>>) : [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember, [Measures].[<<Target Measure>>] ) // This calculation returns the average value of a member over the specified time interval. FORMAT_STRING = "Standard";
This is what I have before I started to add the moving average. The report works but only displays the cummulative complexity rank.
What I want to do is also add the moving average for this over the parameter of Iterations. (IterationParam). This is what I have so far with the moving average in my report: I know I must have a lot of errors but I can't get past this first error. I highlighted the line with error. The error said: Query(7, 15) Parser: The syntax for '.' is incorrect. (msmgdsrv)
I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:
And the resultant string is: dd/mm/yyyy, for example 30/08/2004. But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?
I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?
I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.
The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.
I've tried evaluating the parts of the episode date seperately like:
where (epimonth>=10 and epiyear=2003) or (epimonth<=2 and epiyear=2004)
and that works, but what happens when someone wants to see from 10/2002 to 2/2004?
I have two tables, table a holds all the votes by users of each element in table b. I was wondering, how do I get the average of all those votes in table a that relate to that each instance of a element in table b. For example table b has two elements created by a certain user that has been voted 5 times each by users with scores like 2, 5, 4, 2 , 2 for both of them. I just need to get the average of those numbers that pertain to those elements in table b. Thanks for any help.
Hello all!This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:11 <-From here1234456 <- To here.7I thought I could solve it by subqueries and do the following:Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9 which would give me what I want, but I realised that when aggregating I cant order the subqueries.This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?
Hi, I have two tables, Weekly and AverageEngTime. Weekly table has 14 columns, "Mon_Day,Mon_Night,Tue_day,....Sun_Night". AverageEngTime table has 15 columns, "Shifts,Mon_Day,Mon_Night,Tue_day,....Sun_Night". I have inserted "Average" as a value for column "Shifts" in AverageEngTime table. Now how do I find the average for each column in Weekly table and insert into AverageEngTime table in the respective columns, and in the row where Shifts = "Average".
I used the following codes to try but i receive errors at where statement.Please correct me. The expected output as below.
declare @Weekday tinyint, @hour int select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())
if (@Weekday= 1 and (@hour>= 7 AND @hour<19)) begin Insert Into AverageEngTime(Sat_Night) where Shifts = 'Average' SELECT AVG(Sat_Night) FROM Weekly end
if ((@Weekday= 1 and @hour >= 19) OR (@Weekday = 2 and @hour < 7)) begin Insert Into AverageEngTime(Sun_Day) where Shifts = 'Average' SELECT AVG(Sun_Day) FROM Weekly end
Can anyone tell me how to calculate a moving average for the below table? -------------------- MONS /vol /counter(the date) jan/8 / 2006-01-01 feb/9 / etc. ........ oct/5 / etc. nov/6 / dec/7 / --------------------- I am using the below code, but it is having no effect...... SELECT i1.MONS, i1.COUNTER, ( SELECT AVG( i2.VOL ) FROM #RES1 AS i2 WHERE i2.MONS = i1.MONS And DATEDIFF( month , i2.COUNTER , i1.COUNTER ) Between 0 And 12 ) AS MovingAverageOver12Months FROM #RES1 AS i1 ORDER BY i1.COUNTER
I made a code to in SQL reporting services for the reports of our company regarding the new applicants. Now I need to get the average datedifference in each phase from all applicants who entered Initial/Data Collection. Example, "Initial/Data collection"PHASE will have an average of 60 minutes to be finished, averaged from all 200 applicants, and "Screening"PHASE 240 minutes to be finished, averaged from 100 applicants.
All applicants must go through stage 1 before he can go to stage 2 and so on.
The date diff will be obtained from the "createdon" column subtracted to the last "modifiedon" column in each phase.
I need to insert that code to the existing code that I have if it is possible(which my boss prefer); or either if i can manipulate it in SQL reporting services in visual studio 2005.
Here is a sample output that I have.Each phase has a different color.
Here is the code that i used SELECT subject, regardingobjectidname, createdon, modifiedon, stat, phase, initial, (CASEphaseWHEN 'Initial/Data Collection' THEN 1 WHEN 'Screening' THEN 2 WHEN 'Assesment and Selection' THEN 3 WHEN 'Placement' THEN 4 END) AS Phasesort FROM( SELECT subject, regardingobjectidname, createdon, modifiedon, stat, initial, (CASEstatWHEN 'Application Recieved' THEN 'Initial/Data Collection' WHEN 'For Screening' THEN 'Screening' WHEN 'Shortlisted' THEN 'Screening' WHEN 'For Assesment' THEN 'Assesment and Selection' WHEN 'For Assesment' THEN 'Assesment and Selection' WHEN 'Passed Initial Interview' THEN 'Assesment and Selection' WHEN 'Passed Profiles Assesment' THEN 'Assesment and Selection' WHEN 'Passed Technical Exam' THEN 'Assesment and Selection' WHEN 'For Placement' THEN 'Placement' END) AS phase FROM( SELECT subject, regardingobjectidname, createdon, modifiedon, initial, (CASEinitialWHEN '1 stage' THEN 'Application Recieved' WHEN '2 stage' THEN 'Application Recieved' WHEN '3 stage' THEN 'For Screening' WHEN '4 stage' THEN 'Shortlisted' WHEN '5 stage' THEN 'For Assesment' WHEN '6 stage' THEN 'Passed Initial Interview' WHEN '7 stage' THEN 'Passed Profiles Assesment' WHEN '8 stage' THEN 'Passed Technical Exam' WHEN '9 stage' THEN 'For Placement' WHEN '10 stage' THEN 'For Placement' END) AS stat FROM( SELECT subject, regardingobjectidname, createdon, modifiedon, (CASEsubjectWHEN N'application received' THEN '1 stage' WHEN 'process application' THEN '2 stage' WHEN 'screen application' THEN '3 stage' WHEN 'Phone interview' THEN '4 stage' WHEN N'initial interview' THEN '5 stage' WHEN 'profiles assessment' THEN '6 stage' WHEN 'technical exam and interview' THEN '7 stage' WHEN 'background and reference check' THEN '8 stage' WHEN 'Job Offer' THEN '9 stage' WHEN 'Contract Signing' THEN '10 stage' END) AS initial FROM(SELECT subject, regardingobjectidname, createdon, modifiedon FROMFilteredTask WHERE (subject IN ('application received', 'process application', 'screen application', 'initial interview', 'profiles assessment', 'technical exam and interview', 'background and reference check', 'job offer', 'contract signing')) UNION ALL SELECT subject, regardingobjectidname, createdon, modifiedon FROMFilteredPhoneCall WHEREsubject = 'phone interview' ) AS Orion ) AS Initials ) AS Phases ) AS Stats ORDER BY regardingobjectidname, initial
I appreciate your help.
Thanks a lot.
__________________________________________________ Your future is made by the things you are presently doing.
SELECT dbo.TechphonesCalibQA.ID, dbo.TechphonesCalibQA.EmpID, dbo.TechphonesCalibQA.EmpName, dbo.TechphonesCalibQA.[Level], CASE dbo.TechphonesCalibQA.G_Branded WHEN dbo.TechphonesCalibClient.G_Branded THEN 1 ELSE 0 END AS G_Branded FROM dbo.TechPhonesCalibClient INNER JOIN dbo.TechphonesCalibQA ON dbo.TechPhonesCalibClient.SesID = dbo.TechphonesCalibQA.SesID
I would like to get the average of column G_branded (Result from the above formula) Any idea?
Apologies for the simplicity of the question, but it reflects mycapabilities! I have the following sample fields coming from differenttables:LocationTimeDate (timestamp)DataI need to return the average of Data per Location per HOUR.Thanks.
Hi AllWe have an order processing database which includes the standard OrderHeader/Order Lines tables. I'm trying to write a query to get the averagenumber of lines per order over a given period, and I'm stuck :-(I can get the number of lines per order with:SELECT COUNT(*) FROM OrderDetailsINNER JOIN Order Header ONOrderHeader.OrderNo = OrderDetails.OrderNoWHERE OrderHeader.OrderDate ..... {various criteria} ...GROUP BY OrderDetails.OrderNumberBut how do I then get an average of this for the period?TIAMike Bannon
I created a query that could give me the % of each product that are ordered alone. I tried modifying it to give me avg check but ran into problems because the when i use max(product) to aggergate the field, it uses the products with the highest first character in the alphabet thus it products with V's and Y's get a lot more results (
Is there an easy way to do this by creating a temp table or nested query?
I can create a nested query to give me the average check for only one product at a time. I'm looking for something that can group the average check by all product.
