Thursday 19 February 2015

SQL Server - Get dates between two dates

DECLARE @MinDate DATE = '2014-01-01',
        @MaxDate DATE = '2014-02-06';

SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;

        

Wednesday 18 February 2015

Sql Server Find Percentage Calculation Function

CREATE FUNCTION [dbo].[Percentage]
(
    @Percentage               DECIMAL(4,2)      ,
    @ColumnNameValue    DECIMAL(7,2)     
)
RETURNS DECIMAL(7,2)
BEGIN
    DECLARE
            @Output DECIMAL(7,2)
            SET @Output = (@Percentage/100)*@ColumnNameValue
    RETURN @Output
END


select Percentage(10,1000)

output : 100

Kalman Filter Matlab Code

clc
clear all
close all
warning off

error = 2;
Xnp = [1 2 3 4];
Zn = [1.1 1.9 3 4];
si = length(Xnp);
A = zeros(si,si);
P = zeros(si,si);
Q = zeros(si,si);
H = zeros(si,si);
R = zeros(si,si)*error;
I = zeros(si,si);

for i = 1:si
    A(i,i) = 1;
    P(i,i) = 1;
    H(i,i) = 1;
    R(i,i) = 1;
    I(i,i) = 1;
end

Xn = Xnp*A;
Pn = (A*P*A')+Q;
prevXn = [];
while(true)
    Kk = (Pn*H')*(((H*Pn*H')+R)');
    Xn = Xn+((Zn-(Xn*H))*Kk);
    Pn = (I-(Kk*H))*Pn;
    if(isempty(prevXn))
        prevXn = Xn;
    else
        con = sum(abs(Xn-prevXn));
        if(con==0)
            break;
        end
    end
end

FXn = Xn


Thursday 12 February 2015

How to Shrink Your MSSQL Database Log File / Truncate Transaction Log


  1. Log into Microsoft SQL Server Management Studio
  2. Right click on your database.
  3. Select Properties.
  4. Click on Options.
  5. Change the recovery model to Simple.
  6. Click OK.
  7. Right click on the database.
  8. Select Tasks > Shrink > Database.
  9. Click OK.
  10. Right click on the database.
  11. Select Properties.
  12. Click on Options.
  13. Change the recovery model to Full.
  14. Click OK.

Retrieve List out Clustered Index or Non Clustered Index on Tables for Entire Database

SELECT COUNT(i.TYPE) NoOfIndex,
[schema_name] = s.name, table_name = o.name
FROM sys.indexes i
INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.TYPE IN ('U')
--AND i.TYPE = 1
AND i.TYPE = 2
GROUP BY s.name, o.name
ORDER BY schema_name, table_name

--select * from sys.indexes

select * from sys.objects

How to Find Number of Primarykey and Foreignkey columns in Tables

-- No Foreign key Tables
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT IN
(
      SELECT OBJECT_NAME(f.parent_object_id) AS TableName
      FROM sys.foreign_keys AS f
      INNER JOIN sys.foreign_key_columns AS fc
      ON f.OBJECT_ID = fc.constraint_object_id
)
AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

--select * from sys.foreign_keys
--select * from sys.foreign_key_columns

select * from sys.objects

-- No primary key Tables
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasForeignRef') = 0

ORDER BY SchemaName, TableName;

How to Find List of Primary Keys and Foreign Keys in Database in Sql Server

SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')
GO
Related Posts Plugin for WordPress, Blogger...