What this does is, it calculates the median of the medians over the period of 3 years. But, what I'm looking for is the overall median of the underlying measure over a period of 3 years.
Which works fine for all my other calculations except this, where I need a distinct count. CNT is a calculated measure. The browser would look like this:
Category Year1 Year2 ..... MDX what I have now MDX what I need
Hi All, I have a table that of server names and their execution times that run in to hundreds of thousands of records. What i need is some SQL that gives me the median execution times for each of these different servers. At the moment i have some SQL that only gives me the median for all the records in the table not the median execution time for every different server name. For example my tables looks something like this;
Here is my SQL, hope someone can modify it and thanks in advance.
Code:
SELECT DISTINCT instance, exec_time AS median FROM (SELECT instance, exec_time FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance FROM (SELECT TOP 50 PERCENT exec_time, instance FROM llserverlogs ORDER BY exec_time) sub_a ORDER BY 1 DESC) sub_1 UNION ALL SELECT instance, exec_time FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance FROM (SELECT TOP 50 PERCENT exec_time, instance FROM llserverlogs ORDER BY exec_time DESC) sub_b ORDER BY 1) sub_2)
I am trying to calculate the median value using one of the measures and a dimension value.
Time is a measure in my cube and OpId is one of the dimensions.The result is as follows:
opid time median
1 55
2 23
3 23
Total 23
The Time here for Op Id 1 is the aggregation for all the rows whose OpId is 1.I want the median of the values whose OpId is 1 which is not showing at the moment.
What I am getting here is the median for all of the OpId but what I really want is the median for each of the individual Opid's as well.
I am using a calculated field Median with the following expression.
Hello to everybody, I'm quite new to the Analysis Services world and would like to create a BI Solution for one of my customers with the following problem: I have sales data from the ERP from different years (2005 and on). My customer wants a report that shows the sales of the current year up to the month he wants to specify (e.g. january - march 2008) compared with the sales of the same period of the previous year (e.g. january - march 2007), divided into product category.
Is there a way to build a cube and then a matrix report in Reporting Services that lets the user specify the period for the current year and shows the same period of the previous year. Do I achieve this behaviour with a calculated member in the cube (e.g. ParallelPeriod)
Any hint (or web like example) would be very appreciated.
I need to calculate a median on a column in a table. The code I have is:
Code: Select gender, CASE when gender = 'F' then 'Female' when gender = 'M' then 'Male' else 'Unknown' end as test, datediff(day, [admit_date], getdate()) as 'datediffcal', from [tbl_record] How do I calculate the median on the datediffcal column?
It doesn't matter if the resultset only shows the median result. So if the output shows:
--Use the following formula to calculate a person's age in years in a stored procedure, --where @dob is the person's date of birth and @yyyymmdd is the date on which to determine the age:
DECLARE @age int DECLARE @dob datetime DECLARE @yyyymmdd varchar(11)
--Notes: --Substitute "getdate()" for the @yyyymmdd variable if you want to determine the person's age right now. --The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer. --The function DATEDIFF(year, @dob, @yyyymmdd) doesn't work because Microsoft chose to implement --the "year" part of the function to calculate the number of year boundaries crossed.
Thanks for taking the time to read my post. I greatly appreciate it!
What i'm trying to do is get a distinct count of account numbers within a rolling period. My actual take is rather large but i've created a smaller-like version below. Please reference this table.
Account Date
1 1/1/08
2 1/2/08
3 1/2/08
2 2/8/08
4 2/9/08
1 2/15/08
1 3/5/08
5 3/6/08
4 3/9/08
3 3/10/08
1 4/1/08
5 4/9/08
2 4/15/08
3 4/26/08
1 5/3/08
2 5/15/08
3 5/29/08
6 5/30/08
Let's say i want to return distinct count of accounts within a 2-month rolling period meaning in February, i'd get a distinct count for accounts in January & February, then in March i'd get a distinct count for February & March, then in April i'd get it for March & April, and so on... my results table would like the table below:
Account Month
3 1
4 2
5 3
5 4
5 5
I had asked this before but it was a summing equation and not a unique count. I've tried to play with the summing equation to kind of make it work, but i'm starting to get a headache. It's probably so simple!
Here's my previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2289509
12/1/07 What i'm trying to do is return a result with total charges from 3 months previous based on a date parameter or just as a whole for that matter. For example:
If my date parameter is 6/14/07, i'd like my total charges to be 15 (6+5+4). If my date parameter is 7/10/07, the charges would be 18 (7+6+5)
I hope that makes sense of where i'm going with this. I've played with this using the "Guru's Guide" solution by Ken Henderson, but still to no avail yet. Here's his code syntax:
Code Block
SELECT a.DayCount, a.Sales, SUM(b.Sales) FROM Sales a CROSS JOIN Sales b WHERE (b.DayCount <= a.DayCount) AS RunningTotal GROUP BY a.DayCount,a.Sales ORDER BY a.DayCount,a.Sales Here is the result set i'm looking for:
Charges
Date
1
1/1/07
2
2/1/07
6
3/1/07
9
4/1/07
12
5/1/07
15
6/1/07
18
7/1/07
21
8/1/07
24
9/1/07
27
10/1/07
30
11/1/07
33
12/1/07 Each date's charges are a culmination of 3 months worth of charges.
Any help would be greatly appreciated. I'll be trying to figure this one out concurrently and if i do, i'll post back!
I am using MS SQL 2012 and have a pretty simple table dbo. Migration Breakdown with sample data as follows.
DepartDateTime ZoneMovement 2015-06-26 14:00:00.000 6 to 4 2015-06-26 14:00:00.000 11 to 7 2015-06-26 15:30:00.000 9 to 6 2015-06-26 21:00:00.000 7 to 3 2015-06-27 08:01:00.000 7 to 4
[code]....
What I am trying to do is parse the data set to find out when we have more than three like movements ex. 3 to 10 within ANY rolling 72 hour period. I have looked at the SQL Window Functions OVER with a ROW | RANGE subclause, but I can't find out how to tackle this rolling 72 hour business.
Aim – Calculate the number of days between CreatedDate and [Date_Docs_In_Complete__c],count how many Ids, Fall within a specific month by year, and then work out the avg number of days it took for each month
My query so far
select ID, left(CreatedDate,10) as CreatedDate, left([Date_Docs_In_Complete__c],10) as [Date_Docs_In_Complete__c], DATEDIFF(day,CreatedDate,[Date_Docs_In_Complete__c]) as Days from #build
Using 'TAIL([Time].[Time].[Year].MEMBERS,1)' I can get the current year dynamically. My question is how do I get the previous two years dynamically as well. I've tried a few different ways with no luck. As you can see below I'd like to replace 2014 and 2013 with current year -1 and current year -2.
SELECT { [Time].[Time].[Year].&[2013], [Time].[Time].[Year].&[2014], TAIL([Time].[Time].[Year].MEMBERS,1) }ON COLUMNS, NON EMPTY {[Branches].[Branches].[Region]*[Measures].[Ship Resale S&D Run Rate]} ON ROWS FROM SALES
I have make a calculated member for previous period of an given date range. The previous period is the same date range from the previous year, and I have managed to achieve that with the calculated member:
Create member currentcube.[Measures].[PrevPeriod] as (ParallelPeriod( [Start Date].[Cal Hierarchy].[Year], 1, [Start Date].[CAL Hierarchy].CurrentMember), [Measures].[Count]);
This member returns the correct result as long as my query uses the time dimension, which makes sense... but I also need to show results sliced by other dimensions in bar charts that do not display the time dimension. For example, I have a dimension with only 3 members called [Region].[Area].[AreaName].
The result set for the bar chart needs to look like this:
[AreaName] | [Count] | [PrevPeriod] East | 43 | 56 West | 53 | 95
But the [PrevPeriod] only returns values if I include the time dimension. I essentially need to sum the results of the time dimension/AreaName/[PrevPeriod] tuple down to just Areaname/[PrevPeriod] for whatever date range may be involved.
I don't know if this is significant to the issue, but the client tool that generates the bar charts builds the query with the date range as a subcube in the FROM statement. If the [PrevPeriod] is outside of the subcube that is still OK, as long as the time dimension is included in an Axis on the final select statement, so at least I know I am not suffering from the members inside the subcube. I've also found in SSMS that it makes no difference if I make the query a subcube, or put the date range in a where clause instead; I still get NULL for [PrevPeriod] without the dates.
I can't imagine that this is an unusual situation, so I hope I've explained it adequately! What is the recommended technique for summarizing a Parallelperiod by dimensions without displaying the time/dates ?
I would like to create calculated measure which should give me closing holdings at all levels(week ,Month,Quarter and year levels.) whichever i pull in the browse pane.
Below calculated measure would show the holdings for whatever the level you have specified.
([Measures].[Holdings], ClosingPeriod( [DIM BI DATE].[Calendar].[WEEK], [DIM BI DATE].[Calendar].CurrentMember ))
But this measure shows the value for only week attribute.
for month,([Measures].[Holdings],ClosingPeriod( [DIM BI DATE].[Calendar].[Month], [DIM BI DATE].[Calendar].CurrentMember ))
But my aim is to create a calculated measure to give closing value for any level.
I have developed a cube in my work place for analyzing current year sales with previous year sales in Time Hierarchy (Year- Quarter- Month) using Parallel period. If we want to see data for particular Quarters i.e. Q1 and Q2 then total at the year level should also get change. Currently if we only choose 2 quarters in the filter then current year data gets change, however data using parallel period is not getting change accordingly and its shows Total of full year.
Our client wants to report on their trade volume for last year as compared to the current quarter. For simplicity let's pretend they have a report where they have a two key measures:
is based on outlook - that is, for any period we are reporting on the trade volume will be reported as actuals that have been loaded up until the current period, and forecast for the current and future periods.
[Trade Volume - Tons MTD] is based only on actuals - that is for any period we are reporting on the trade volume will be reported as actuals that have been loaded up until and including the current period, and 0 for any future periods.
If Feb09 is our current period, and we are using quarter on the time dimension (where quarter 1=Jan09,Feb09,Mar09) and we have the following data:
This is a problem, because the comparison with their current results ([Trade Volume - Tons MTD]) with what they 'forecast' ([Trade Volume - Tons]) is not based on the same period of time - we are comparing the sum of two periods versus three periods.To solve this we changed the reporting period to be monthly granularity, and now select Jan09-Feb09 as our range (as opposed to having a quarter granularity and selecting Q1,2009 in the example above).
This works well and produces the expected results:
However, this introduces a secondary problem: we are doing a prior year calculation on the Trade Volume also, so the users can compare how the actuals are comparing to the same period last year.To do this we use the following formula for the prior year calculation:
Prior Year Actuals=([Measures].[Trade Volume - Tons], ParallelPeriod([Time].[544 Hierarchy].[Period Year],1,[Time].[544 Hierarchy].currentmember))
The problem is as soon as we move from quarter granularity to (monthly granularity AND select more than one monthly period) the Prior Year Actuals calculation produces a an error "The MDX function CURRENTMEMBER failed because the coordinate for the 'Period Year' attribute contains a set".So, ParallelPeriod does not like it when currentmember is a range (Jan09,Feb09) rather than a single period (Jan09).
I have a monthly time period dimension representing average number of students for each month. At the yearly aggregate level I don't want it to sum up the avg number of students from every month because that number is incorrect. I would like it to use the number of students from the most recent month as a roll up. Is that possible to configure in SSAS?
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |... Orders -> id | idCustomer | date | ... Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total FROM Products INNER JOIN Orders ON Orders.id = Products.idOrder INNER JOIN Customers ON Customers.id = Orders.idCustomer WHERE year(date)=2014 GROUP BY customers.name ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015) -------- customer A | 1000$ | 2000$ | 3000$ customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
I'm using an olap cube in a report (dash board). The last "actual" period should be viewed when the user opens the report. I'm using the MDX tail function for that. Once the report is opened the users want to select another period. Then I want to put period as a parameter. But that is not possible since the parameter check box is no longer available when using a MDX filter expression.
I recently had to use my own little median technique again on a report here at work, and had posted it before, but wasn't sure if anyone had seen it. I have read Celko's and others techniques for generating a median and haven't seen one more efficent.
Does anyone have a better way they can think of? I think this bad boy is pretty short & efficient.
First, if you want to return the middle number or the higher one next to the middle if there is an even number:
SELECT x.Value AS median FROM Vals x CROSS JOIN Vals y GROUP BY x.Value HAVING SUM(SIGN(x.Value-y.Value)) IN (1,0)
Change the " IN (1,0)" to "IN (-1,0)" to get the lower value if there is an even # of values.
Basically, we are saying compare each number to all possible numbers, and add up values of 1,0 or -1 depending if the first number is less, equal or higher than the second. The number that returns 0 is right in the middle ... If there is no middle, a -1 or 1 is returned. There will never be a 0 and (-1 or 1) at the same time returned.
To get the financial median (avg of the 2 values middle values if there is an even number), you need to encapsulate the results of the above into a subquery, allow for not just (-1,0) but all three (-1,0,1) and then take the AVG of the values returned.
That is,
SELECT Avg(Median) as Median FROM ( SELECT x.Value AS median FROM Vals x CROSS JOIN Vals y GROUP BY x.Value HAVING SUM(SIGN(x.Value-y.Value)) IN (1,0,-1) ) A
If there is an even number of values, the lower and higher middle ones are averaged. If there is an odd number, only the middle value is returned and averaged (which of course has no effect).
Most other techniques used several COUNT(*) subqueries which this one avoids.
I have a table that contains the following: customer (ID of customers) product (Product description numeric value) UOM (unit of measure like each or pak) avgprice (Avg proce that this product and uom was sold)
I need to find the median value for a product, uom. Then I need create a table that shows product,uom,avgprice,median grouped by product and uom I have been at this for two days with no luck
I have a long term need for a median function, I was wondering if anyone has or knows of some code or user defined functions somewhere that would do this. Ideally you could use it just liket the rest of the aggregate functions like AVG, etc.
I have looked all over the web to try to find some very basic / simple explanations of how to get a median value from a group of records in a table but with no luck
the problem i am having is that all the information i find is always centered around getting a median using every single row in the table. except i have groups of data in the table and want to work out a median for each group. the group is identified by 4 different columns (the 5th column is what i want to get the median on but for each group not the entire table) and i want to produce a resulting table that has 1 row for each group and therefore contains the median value for the group instead of the individual numbers that it currently has. e.g. the current table is like this
column1 column2 column3 column 4 column5(median of this)
value 1 value 2 value 3 value 4 1.2 value 1 value 2 value 3 value 4 1.0 value 1 value 2 value 3 value 4 1.5 value 2 value 3 value 4 value 5 0.2 value 2 value 3 value 4 value 5 0.4 etc...
and i need a query to get the results to show like this
column1 column2 column3 column 4 column5
value 1 value 2 value 3 value 4 1.0 value 2 value 3 value 4 value 5 0.3 etc...
This is driving me crazy and i will be very helpful if anyone can help
the statement i need to add it to is:
select pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate, avg(srtot)as meansrtot, avg(sreff)as meansreff, avg(BFRaw)as BF_Rawmean, avg(BFTGV)as BF_TGVmean, count(srtot)as countsrtot, count(sreff)as countsreff from bodyparametersf where (srtot is not null) OR (sreff is not null) group by pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate
I need to calculate Median on each calculated result from the query below. There is one Median function available in SQL2K but it is not working. Can anyone help me in this regard.
------------------------------------------------------------------------ SELECT INS.Code As [code],INS.FinYr as [YEAR], ' ' As [FIRE BUSINESS], (INSRev.FI_NetPremLessIns / INSRev.FI_GrPremium) * 100 As [Rention Ratio], (INSRev.FI_NetClaimPaid/INSRev.FI_AdjNetPremium)*100 As [Claim Ratio], ((INSRev.FI_AgencyCommPaid+INSRev.FI_ReInsCommPaid+INSRev.FI_MgmtExpenses+INSRev.FI_OthExpenses)/INSRev.FI_AdjNetPremium)*100 As [Expense Ratio], ((INSRev.FI_NetClaimPaid/INSRev.FI_AdjNetPremium)*100)+(((INSRev.FI_AgencyCommPaid+INSRev.FI_ReInsCommPaid+INSRev.FI_MgmtExpe nses+INSRev.FI_OthExpenses)/INSRev.FI_AdjNetPremium)*100) As [Combine Ratio], (INSRev.FI_ClosingBal/INSRev.FI_NetClaimPaid) As [Unexpired Risk Reserve to Net Claim(x)],(INSRev.FI_MgmtExpenses/INSRev.FI_AdjNetPremium)*100 As [Management Expenses to Adj. Net Premium], (INSRev.FI_AgencyCommPaid/INSRev.FI_AdjNetPremium)*100 As [Agency Commissioned to Adj. Net Premium]
FROM (InsuranceGen As INS LEFT JOIN INSURANCEGen As INS1 ON (INS1.FinYr=INS.FinYr-1 AND INS.Code=INS1.Code)) LEFT JOIN INSRevGen as INSRev ON (INS.Code=INSRev.Code AND INS.FinYr=INSRev.FinYr) WHERE INS.Code IN ('ABC1','ABC2','ABC3') AND INS.FinYr=2005 ORDER BY INS.Code, INS.FinYr ----------------------------------------------------------------------
Using SQL Server 2005, I have the following query to calculate the median sales of each quarter over the past 5 years:
WITH CompMedian AS
(
SELECT SoldDate, SoldPrice, ROW_NUMBER() OVER(PARTITION BY Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2)) ORDER BY SoldPrice) AS RowNum, COUNT(*) OVER(PARTITION BY Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2))) AS Cnt FROM tbl_Orders WHERE Status = 'Sold'
SELECT Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2)) AS CompDate, AVG(SoldPrice) AS CompMedian
FROM CompMedian
WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2)
GROUP BY Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2))
ORDER BY CompDate;
Now my client would like me to change the query so that each quarter would represent the median for the past 12 months ending with that quarter. I've been looking at this for hours and I'm at a loss. Anyone have any thoughts?
