sqlsql-serverssmsssas
How to write SQL to calculate running average with some additional formulae?
Following is the image that has running average calculated by me. But the requirement is a bit extra on top of the running average.
Following is the image where the requirement is in the Microsoft Excel sheet.
So, in order to calculate the running average with formulae like =(3*C4+2*C5+1*C6)/6 that have been gathered in excel sheet, what SQL Query could be written?
Also, if it's not feasible through SQL, then how could I use the Column D from the second image as my measure in SSAS?
Solution
use LAG() with offset and follow your formula accordingly
avg_val = ( (3.0 * lag(Open_, 2) over (order by M, [WEEK]) )
+ (2.0 * lag(Open_, 1) over (order by M, [WEEK]) )
+ (1.0 * Open_) ) / 6
- Delete duplicate records in SQL Server?
- SQL - Continue running all SQL statements even after error
- Retrieve result of StoredProcedure that has NO out parameter
- opposite of "top" in sql server, without using order by, there are no keys/indices
- LINQ Pivot Problem - Convert SQL Script to LINQ
- What is the difference between a primary key and a unique constraint?
- How to SELECT the last 10 rows of an SQL table which has no ID field?
- How to remove a specific character from a string, only when it is the first or last character in the string.
- extract time from now() in amazon redshift
- QL query MS Access error "field could refer to more than one table"
- How to get all auto generated foreign key names?
- Selecting data from table based on date
- SQL transaction locks query weird behavior
- PostgreSQL splitting time range into days
- How to concatenate strings of a string field in a PostgreSQL 'group by' query?
- SPARK SQL Equivalent of Qualify + Row_number statements
- Difference between CURRENT_TIMESTAMP and CURRENT_DATE
- Returning Continuous Months and Copying Values from the Previous Month
- How to drop a column from a Databricks Delta table?
- postgres. How to check if a field contains at least one substring from a list of substings?
- Oracle retrieve only number in string
- SQL ASC and DESC ordering on same column based on another column
- ANY and ALL against a small data set producing confusing results
- Generating a sequence number in a PostgreSQL database - concurrency & isolation levels
- Transact-SQL shorthand join syntax?
- How to increase the performance of a Database?
- What is the best way to create and populate a numbers table?
- What does the new stored procedure default content mean?
- Compare tkinter calendar get_date() Date with datetime field in Oracle db :
- SQL Selecting if "active" = true