SQL Server 2014 :: Reverse Recursion For Some Specific Statistical Calculation
Oct 2, 2014
I have a table :
id + A + B
1 |0,11| 0
2 |0,45| 0,5
3 |0,85| 0,75
I need to calculate the following :
F = 0,85 * ( 1 - 0,75 * ( 1 - 0,45 * ( 1 - 0,5 * ( 1 - 0,11 * ( 1 - 0 )))))
In which F = A3 * ( 1 - B3 * ( 1 - A2 * ( 1 - B2 * ( 1 - A1 * ( 1 - B1 )))))
It seemed quite easy at first glance. I Built it up via string concatenation and thought to execute the dynamic sql with sp_exec and get the result. As I don't like dynamic sql I was wondering If there is any other way..
ALTER PROC [dbo].[Tools_Serial_FE]
AS
BEGIN
DECLARE @IP FLOAT,
@IF FLOAT,
@FE FLOAT,
[Code] ....
View 5 Replies
ADVERTISEMENT
Sep 9, 2015
I have huge export files in a DB and i need to check if there are any datasets that have the same value in the first column, but a different in another one, via a query of course.
Like this:
ID IS NULL
1 1
2 1
3 0
1 0
The expected ID i get as a result of my query should be 1 in this case.
View 6 Replies
View Related
Apr 1, 2014
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
EMP_ID INT,
Title varchar(50),
DateValue DATETIME,
TITLE_YEAR INT,
[code]....
I am new to this level of coding in SQL SERVER 2012, but I am looking to update the TITLE_YEAR field in the temp table with the Year the employee is in that title. For example for employee 11127 the data should look like this:
EMP_IDTitle DateValue TITLE_YEAR
3 Senior Consultant 2009-01-01 00:00:00.0001
3 Director 2010-01-01 00:00:00.0001
3 Director 2011-01-01 00:00:00.0002
3 Director 2012-01-01 00:00:00.0003
3 Director 2013-01-01 00:00:00.0004
3 Senior Director 2014-01-01 00:00:00.0001
View 6 Replies
View Related
Apr 7, 2015
I have this query
SELECT top 100 Ltrim([text]),objectid,total_rows,total_logical_reads , execution_count
FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
where last_execution_time >= '2015-04-07 10:01:01.01'
ORDER BY execution_count DESC
But the result of execution count is from the first. I want to know it only one day.
View 9 Replies
View Related
Apr 8, 2015
I need to do some operations on a table when any user dont work with it.How do i do it?
Do i lock the table? and is it possible?(I dont want to deny permission from a user because cause error)
Can i do it with a open transaction ?
View 4 Replies
View Related
May 20, 2015
I created a new login and then created a new user [COM] in DB with default schema pointing to [COM]
I created then schema [COM] WITH AUTHORIZATION [COM]
I want this [COM] user to have all permissions it needs on [COM] schema only. How do I do that? When I try to create table [Com].Table it gives me permission denied.
What am I missing?
View 9 Replies
View Related
Apr 8, 2015
I need to do some operations on a table when any user dont work with it.How do i do it?Do i lock the table? and is it possible?
View 8 Replies
View Related
Aug 8, 2015
I'm looking for a quick script that someone has already written to update statistics (not to rebuild or re-organise) on specific indices in specific databases - I guess loop though a table comprising of a list of databases and the indices.
I know Ola has one but I'm not look for something that is that complicated. If I cannot find one I'm going to have to write one myself - I want to try and avoid re-inventing the wheel as tomorrow I have to do this work and it's about 7K plus indices in about 10+ databases.
View 2 Replies
View Related
Oct 29, 2015
This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
DECLARE @stoptime DATETIME = NULL;
[Code] ....
View 8 Replies
View Related
Aug 5, 2015
I have a user who needs access to views like(dbo.viewnameabc1,dbo.viewnameabc2 and so on...) dbo.viewnameabc* and anytime the user creates the view he already have the permission to view those views....
View 3 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 17, 2007
Hi everybody,
I would like to use MatLab built-in statistical functions (beta, gamma, normal, etc.) from inside a SQLServer stored proceudre. Does anyone know if possible? (Of course, If so, where can I get documentation for doing this?)
Thanks in advance!
JorgeHG.
View 5 Replies
View Related
Sep 22, 2006
Hello my friendsThis is my sql table structureFK = ID int, Empnaam varchar(200), PK = EmpID int With this table, where i insert values, employees can hire other employees. Now i want to see with a function in visual studio who hired who. I get stuck when a person hired more than 1 person....This is my stored procedure :set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[ShowHierarchy] ( @Root int ) AS BEGIN SET NOCOUNT ON DECLARE @EmpID int, @EmpNaam varchar(30) SET @EmpNaam = (SELECT EmpNaam FROM dbo.Emp WHERE EmpID = @Root) PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpNaam SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE ID = @Root) WHILE @EmpID IS NOT NULL BEGIN EXEC dbo.ShowHierarchy @EmpID SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE ID = @Root AND EmpID > @EmpID) END END Thanks in advance!Grtz
View 8 Replies
View Related
Jun 30, 2015
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
View 2 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
Apr 12, 2007
I want to keep track of some application statistics.
-How many applications per day, month, year
-How many apps for a specific type of widget
-etc.
Is this best accomplished by a script that review the data or by using counters that are updated with every app?
Can someone give me some info on how to get started with this? I did a search, but only found a thread that recommends an article that costs $100.
View 9 Replies
View Related
Feb 6, 2007
PLEASE HELP> URGENT.
i have a table with columns, count, monther , year , officer, location.
count is number of x owned by officer by month , by year for a location. i want to display the statistical data. can any one give me some ideas bcoz some officers dont own anything, thats what i am looking for. like 0 is owned by these % of officers in this location... something like that.
all this is for differenatiating officers with more work than with less or no work..
please help ...
View 1 Replies
View Related
Jan 16, 2007
Hi everyone, I am storing statistics for different affiliate and merchant sites, and I have a few questions about how to store it. My first idea was to create a serializable array and store it in a statistics column with the rest of the site information. I could even have two columns, one being an archive, and one being the current month. I know these arrays would get quite large, but they would only be retrieved when someone was looking at the statistics. Is this a viable way to do it? The other idea I had was to create tables for each site and ad and store the statistics as rows in each objects respective table. While easy, I didn't know if it was a bad idea to have a very large amount of tables, ie., one for every advertisement and site signed up for the affiliate program. Thanks for all your expertise in advance and I look forward to contributing to this great community. Dave
View 2 Replies
View Related
May 3, 2001
I want to perform SPC on a data set.
The type of analysis I want to do is
Mode,Median,Max,Min,Average,standard deviation.
Does Sql server have any in built functions to accomadate this type of analysis.
Is there any information on the net that I could be referred to. I have SQL Server 7.0 Enterprise Edition.
Thanks Pargat
View 1 Replies
View Related
Oct 5, 2001
I was trying to add an index to a heavily used table which has one other index. When I did I recieve the following error message
Unable to create index 'IX_PATIENT__1'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create more than 249 nonclustered indices or column statistics on one table.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
Do I need to wack the statistical
View 1 Replies
View Related
May 11, 2004
Hi,
I want to use functions like Standard Deviation, Variance, and other forms of these (StdDevp, Varp) but am not clear about what these really do. All I know is that if I am clear about these functions, I can really put these to good use. BTW, maybe there are many more functions in addition to the one's that I have listed??
Can you kindly assist me with a mini example in understanding what these functions do?
Suppose I have the Total and Average of orders. How can these functions be put to use? Kindly help me out if you can come up with a better example.
I'll be grateful for your help.
Many TIA.
View 3 Replies
View Related
Jan 27, 2008
What are MS SQL Server terms for
- statistical view
- materialized query tables
used in DB2
?
View 7 Replies
View Related
Jan 9, 2007
Hi,
I might be asking one of the most weird and most irrelevant question... but actually I am very new to this technology, and need help!
If I am asked about 'Statistical Tools For Data Mining', then in which way do I go?
Is it related to the various methods like Neural Network, K-Nearest Neighbour (KNN), etc. or is it related to something else?
(A few statistical tool examples would be highly appreciated!)
Thanx a lot for you time and advice!
Best Wishes.
View 3 Replies
View Related
Jan 8, 2005
Hi there, Any tips on my problem would be most welcome...
right, the scenario.
A web Blog
blogger1 posts a blog_entry, e.g I love the simpsons
blogger2 comments on that blog_entry, e.g No, I hate the simpsons
Blogger3 comments on that comment, ie, How can you hate the simpsons.
so you can comment on a comment on a comment etc.. lool.
right, i have got two tables... Blog_entry & comment. i need to be able to search for a blog_entry + all the comments on that blog_entry..
at the moment i can search for the comments on the blog_entry using the FK in the comment table.
blog_entry
INSERT INTO Blog_Entry VALUES(0001,'I love the Simpsons');
comments
INSERT INTO Comment VALUES(0001,' No I hate the simpsons, cID 1000);
but i need to be able to search for the comments on comments
INSERT INTO Comment VALUES(0001,' How can you hate the simpsons, cID1000);
hopefully you can see the problem here, with only one comments table how can i get the search for the comment on the comment.. there’s nothing linking them...
I could make a sub comments table, and use a FK (as with the blog_entry & first comment)
but then I would have to make another sub sub table to be able to get those comments on the first sub table... this would go on and on for each comment on comment.
you can see the cID1000 (comment PK) I can't use this to get the comment because its duplicating the PK...
So, I need to be able to search for the comments on comments… eg. I need to be able to search for blogger2, and any comments that were made on his comments.
Someone I know mention using recursion to get the comment on comment info, is this right ?
Hehe, I hope you understand what im asking here… the is my first exploration of SQL, so any tips, hints, would be most welcome….
Thanks loads…
PS: if there anything you don’t understand about what I have written, or what im asking… please say so…
Spence.
View 14 Replies
View Related
Mar 28, 2008
I have a table
CREATE TABLE [dbo].[chart_hiera2](
[AccessID] [int] NULL,
[ChildID] [int] NULL,
[Child] [varchar](100) NULL,
[ParentID] [int] NULL,
[CGID] [int] NULL,
[Depth] [smallint] NULL,
[Lineage] [varchar](255) NULL,
[node] [bit] NULL,
[PercentOwnership] [varchar](10) NULL,
[Notes] [varchar](80) NULL
) ON [PRIMARY]
I am trying to build the value off hierarchy that will be later inserted in the linage and Depth column. I am trying to do so using recursion . the rulles for recording the linages as as follows Lineage = parent.Lineage + Ltrim(Str(ParentID,6,0)) + '/'
here is my code below
with BuildHierarchy as (
SELECT AN.AccessID,AN.ChildID,AN.Child,AN.ParentID,AN.CGI D, 1 as Depth,AN.Lineage,AN.node,AN.PercentOwnership, AN.Notes
FROM chart_hiera2 as AN WHERE AN.Depth Is Null and AN.AccessID = @accID
union all
SELECT AN.AccessID,AN.ChildID,AN.Child,AN.ParentID,AN.CGI D,Cast(AN.Depth as smallint) +1 ,Cast(BH.Lineage+ Ltrim(Str(AN.ParentID,6,0)) + '/' as varchar(255)),AN.node,AN.PercentOwnership, AN.Notes
FROM chart_hiera2 as AN inner join BuildHierarchy BH on AN.ParentID=BH.ChildID
WHERE AN.Depth>=0 AND AN.Lineage Is Not Null AND AN.Depth Is Null and AN.AccessID = @accID)--@accID --and T.AccessID = @accID)
select * from BuildHierarchy
but it does not increment Depth or builds Lineage .What am I doing wrong?
View 1 Replies
View Related
Nov 12, 2007
How to write a recursive Procedure in SQL Server to find factorial of 50?
The recursive call is limited to 32..could any one help me out.
Thanks in Advance
View 11 Replies
View Related
Nov 20, 2007
Guys,
I desperately need your help.
what i need to do is call a function getdate to return dates recursively
like so
classdate=getdate(class#, repeat, sportcategory,date)
while (classdate <yearend)
begin
classdate=getdate(class#, repeat, sportcategory, classdate)
end
I want to use the date returned and keep calling that function till the yearend is reached, to get a bunch of dates
declare @startDate datetime
with cteStartDate as
(
select x= classdate,
repeat,
class#,
sportcategory,
yearend
from sports
union all
select x=getdate( classdate,
repeat,
yearend),
repeat,
class#,
sportcategory,
yearend
from cteStartDate
where x < cteStartDate .yearend
)
I tried using cte for this, it only returns one date for everything, as opposed to an array of dates, how do i solve this problem.Any
insight will be greatly apprecaited.
Thanks
View 9 Replies
View Related
Apr 28, 2008
Hi there! I have an application that uses stored procedures wuth CTE statements to populate trees from database. Every second I need to populate 100-300 trees each one of them has 15-20 nodes. I checked those procedures with profiler and payed attention that populate procedure with CTE is slower that others (without CTE) per 1000 times! Would procedures with recursion faster than same ones with CTE? I am asking because I read somewhere that CTE is becomes slower while populating little chunks of data. Is that right and I should use recursions?
And second question: here the code -
Code Snippet
;WITH dt (Id, NodeText, PId, State, RowNumber) AS
(
SELECT Id, NodeText, PId, State, ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber
FROM DataTable
WHERE Id = 1
UNION ALL
SELECT d.Id, d.NodeText, d.PId, d.State, ROW_NUMBER() OVER(ORDER BY d.Id) AS RowNumber
FROM DataTable AS d
INNER JOIN dt ON dt.Id = d.PId
)
SELECT * FROM dt
This is how I populate a tree with CTE. Could you give me some example how to do exactly same but with recursion?(without CTE). Thank you!
View 3 Replies
View Related
Jul 8, 2015
I'm working with the statistical functions Stdev and Median with calculated members. The only way I can get the "correct" answer is if I have a dimension at the same granularity as the Fact table (Actually it's a degenerate dimension of the FACT table itself). Otherwise it seems that the measure I'm using with Stdev returns results that are so wildly high, I think it must be acting on the SUM of the measure; because the measure itself is a Summed one. When I try to use the coordinates in the Stdev function, it seems like it is using the wrong set of data points :
stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members ), [Measures].[Value])Â returns answers in the thousands when it should be more like 2.5
When used with a query, there would only be a single date member and a specific parameter member. The total number of fact records is between 200 and 500 with values that range between 0 and 150. This is the version that gives me answers that resemble the total sum of the [Measures].[Value].
If I add the dimension that is essentially a row number from the fact table, it gives the right answer (slowly, but that will be a different post ....
stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members, [FACTTable].[FACTTable].[KeyField].members ), [Measures].[Value])
View 4 Replies
View Related
Oct 27, 2006
Hi Y'all,I receive an error while using recursion:The maximum recursion 100 has been exhausted before statement completion. Can someone tell me where i can alter the default value?Thanks in advance
View 3 Replies
View Related
Oct 5, 2007
Hello
Maximum nesting level of recursion in SQL Server 2000 is 32. How much is maximum nesting level in SQL Server 2005?
Thanks
View 1 Replies
View Related
Jul 31, 2006
I have a multi-level folders table, named folders with attributes of folder_id, parent_id and user_id, and have another table, users, contains all the user_id. I need to list all the users for each folder which has parent_id = 0 and its all sub-folders' users into one table. I have created a function to return a table with folder_id and user_id for one single folder. However, I don't know how to use this function to get the sub-folder's users and merge them together as one single table.
Here is my function:
CREATE FUNCTION [dbo].[FolderUsers] (@fid int) RETURNS Table AS Return (select folder_id, f.user_id from folders f, users u where f.user_id = u.user_id and folder_id = @fid)Go
where @fid is the top folder with parent_id = 0 at here, the next level sub-folder's parent_id would be = @fid.
I am thinking to have recursive call from the parent_id = @fid that returns another table and have to concatenate to the called table. I have been thinking of store procedure, "insert into" and so on, but don't know how to implement it.
Do you have any good inspiration for me? Thank you in advance!
View 9 Replies
View Related
Apr 17, 2008
// I need to write a query to extract data from two tables where I save information on my tables in one and the relationship between the tables in the second.
//CREATE TABLE [dbo].[tblPages](
// [PageID] [int] NOT NULL,
// [PagePath] [varchar](max) ,
// [MenuName] [nchar](10),
// [Directory] [varchar](15))
//CREATE TABLE [dbo].[tblPageRelation](
// [PageRelationshipID] [int] NOT NULL,
// [TopPageID] [int] NOT NULL,
// [BasePageID] [int] NOT NULL)
// What I would like to do is do the following in SQL is a combination of this
// SELECT * FROM TblPageRelation PR LEFT OUTER JOIN TblPages P on PR.TopPageID = P.PageID
//WHERE P.DIRECTORY = 'MENUBLOCK' UNION SELECT * FROM TblPageRelation PR LEFT OUTER JOIN TblPages P on PR.TopPageID = P.PageID
//WHERE P.DIRECTORY = P.DIRECTORY
//public void recursivemove (String Directory)
// {// Find Rows that Have this Block Name associated with em
// For each returned row find the rows that share the same Directory
// }
//}
View 3 Replies
View Related
