Apologies for the simplicity of the question, but it reflects my
capabilities! I have the following sample fields coming from different
tables:
Location
TimeDate (timestamp)
Data
I need to return the average of Data per Location per HOUR.
Thanks.
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:
Avg_AMP:=AVERAGEX('Fct Sales';'Fct Sales'[_AMP]/100)
Avg_AMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_AMPdollar]/100)
Avg_FMP:=AVERAGEX('Fct Sales';'Fct Sales'[_FMP]/100)
Avg_FMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_FMPdollar]/100)
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.
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');
with a result of:
NAME_________________CO
-------------------- --
OFFUTT AFB___________US
MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS
-- ---------- ---------- ---------- -----------
01_________21______-5.31________-30__________60
02_________26______-2.19______-28.3__________61
03_______31.1_______3.61______-26.1__________60
04_______35.6______11.07______-12.2__________60
05_______37.2_______17.2_______-3.3__________60
06_______41.1______22.44__________5__________60
07_______43.3______24.92________7.2__________60
08_______40.6______23.71________5.6__________60
09_________40______18.84_______-2.2__________59
10_______34.4_______12.5_______-8.9__________59
11_________29_______4.13______-23.9__________60
12_________21______-2.52______-28.3__________60
Hi! how do get the number of students who atteded prep with the last 1 hour. and log store the number in a Variable
CREATE TABLE [dbo].[PrepTime] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PrepNo] [int] NOT NULL ,
[Log_Time] datetime NOT NULL DEFAULT, CURRENT_TIMESTAMP,
[coming] [int] NULL ,
[going] [int] NULL ,
[Student_ID] [bigint] NULL ,
[Study_Type] [int] NULL ,
[State] [smallint] NULL
) ON [PRIMARY]
GO
I am working with a company that needs some SQL Reporting developed. What is the going rate for SQL Reporting work?
View 14 Replies View RelatedI am trying to get hourly data on a table which was working fine on mysql but not on sql. how to use this in sql server managmenmt studio, i receive 'time' is not a recognized function name.and curdate() is not recognise function.
select [pa_number], [pa_surname]
,[pa_forename],
sum(time(datetime) >= '07:00:00' and time(datetime) < '08:00:00') as '7.00-8.00 AM',
sum(time(datetime) >= '08:00:00' and time(datetime) < '09:00:00') as '8.00-9.00 AM',
sum(time(datetime) >= '09:00:00' and time(datetime) < '10:00:00') as '9.00-10.00 AM ',
sum(time(datetime) >= '10:00:00' and time(datetime) < '11:00:00') as '10.00-11.00 AM',
[code]....
hi,
I have setup an hourly schedule to run every hour, but how can I choose a start and end time ? This is within subscriptions.
is this possible ?
HiHow can I create a job in sql agent to create a new snapshot every hour?I have, for eg a T-SQL that does it manually.create database Snapshotter_snap_20070418_1821 on( name = Snapshotter, filename ='c: empSnapshotter_snap_20070418_1821.ss')as snapshot of SnapshotterNow, what I do NOT want, is to only have one copy, but rather to do thisevery hour or two through out the day - and keep the old copies for sometime. (In that case, a DROP database, and a CREATE database <generic name>is easy).Any help appreciated,M
View 4 Replies View RelatedFirst time here so please bear with me.Set up a DTS package to export data to an excel sheet on an hourlybasis. Problem is, it keeps appending to the same excel sheet.Any idea how to prevent that. All I want to accomplish is that everyhour, the latest data is in the excel sheet and the previous data isdeleted.Thanks in advance!
View 5 Replies View RelatedFor a GPS utility project we are planning on extracting certain attributes from a huge "GPS Raw Data" read only database which we have access to containing GPS data from several years from several devices attached to vehicles.The data is time stamped. Where the time gap between pieces of data is more than 10 minutes, a new trip is instance is assumed and in our write access "Trip" database we create a new instance for the data clump with a new trip id along with the time range of the data. The process is to be run hourly to update the "Trip" database with new trips and append to overlapping trips. We've some questions:a) Is it easy to read from one database and write into another in c# hourlyb) How would one go about running a C# program automatically every hour on the server?c) Is there a better way to do this than an hourly update? (dynamically perhaps??)d) When querying the database and comparing the time stamps, how for instance would we go about identifying a 10 minute gap when the time/date is in the format "22/12/2007 11:25:00". I can't get my head around actually writing this - it's probably ridiculously simple
View 7 Replies View RelatedAny thoughts as to why I get this error, every hour. How do I fix it?
Be gentle I'm a SQL novice.
Error reported in the Application Event Log.
Package "Hourly Transaction Log" failed.
OS: Windows Ser_2003_sp1
Source: SQLISPackage
Category: None
Type: Error
Event ID: 12291
File Name dtsmsg.rll
File Version: 2005.90.3042.0
Product Name: Mic SQL Server.
Product Version: 9.0.3042.
Time 3:00:01 PM
Type: Error
I have two servers, using SQL server 2000.I was asked for implementing hourly Backup 3 databases in one serverand restore those databases to another server.Could anyone give me the detailed steps to do that?Thanks a lot in advance!
View 13 Replies View RelatedHello everyone,I have around 20 reports in an ASP web-application which connects to aSQL Server 2000 dB, executes stored procedures based on inputparameters and returns the data in a nice tabular format.The data which is used in these reports actually originates from a 3rdparty accounting application called Exchequer. I have written a VBapplication (I call it the extractor) which extracts data fromExchequer and dumps the same into the SQL Server dB every hour. Therunning time for the extractor is an average of 10 minutes. Duringthese 10 minutes, while the extractor seems to run happily, my ASPweb-application which queries the same dB that the extractorapplication is updating becomes dead slow.Is there anyway I can get the extractor to be nice to SQL Server andnot take up all its resources so that the ASP web-application users donot have to contend with a very very slow application during thosetimes?I am using a DSN to connect to the dB from the server that runs theweb-application and well as the other server which runs extractor.Connection pooling has been enabled on both (using the ODBCAdministrator). The Detach Database dialog gives me a list of openconnections to the dB. I have been monitoring the same and I havenoted 10-15 open connections at most times, even during the executionof extractor.All connection objects in the ASP as well as VB applications areclosed and then set to nothing.This system has been in use from 2002. My Data file has grown to 450MBand my Transaction Log is close to 2GB. Can the Transaction Log be aproblem. For some reason, the size of the Transaction Log does not godown even after a complete dB backup is done. Once a complete dBbackup is done, doesn't the Transaction Log lose its significance andcan be actually deleted? Anyway this is another post I'm doing todayto the group.In the extractor program,1) I create a temporary table2) I create an empty recordset out of the table3) I loop through the Exchequer records using Exchequer's APIs, addingrecords into the recordset of the temporary table as I go along.4) I do an UpdateBatch of the Recordset intermitently5) I open an SQL Transaction6) I delete all records from the main table7) I run a INSERT INTO main_table SELECT * FROM #temp_table8) I commit the transactionI hope that the information is sufficientThanksSam
View 4 Replies View RelatedI am using the following query in a view to retrieve the latest 24 hourly records for a site.This returns 24 hourly records for the last day of measurements at a Site.This works great. However, I now need to retrieve the latest hourly records from the current hour. For example, hours will run from 00:00 to 23:00 and if the query is executed at 15:00, I will return only hourly records for 00:00 to 15:00 etc. I believe I need to filter the result set or modify the query to exclude records greater than the current hour.
View 6 Replies View RelatedI am using the below script and I am getting data for 15 minutes interval. I would like to aggregate this data to hourly so instead of reading for 2014-01-01 00:15:00.000 and 2014-01-01 00:30:00.000 I want all the data aggregated for 2014-01-01 00:00:00.000 and then for 2 o’clock. how should I tweak this query to sum the interval values and display it?
SELECT r.MeterId, r.ReadingDate, r.Reading
FROM MeterReading r, MeterDetail d, Building b
where r.MeterId = d.MeterId
and d.BuildingId = b.BuildingId
and b.BuildingName like '%182%'
and r.ReadingDate between '2014-01-01'and '2014-01-10'
order by r.MeterId
Current Output
MeterIdReadingDateReading
3969 1/01/2014 0:000
3969 1/01/2014 0:150
3969 1/01/2014 0:300
3969 1/01/2014 0:450
3969 1/01/2014 1:000
3969 1/01/2014 1:151
3969 1/01/2014 1:300
3969 1/01/2014 1:450
3969 1/01/2014 2:000
3969 1/01/2014 2:150
3969 1/01/2014 2:300
3969 1/01/2014 2:450
3969 1/01/2014 3:000
I am using SSIS package for pulling the data(last 2 months data).
Since the data size is huge, i have to split the data into hourly basis and pull the data.
how i can make this dynamic? Right now i am changing the hours manually after package execution.
I am trying to break down the content based of hourly basis. It works fine when there are values for that specific hour but if there are entries or values for a specific hour then it returns null instead of 0. How not to get null instead get zero.
Here is the code below:
With temp_exp As
(Select pl.state,Cast(signeddate As date) As signatureDate, signeddate As DoneTime From contract c with(nolock) where c.signeddate>DateAdd(Day, Datediff(Day,0, GetDate()), 0)
)
Select
Sum(Case When CONVERT(varchar(8),DoneTime,108) Between '07:00:00' And '07:59:59' Then 1 Else 0 End) '8AM',
[Code] ....
I have SharePoint 2010, which I have uploaded a PowerPivot model onto.
Currently it doesn't seem like I could setup the Data Refresh service to refresh my model more frequent than once a day. The Data Refresh configuration page looks like this:
Which doesn't show an option for anything more frequent than daily.
I have also tried to refresh the model's database directly on the Tabular SSAS instance (which SharePoint is using to store PowerPivot models) via SSIS or XMLA, but I get an error saying the tabular model is in "ReadOnly" mode, which I could potentially bypass (by detaching and re-attaching the model), but thats starting to sound abit too hacky.
Is there any way I could refresh my SharePoint uploaded PowerPivot model more than once daily?
This is the code I have written and I am trying to retrieve minimum count of PQpageId for every hour for a given date of range.
WITH CTE AS (
SELECT PQIM.PQPageID
,PQIM.PageURL as PageDescription
,CONVERT(Date,NCPI.RequestDateTime) AS [Date]
,DATEPART(HOUR,NCPI.RequestDateTIme) AS [HOUR]
,ISNULL (COUNT(NCPI.PQPageID),0)AS HourlyPQPageIdCount
FROM dbo.NewCarPurchaseInquiries AS NCPI WITH (NOLOCK)
[Code] ....
This is the output I get :
PQPageId Date HOUR MINCOUNT
-------- ---------- ---- --------
1 04-11-2015 8 2359
1 05-11-2015 8 2332
1 06-11-2015 8 2008
1 07-11-2015 8 1964
1 08-11-2015 8 2139
1 09-11-2015 8 54
[Code] ....
But I am expecting
PQPageId Date HOUR MINCOUNT
-------- ---------- ---- --------
1 09-11-2015 8 54
1 11-11-2015 9 10
1 11-11-2015 10 4
2 11-11-2015 8 10
2 11-11-2015 9 2
2 11-11-2015 10 1
For ex: Pqpageid 1, at 8am on date 4-11-2015 has a count of 2359 and on 9-11-2015 at 8 am it has count 54then it should return date 9-11-2015 and count 54 for hour 8 like wise for every pageid and hour from 8 to 23 it should return the min count from given date of range.
We have some reports run quite slow because the queries are complicate and tables are large. So we create a materialized view in Oracle which store the result from the query and refresh it occationally. How to do this in MSSQL? I try the indexed view but seems it has lots of restrictions, our query has sub queries and cross database table joins so can't use the indexed view. Any other object or temp table can be used to cache the report data and be accessed globally by other procedures?
Any suggestions are welcome,
Cheers
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
View 2 Replies View Relatedif I have a bunch of times, and want an average of them, how do I do that?
Thanks.
For more details, see this thread:
http://www.dbforums.com/showthread.php?t=1215633
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....
(@prevMonthStart datetime, @prevMonthEnd datetime, @thisMonthStart datetime, @thisMonthEnd datetime)
as
select sum(case when orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end ) as PrevMonthCount,
sum(case when orders.orderdate between @thisMonthStart and @thisMonthEnd then 1 else 0 end ) as ThisMonthCount
(how do I add in the average for both months? The column is orders.ordertotal)
Using MS SQL 2005 server how can I get the average cost price of item 'shirt' from this table please?
code item description cost retail
---- ---- ----------- ---- ------
191 shirt shirt - white 2.30 9.99
170 pants pants - beige 6.34 15.00
196 shirt shirt - red 2.64 9.99
199 shirt shirt - blue 2.61 9.99
Hi !
I am having this problem. I have a grouping someting like this.
Group1
Resolved Resolution Time Average Resolution Time
30 2904.46 96.82
54 1442.03 26.70
0 0.00 0.00
0 0.00 0.00
------------------------------
Avg : 30.88
Now to calculate the average inside the grouping I have used this
iif(Sum(Fields!ResolutionTime.Value) = 0 ,"0.00",(Sum(Fields!ResolutionTime.Value)/(iif(Count(Fields!ResolvedDate.Value)=0,1,Count(Fields!ResolvedDate.Value)))))
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.
Any ideas will be greatly appreciated.
Thanks in advance.
--Abbi.
Hi All,
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.
WITH
MEMBER [Measures].[Date Key] AS
[Date].[Date].CurrentMember.UniqueName
SELECT
{
[Measures].[Date Key],
[Measures].[ComplexityRank]
} ON COLUMNS,
(
[Work Item].[System_State].[System_State],
(StrToMember(@StartDateParam):StrToMember(@EndDateParam))
)
ON ROWS
FROM [Team System]
WHERE
(
STRTOMEMBER("[Team Project].[Team Project].["+@Project+"]"),
STRTOSET(@IterationParam),
STRTOSET(@AreaParam),
STRTOSET(@WorkItemTypeParam)
)
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)
WITH
MEMBER
[Measures].[Date Key] AS
[Date].[Date].CurrentMember.UniqueName,
[Measures].[Moving Average] as
AVG({[Date].[Date].currentmember.lag(@IterationParam):
[Date].[Date].currentmember},[Measures].[ComplexityRank])
SELECT
{
{measures.[moving average],measures.[Internet Total Product Cost]} on 0,
[Date].[Date]. Members
[Measures].[Date Key],
[Measures].[ComplexityRank]
} ON COLUMNS,
(
[Work Item].[System_State].[System_State],
(StrToMember(@StartDateParam):StrToMember(@EndDateParam))
)
ON ROWS
FROM [Team System]
WHERE
(
STRTOMEMBER("[Team Project].[Team Project].["+@Project+"]"),
STRTOSET(@IterationParam),
STRTOSET(@AreaParam),
STRTOSET(@WorkItemTypeParam)
Does anyone have any suggestions?
Thanks,
Nici
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.
View 4 Replies View RelatedCan any one be able to help me to write a querry on Moving Average
Example
Product Volume
Fish
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?
View 9 Replies View RelatedSelect * from personnel
where salary > ALL (SELECT AVG (salary) from personnel group by bolno)
And how i find max average salary?
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.
Shifts | Mon_day | Mon_Night | Tue_Day.....
Average | 5.2 | 10.2 | 15.2........
The codes that i tried..
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
Problem
fldyear fldascending flddecending
2007 20 23
2006 21 14
2007 41 12
2007 12 5
how do i find the average of fld ascending and descending where year=2007
Your help will be appreciated
How do you get the average number in sql?
View 11 Replies View Related