SQL Server 2008 :: Complex Mode (average) Calculation
Aug 20, 2015
I've been asked to find the Mode of the loads on trips we run. So I run a count on the [loads] (without grouping, in an analytical function) column, then sort on the highest counts. The problem is when there is a tie. In that case, I need to find the mean of all the tied values. This means that if there is no mode at all, it would simply take the mean of all the values.
Here is sample data:
CREATE TABLE test6
(
trip char(1) NULL,
date int NULL,
loads int NULL
[Code] ...
And here is what I'd like to see:
CREATE TABLE test7
(
trip char(1) NULL,
loads int NULL
)
insert into test (trip, loads)
values ('A', 15)
insert into test (trip, loads)
values ('B', 17)
insert into test (trip, loads)
values ('C', 11)
View 1 Replies
ADVERTISEMENT
Jun 21, 2015
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.
View 2 Replies
View Related
Aug 3, 2015
I have a set of data and calculated already the average of a specific measure, incl. the Standarddeviation of this measure. Now I want to create a average measure of the values on row level which arehigher than [Avg]-[StdDev] AND lower of [Avg]+[StDev].As far as I understand I have to check on Rowlevel, which means on Date and Brand Level, if the value of the measure is above or lower than the Mean +/- StdDev.
SUM( FILTER( {[Dim 1].[Attribue].&[selected]},[Measures].[Fact Survey Count] > [Measures].[Mean + 0,5 StDev] AND [Measures].[Fact Survey Count] < [Measures].[Mean - 0,5 StDev] ),[Measures].[Fact Survey Count])
which leads to a wrong result. This one counts all rows if true.With Descendants I got a #Value-Error, probably because I did not understand how the calculations work. I planned to include this in a IIF calculation.
SUM( Descendants(
   {[Dim 1].[Attribute].&[selected],
   [Dim Date].[Date].[Date].CURRENTMEMBER}, Leaves), [Measures].[Fact Survey Count])
View 4 Replies
View Related
Apr 1, 2008
>I'm fairly new to SQL server 2005
Quote:
View 1 Replies
View Related
Jul 11, 2006
Hi
I have got two tables and have one to many relationship on them.
i ll show you the sample data
Table 1
ID DoctorName Practice Full_Time Cost Expenditure
1 ABC qw yes $100
2 ABD wer no $566
3 ZXA ddf yes $22
........................
table 2
Practices
qw
wer
ffgg
hhjk
ddf
..
The scenario is like this
a doctor can work in more than one practice. and he can be a full time practioner or a part time practitioner
The problem is if a doctor is a full time practioner in 1 service and a part time practitioner in another, 75% of cost goes to first practice and the rest 25% goes to other
if the doctor is non full time in more than one practice, the cost gets distributed equally in all practices.
how shall i do this???
pls HELP!!
View 1 Replies
View Related
Jan 8, 2008
Hi,
Can someone please guide me how to extract this particular data. It is quite complex, the raw data is not in a good shape and I am not sure if it is even possible. For simplification, I am copying a sample of only 3 columns from my table.
ID FromDate ToDate
01 06/09/2006 06/09/2006
02 06/09/2006 06/12/2006
03 06/09/2006 06/12/2006
04 06/09/2006 06/12/2006
05 06/09/2006 06/12/2006
06 06/09/2006 06/12/2006
07 06/10/2006 06/10/2006
08 06/12/2006 06/12/2006
09 08/30/2006 09/05/2006
10 08/30/2006 09/05/2006
11 08/30/2006 09/05/2006
12 09/13/2006 09/17/2006
13 09/13/2006 09/26/2006
14 09/13/2006 09/26/2006
15 10/05/2006 10/10/2006
16 10/05/2006 10/10/2006
17 10/10/2006 10/15/2006
18 10/11/2006 10/11/2006
19 10/16/2006 10/16/2006
20 10/20/2006 10/22/2006
21 10/23/2006 10/25/2006
From and to date represent a duration of an event, but in some instances the data has multiple lines but they are all to be considered one event. For example all the from dates (row 1 to 8) in Oct 06 are covered by ID/row 05 because 6/9/06 to 6/12/06 is one event, all other rows of June 06 data are just redundant and not needed in the resultset. There isn't a unique event name either so comparing and picking row 05 just based on the above data is an issue.
Another problem is for example the dates in October (10/5 - 10-16) all represent 1 event but the date span is spread all over. Multiple events are also possible in one month, so gettign MIN and MAX based on the month will not work.
The desired result set need to have duration of each unique event for example (1 in June, 1 in August, 1 in Sept, and 1 in Oct) and also need to have the number of events after that date and the calculated from and to date of that event:
ID FromDate ToDate Events after this date NextEventFromDate NextEventToDate
05 06/09/2006 06/12/2006 4 08/30/2006 09/05/2006
09 08/30/2006 09/05/2006 3 0913/2006 09/26/2006
13 09/13/2006 09/26/2006 2 10/05/2006 10/16/2006
? 10/05/2006 10/16/2006 1 10/20/2006 10/25/2006
? 10/20/2006 10/25/2006 0
View 3 Replies
View Related
Sep 14, 2015
At the 2nd level, the calculated measure will only be correct if it is Averaged. And at the 1st level, the calculated measure will only be correct to take these Average value from 2nd level and Sum then up together.Â
Level 1 Level 2
A Â Â
X1
X2
=================
(Avg of X1 + X2) Â
B
Y1
Y2
Y3
=================
(Avg of Y1 + Y2 + Y3)
===================================================
(Sum of (Avg of X1 + X2) Â +Â (Avg of Y1 + Y2 + Y3) )
Currently, Instead of summing all the 3 averaged value, it is averaging against all the items like,
(Avg of X1 + X2 + Y1 + Y2 + Y3)
My MDX currently looks something like thatÂ
[Measure].[Value] / [Dimension].[Attribute.count] -> so i can get the avarage at the 2nd level but it doesn't require on the 1st level but retaining this value.
How can i do an average on the leaf level and using these average values to sum at parent level?
View 4 Replies
View Related
May 8, 2015
I am trying to get a date diff in a named calculation using inner join I am getting
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. #
I have checked online and have been able to get some of my codes right by using the where condition in place of the inner join, but I have not been able to work around the datediff one
(select
DATEDIFF(day,TDate,[DID])
FROM [dv].[dbo].[Dail] as D
inner join
[dv].[dbo].[ANT] as A
on
a.AID = d.AID)
View 0 Replies
View Related
Sep 16, 2015
Any good way to add a percentile calculation to a table in sql server 2008 r2?
I've got 4 fields and I'd like to add a 5th labeling the records according to the percentile they fall in for one of the fields.
View 4 Replies
View Related
Mar 23, 2015
I have Two tables @master and @child
Master Table :
MasterID EntryNumber BranchId IsstockIn
1 1 1 1
2 1 1 0
Child Table:
CEntryNumber CBranchID EntryQty
1 1 10
1 1 20
1 1 -5
1 1 -4
My Query:
Select SEC.EntryQty from Item.StockEntryChild SEC
where SEC.CEntryNo =
(
select SEM.EntryNumber from item.StockEntryMaster SEM
where SEC.CBranchID=SEM.BranchID and SEC.CEntryNo=SEM.EntryNumber and SEM.MasterID=1 and SEM.isStockIn=1
)
My Result:
EntryQty
10
20
-5
-4
Expected Result:
10
20
View 6 Replies
View Related
Mar 13, 2015
I have below records coming in from source files
ProdName Amount TranType
P1 100 A
P1 100 S
P2 200 A
P2 205 S
In case the ProdName is same, and Amount = or (within +/- 5%) of Amount, I have to update the TranType column as IN/OUT respectively as shown below in the tables.
I am okay with using 2 different tables if needed as in the records comes in one table and then i can reference that table to upload the values in another.
ProdName Amount TranType
P1 100 IN
P1 100 OUT
P2 200 IN
P2 205 OUT
The order of the records coming in can be different order, they need not be subsequent.
This is happening in SQl Server 2008.
View 3 Replies
View Related
Mar 5, 2015
do you have a general rule of thumb for breaking a complex query into temp tables? For someone who is not a sql specialist, a query with more than a few table joins can be complex. So a query with 10+ table joins can be overwhelming for someone who is not a sql specialist.
One strategy is to break a problem into pieces so to speak by grouping together closely related tables into temp tables and then joining those temp tables together. This simplifies complex SQL and although not as performant as one big query it's much easier to understand. So do you have a general rule of thumb as far as a threshold for the number of joins you include in a query before you break the query into temp tables?
View 9 Replies
View Related
Mar 4, 2015
I have tried everything I found on Google, but nothing seems to be working. Already Ran alter command set multi_user, but getting an error.
"Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1..ALTER DATABASE statement failed."
View 8 Replies
View Related
Mar 10, 2015
Recently we had multiple production failures because one database was in single-user mode. I am trying to recreate this scenario to write a script that will kick out that connection that was holding database in single-user.In SSMS window 1 I run the following code:
use master
go
alter database test
set single_user
with rollback immediate
[code]....
Yes, it shows one record, but the database name is master, not test. And indeed, I run it from the context of master. Also tried sp_who, sp_who2, sys.dm_exec_requests - nothing works. For dm_exec_requests it shows nothing because it does not run at the current moment.Any other solutions to catch this SPID with a script?
View 5 Replies
View Related
Jul 19, 2007
Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the
calculations.
Oracle
TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')
this is a sfar as I have got with the sql version
SQLSERVER2005
= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )
visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.
using:
VS 2005 BI Tools
SQLServer 2005
View 5 Replies
View Related
May 11, 2015
I have a matrix in one of my SSRS report data is like below
Row as Branch
Column as Tasks (Progress, Completed , cancelled, In Complete)
Branch  Progress Completed cancelled In Complete   Total    %(Complete/Total)
Aus          1            2              3                4                  9                     22.2%
NZÂ Â Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â Â Â Â Â 2Â Â Â Â Â Â Â Â Â Â Â Â Â Â 3Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 5Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 11Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 18.88%
USÂ Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 3Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 3Â Â Â Â Â Â Â Â Â Â Â Â Â Â 6Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 0.00%
How can do in the SSRS matrix %(Completed/Total) percentage calculation ?
View 4 Replies
View Related
Jul 25, 2014
We have reports in SharePoint integrated mode which are really slow when compared to native mode. I have been asked to research and give info on what exactly causes the delays.
Any articles which give me information as to what happens when a report is run from SharePoint server and where does it log.
View 1 Replies
View Related
Mar 26, 2005
Recently I read such statments 'When SQL Server is run in "lightweight pooling" mode (fiber mode) and the DTC service is started, unexpected behavior may occur.'
Can someone say anything about fibe mode?I am appreciated for it.:)
View 3 Replies
View Related
Jul 22, 2015
We want to create interactive dashboards using SSRS 2008 R2. We currently has Report Server installed in Native mode. I see that we can build some good dashboards using SSRS, SharePoint 2010 and PerformancePoint. But I wanted to find out if we can build that level of dashboard using just SSRS. We are still convincing the management for getting SharePoint license. In case if they don't agree for the Sharepoint license, can we build some good dashboards just using SSRS?
View 5 Replies
View Related
Sep 17, 2015
I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here  )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
View 2 Replies
View Related
Feb 15, 2008
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
View 4 Replies
View Related
Nov 12, 2003
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
View 7 Replies
View Related
Jan 18, 2007
Durning install I selected Window's Authentication only, but now it seems we may need to use a Mixed Mode with an SA account etc... is there anyway to switch SQL 2005 to use Mixed Mode after the fact?
View 1 Replies
View Related
Mar 9, 2000
Hello,everyone!!
There is a query which when executed in the grid mode(ctrl+d) takes approx 0.02 seconds(about 21,000
rows) But when I execute in the text mode, it takes about 0.40 seconds!!
Why is this difference?
Also, when the records from this table are read from a VB application, they are equally slow (as in the text mode!)
Why is it so slow on the text mode & relatively faster in the grid mode?
Has anyone got any idea on ‘Firehose’ style cursor ?(which may speed up access of data in the VB application)
Rgds,
Adie
View 1 Replies
View Related
Jul 27, 2015
how to put sql server database in suspect mode intensely and  get it out from suspect mode to normal mode.
  i am using SQL server 2008 R2
