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.
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');
Is it possible expire a report cache after less than one minute? I'm looking for a way to only have a report hit the database once every 10 seconds, no matter how many people are hitting it. Thanks.
Hello Probably a very simple problem, but im stumped. I have a table which gives the start-time and end-time of an employees work day. I want to create a view which contains a line of data for each 5 minute period worked. Please help.
Is there any non-obnoxious way (eg: without have to result to using datepart a million times) to do this? For instance, Oracle provides a function called Trunc which does it, but I cannot find an SQL Server equivalent. Anyone? TIA!!!
I have collected perfmon data that is in every 15 seconds. I need to run a query that will only retrun rows that are 5 minutes from the last row starting at a specific date/time.
What I'd like to do with the sample query above is take an AVG for one hour for the values ZNT, SAT, RAT, RH...like I stated the records are every five minutes in the table, I'd like to create 60 minute AVGs.
hi, i need to synchronize a table from a database to two tables on a different database. I was thinking in a stored procedure to do it and create a job that execute it every minute.
I don't know if this is the right method... maybe it can be a bad decision for the server performance.
Just wondering if anyone knew if it was possible to create a SQL Server agent job that ran once every 30 seconds. I can't enter a value below 1 minute when editing the job schedule in Enterprise Manager.
Alternatively, I can create duplicate stored procedures and set one to run on the minute and one on the half minute. Thought there might be a better alternative to this though and I'll always have to be remembering to makes changes to both sprocs.
I have a situation where I have table with over a billion records and needs to be scrubbed. Table does have a field with date time timestamp. I have been deleting rows from the table using the script below which basically provides me delete statements by date for records older than 90 days.
But now on each day row count is over 30 million rows and it takes forever to delete by date and transaction log becomes humongous.
So I would like to scrub it in 5 minute intervals instead of daily for records older than 90 days. Even in 5 minute intervals the record count tends to be around a million. This will keep the delete slice small enough to not a gigantic transaction log.
declare @startdate Datetime declare @enddate Datetime set @startdate = getdate()-480 set @enddate = getdate()-90
WHILE (@startdate < @enddate) BEGIN print 'delete from vending where DetectedDate < ''' + CONVERT(varchar(10), @startdate, 101) +'''' set @startdate = @startdate+1 END
I am hoping to modify the script above to produce a script with statements like this for a window between last 90 and 120 days:
delete from vending where DetectedDate <'6/15/2015 8:25:00 PM' go delete from vending where DetectedDate <'6/15/2015 8:30:00 PM' go delete from vending where DetectedDate <'6/15/2015 8:35:00 PM' go
Just wondering what is the best time to ensure that we only return data when the datetime field is the same when compared between two datetimes within a minute difference.
As in the following should return the data:
'2015-04-09 09:00:20' compared to '2015-04-09 09:00:50'
And the following should not return the data:
'2015-04-09 09:01:20' compared to '2015-04-09 09:00:50'
The problem, is that I'm merging data from three different result sets, which they all have data for every minute, however, the timestamp can be different by seconds or milliseconds.
So, I'm only interested to return the data when the two fields that I'm comparing are equal within a minute. I need to ignore seconds and milliseconds.
I'm trying to create a query to return Open, Close, Max and Min Price for each 1 minute interval. Source data has two fields - Price, and Datestamp at 5 second intervals.
I can calculate the Max and Min (below) and set the datestamp to the middle of the interval, but get stuck on how to also return the Open and Close price for each interval.
SELECT MAX(price) AS MaxPrice, MIN(price) AS MinPrice, DATEADD(ss, 30, DATEADD(n,DATEDIFF n, '1/1/2006', DateStamp),'1/1/2006')) AS DateStamp FROM MasterData GROUP BY DATEDIFF(n, '1/1/2006',DateStamp)
Right now the database I am working with is storing time inan Integer data type and is storing the time value in seconds.The application does not allow entering seconds. It acceptsminutes and hours.I have a report where it is doing:SELECT SUM(TIMEENTERED)and the SUM is *blowing* up as the SUM is reachingthe BIGINT range.I can fix the problem by changing all codes to:SELECT SUM(CAST(TIMEENTERED AS BIGINT))But now that I ran into this problem I want to find outif storing the time in seconds using INTEGER datatype is the best solution?I've been searching this newsgroup and other placesthe whole day. I even ran into my own three year oldpost. Three years ago my problem was data migrationrelated and now it is more of performance related thananything else.http://groups.google.com/groups?as_...y=2006&safe=offI could not find this specific topic in SQL books likeSQL for Smarties 2005 by Joe Celko (very good stuff ontemporal topics but nothing specific to my question),or Inside SQL Server 2000.Which data type would be ideal and why?smalldatetime?integer?decimal?float?The type of operations that are being done in the databaseare:1- Entering time in hours on work done on a taskFor the data entry part, the application accepts2.5 as 2 and a half hours and it is storing2.5 * 3600 = 9000 seconds.It also accepts entering 2:30 as 2 hours and30 minutes and again storing 9000 seconds.I even saw a page where you can enter clocktime: I worked from 9:30AM to 12:45PMas an exampleWhen i checked the underlying table(s) I sawthat the ENTEREDTIME is always the durationin seconds. So the data entry can either be2.5 hours where ENTEREDTIME = 9000 secondsor9:00AM to 11:30AMwhere STARTDATE is today's date for examplestored as 1/27/2005 09:00AMand where ENTEREDTIME = 9000 seconds2- All kinds of reports showing total time in hoursfor example: Project1 = 18.5 hoursThe code in the SP are all like:SUM(ENTEREDTIME) / CAST(3600 AS DECIMAL(6,2))AS TOTALTIME3- I am sure a lot of other arithmetic calculations arebeing done with this ENTEREDTIME field.What would be the best way to store hours/minutesbased on how we are using Time in the database?Either I will stick with Integer but store in minutestime instead of calculating in seconds and most likelyupdate all the SUM(ENTEREDTIME) toSUM(CAST(ENTEREDTIME AS BIGINT))or I will switch to storing in decimal/float andmaybe avoid doing :SUM(ENTEREDTIME) / CAST(3600 AS DECIMAL(6,2))AS TOTALTIMEsince the ENTEREDTIME would already be storedin hours time.or I will use DATETIME since in the cases ofI worked from 9:00AM to 11:30AMI have to have a separate column to store the date also.I am a little confused I am hoping I will get some helpfrom you and maybe if I can't find the best solution, atleast eliminate the NOT so good ones I am thinking of.Thank you
About a month ago I setup mirroring with our DB, High safety with automatic failover. Ever since the lock waits per minute in the DB went from maybe 2-5 per minute to 22-25 per minute. I am not sure if this was expected or what; but sometimes it spikes even higher than that.
Does this sound off the charts to anyone or normal for mirroring?
When I start SQL Server Agent it's shutting down minute after starting. I tried to set Alerts to send email. After I configured Database Mail ,I was able to send test email , however after that SQL Server Agent started to misbehave.
I have configured an alert like below to track all blocked events in SQL Server across all databases and then kick start a sql job when a blocking happens which inserts data to a table, when there is a blocking in SQL server , i get an email --which is working fine and i am able to track all queries.
but, HOW to get notifications ONLY if BLOCKING IS HAPPENING FOR MORE THAN 30 SECONDS OR 1 MINUTE with out using sp_configure?
---ALERT USE [msdb] GO EXEC msdb.dbo.sp_update_alert @name=N'Blocking Process', @message_id=0, @severity=0, @enabled=1,
We have data that consists of an employee number, a start time and a finish time, similar to the example below
EMP STARTTIME ENDTIME
00001 10-Feb-2012 06:00:00 10-Feb-2012 10:00:00
00002 10-Feb-2012 07:15:00 10-Feb-2012 10:00:00
00003 10-Feb-2012 08:00:00 10-Feb-2012 10:00:00
I am trying to come up with a procedure in SQL that will give me each 15 minute block throughout the day and a count of how many employees are expected to be at work at the start of that 15 minute block. So, given the example above I would like to return
I have a table that I need to do some computations on all the data but first I need to remove the duplicate records and insert the results into a destination table. Here's the example below. My table has 3.1 million rows. I have tried using the DISTINCT and the GROUP BY but both ways to select the data takes about half a minute to run. I'm wondering if there is a way to increase performance. Users are ok with this time since the process runs overnight but improving it won't hurt. I do have a clustered index on these fields but that doesn't seem to improve any.
I measure PLE on my server and insert them every minute into a table. Now, when I look into the table I just dont know how to interpret the following data. I dont understand how is that possible. It's an sql server bug? or? How to interpret that data?
I just need the date, hour, and minute...not the micro seconds. Do I have to DATEPART and concatenate each or is there any way to simply truncate the milliseconds from it? Or is there a date format to put extract and report on it as...
I found in another forum that if I take the seconds and divide them by 15 then round up and multiply them by 4 I can get this done, but I can't figure out how to work it into my select statement. Anyhelp would be greatly appreciated. dbo.SLPTRANS.TimeSpent is the field I am trying to convert.
SELECT dbo.SLPTRANS.ClientID, SUM(dbo.SLPTRANS.TransValue) AS Expr1, dbo.SLPTRANS.TimeSpent AS Expr2 FROM dbo.SLPTRANS INNER JOIN dbo.INVOICE ON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordID GROUP BY dbo.SLPTRANS.ClientID HAVING (dbo.SLPTRANS.ClientID = 405)
Is there a way that I can do this at the table level to automatically handle the rounding of seconds, etc. down to the minute automatically without having to use a trigger?
Here is a very basic example of what I am trying to do:
--example: '09-22-2007 15:07:18.850' this is the value inserted into the table by the code select getdate()
--example: '2007-09-22 15:07:00.000' this is the value I want to store in the table select dateadd(mi, datediff(mi, 0, getdate()), 0)
I'm trying to create a report which will give me a break down of how many unique vehicles have been seen between two dates via a 15 minute interval and what Lane they were seen. My current script looks like this
SELECT l.Name [Name], count(l.Name) Total, p.Created FROM PlateReads p inner join Lanes l on p.Lane_ID = l.ID where LicencePlate in (Select Plate from LPRnet_MelAir_C.dbo.TempVehiclePlates) group by Name Name being the Lane they were in and the Total being the amount of times a unique vehicle has been seen and p.Created being the date they were seen (thats what I need the interval powered off)
Ideally the output would look like this
16/03/201408:00 to 08:15Bus Lane 15 16/03/201408:00 to 08:15Elevated Road150 16/03/201408:00 to 08:15Public Pickup75
16/03/201408:15 to 08:30Bus Lane 13 16/03/201408:15 to 08:30Elevated Road120 16/03/201408:15 to 08:30Public Pickup55
All the way to 12/04/2014
I’ve got it so it says Lane and Count just can’t get the interval part
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