View 5 Replies
View Related
Jun 11, 2007
ok so i have this table:
Code:
Products_Sold
priceSold - money
itemsSold - int
An example of 4 rows on my table would be like this
$1400 80
$1500 85
$1560 82
$1700 81
to calculate the average of the price sold related to the number of sold items just have to do
Select avg(priceSold*itemsSold)
But sometimes i just want the average price of the first 100 sold items, so how can i make my query to just use the first 100 sold items?
in math it would be like this
average= ( (1400*80) + (1500*20) ) / 100
but if i wanted the first 200 it would be like this
average= ( (1400*80) + (1500*85) + (1560*35)) / 200
and if i wanted the first 300 would be like this
average= ( (1400*80) + (1500*85) + (1560*82) + (1700*53)) / 300
but of course the number i want will always be a variable which is less than the total of the products sold. So, how the heck do i program this query where the number of the items sold is variable and it will take the rows of the database depending on how many items were sold.
I hope i didnt wrote my explanation too confusing and that i can get any help from you guys. thank you a lot for the help and byye
View 1 Replies
View Related
May 4, 2007
I have a SQL Server hosted on a Win2003 server. I have a monitor on the server letting me know the average download speeds. When I do a large select from a remote connection, it looks like the download rate maxes out at around 10 kb/sec.
Is this an average download rate for a server hosting sql server?
View 5 Replies
View Related
Jul 16, 2007
Hi,
Currently, our Report Builder is running on Local Intranet mode. I'm investigating what the security implications are in changing it to Internet mode. However, I've not been able to find any documentation on this.
Does anyone know of any documentation that addresses this issue or have experience that they can share with changing Report Builder security zone from Intranet mode to Internet mode?
Thanks very much.
View 1 Replies
View Related
Jul 29, 2015
I am trying to calculate cumulative GPA which is currently it is being done via a cursor and i was looking to change it to set based if possible.
The cumulative GPA is basically the average marks of all courses in a term and all previous terms. Where I am having an issue is if the course was taken in a previous term and the mark was lower, then the lower mark in not factored in to the GPA. If a course was taken in a previous term and the mark was higher then the marks for the current term are not factored into the GPA.
In the example below, math is taken in all three terms and the current term's mark is included in the GPA but not the previous terms because current terms mark is higher. Two classes (bio,chem) are taken in third term but not included due to marks being higher in second term.
CREATE TABLE [gpa]([term] int,
[course] varchar(10),
[mark] float);
INSERT INTO [gpa]
VALUES (1,'math',3),
(1,'eng',4),
[Code] .....
View 9 Replies
View Related
Mar 6, 2008
Hi,
In short, i'm running a query in SQL Server 2005
where activitydate <= '2008-02-26 23:59:59.999' and resultset contains...
'2008-02-27 00:00:00.000'
I know that rounding happens, but when i save data with getdate() with 3 digit milisecond accuracy, shouldn't i be able
to recover with this accuracy?
View 4 Replies
View Related
Mar 31, 2008
There are a few features in the new SQL Server - Reporting Services that I really need in production. I have tested everything and it works great. I am running the CTP version since Microsoft is saying they aren't releasing the release version until 3rd quarter 2008.
Since Microsoft won't sell SQL 2008 until 3rd quarter, can I run the CTP in production until the release and then purchase SQL 2008?
Jim
View 1 Replies
View Related
Jan 3, 2006
Hi folks,
I have a SQL 2005 OTC. CTP version running on Windows 2003 server.
I would like to find out how the SQL server option changed to Windows Authentication mode from Mixed mode over the weekend. From the SQL log, I don't see when it changed. I would like to see Date/Time and client IP. If I can see User ID (windows) that would be great. Where I can find these info in SQL server?
Thank you in advance...
SHJ
View 1 Replies
View Related
Feb 16, 2014
i'm building a query to return metrics that will drive 3 seperate pivot tables showing
1. Total count of LineItems per D_Type each month
2. Total count of LineItems per Status each month
3. Avg count of LineItems per Invoice each month
I am able to get the first two, but having hard time with the 3rd.
Here's some representative ddl
create table Remediation
(Invoice nvarchar(10), D_Type nvarchar(20), Status nvarchar(20), RemediationDate datetime);
insert into Remediation values
--this will create data for Jan, 2014
('501', 'Recycle', 'Pass', getdate()-30),
('501', 'Reuse', 'Pass', getdate()-30),
('501', 'Remarket', 'Fail', getdate()-30),
[code]....
how to add the average metric to this query?
View 9 Replies
View Related