Percentile CalculationL

MSSQL 2008/R2


SELECT [AssetClassId],[Period],
MIN(CASE WHEN seqnum >= 0.05 * cnt THEN [ValueInDecimal] END) as percentile_05
FROM (select t.*,
row_number() over (partition by [AssetClassId],[Period] order by [ValueInDecimal]) as seqnum,
count(*) over (partition by [AssetClassId],[Period]) as cnt
from [dbo].[ModelData1] t
) t
GROUP BY [AssetClassId], [Period]
ORDER BY [AssetClassId], [Period]

 
MSSQL 2012

SELECT MAX([5thPercentile]),MAX([25thPercentile])
FROM
(
SELECT [5thPercentile] = PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY [ValueInDecimal] ) OVER (),
[25thPercentile] = PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY [ValueInDecimal] ) OVER ()
FROM [dbo].[ModelData1] T2
WHERE T2.[AssetClassId] = 10024 and T2.Period = 1
)T1


Lock vs Block vs Deadlock – SQL Server

Lock vs Block vs Deadlock

Have you ever asked to differentiate lock, block and deadlock in sql server during your interview session? In this article we are going to differentiate these frequently confused terms – Lock, Block and Deadlock. So lets have a look on “Lock vs Block vs Deadlock”

These terms sound the same but are different in their meanings. Have a look on below definition to understand it.

Lock

When any session needs access to a piece of data from Database, a lock of some type is placed on that data to maintain the isolation property of database. Locking is different from blocking.

Block

When a session needs to wait for a resource being locked by another session, this process is called as blocking. e.g. If session “A” is waiting for a resource “R” being used by session “B”, you can say that session “B” is blocking session “A”. In such type of scenario, SQL Server knows that once the resource will be free from session “B”, it will be available for session “A” and it puts the session “A” on wait until all the locks on resource are clear. Note that session “A” will wait but it would not be killed by SQL Server.

Deadlock

Deadlock occurs when two sessions are waiting for a lock to clear on the other while holding it’s own resources. If session “A” has a lock on resource “R1” and waiting for resource “R2” and another session “B” has a lock on resource “R2” and waiting for resource “R1”, this circular chain will generate the deadlock situation in database. This is a permanent blocking situation and would not be resolve by waiting. SQL server is capable to detect deadlocks and can declare one of the process as deadlock victim and kills that process. The process which declared as deadlock victim is the one which is less resources intensive and has less cost than another process. We can also set the DEADLOCK_PRIORITY using SET statement to control this behavior of SQL Server.

 

Ref: http://www.sqlrelease.com/locking-blocking-and-deadlocking-in-sql-server

 

Some DB links

Database Tuning

https://www.simple-talk.com/sql/performance/performance-tuning-tips-for-database-developers/

Disaster recovery

http://support.microsoft.com/kb/822400

Bespoke applicatons

http://en.wikipedia.org/wiki/Custom_software

Interfacing:-

With regard to system-wide co-ordination, components communicate with each other via interfaces

http://en.wikipedia.org/wiki/Component-based_software_engineering

Data extraction

Data extraction is the act or process of retrieving data out of (usually unstructured or poorly structured) data sources for further data processing or data storage (data migration).

http://en.wikipedia.org/wiki/Data_extraction

Data cleansing

Data cleansing (or ‘data scrubbing’) is detecting and then correcting or removing corrupt or inaccurate records from a record set. After cleansing, a data set will be consistent with other similar data sets in the system

http://en.wikipedia.org/wiki/Data_cleansing

Data load :- Loads it into the end target

Data validation

Data validation is the process of ensuring that a program operates on clean, correct and useful data.

http://en.wikipedia.org/wiki/Data_validation

Data verification

Data Verification is a process where in different types of data are checked for accuracy and inconsistencies after data migration is done

http://en.wikipedia.org/wiki/Data_verification

Find All the Days Between Two Dates

Create PROCEDURE getAllDaysBetweenTwoDate
(
@FromDate DATETIME,
@ToDate DATETIME
)
AS
BEGIN

DECLARE @TOTALCount INT
SET @FromDate = DATEADD(DAY,-1,@FromDate)
Select  @TOTALCount= DATEDIFF(DD,@FromDate,@ToDate);

WITH d AS
(
SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER()
OVER (ORDER BY object_id), REPLACE(@FromDate,‘-‘,‘‘))
FROM sys.all_objects
)
SELECT AllDays From d

RETURN
END
GO

http://www.codeproject.com/Tips/639460/Find-All-the-Days-Between-Two-Dates

Find a stored procedure containing a test

— select table from server

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like ‘%HCP%’

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%Foo%’ AND ROUTINE_TYPE=‘PROCEDURE’

SELECT OBJECT_NAME(id) FROM SYSCOMMENTS WHERE [text] LIKE ‘%Foo%’ AND OBJECTPROPERTY(id, ‘IsProcedure’) = 1 GROUP BY OBJECT_NAME(id)

SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id, ‘IsProcedure’) = 1 AND definition LIKE ‘%Foo%’

Note:-Be aware routine_definition is cropped at 4000 chars just in case you have a long procedure. Use OBJECTPROPERTY method instead.

http://stackoverflow.com/questions/5079457/how-do-i-find-a-stored-procedure-containing-text

More like this :- https://www.mssqltips.com/sqlservertip/1768/identifying-object-dependencies-in-sql-server/

INNER JOIN vs. CROSS APPLY

This article is from this post:- https://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-joinhttp://explainextended.com/2009/07/16/inner-join-vs-cross-apply/

MASTER TABLE

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

DETAILS TABLE

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x

There are many situations where we need to replace INNER JOIN with CROSS APPLY.

1. Join two tables based on TOP n results

Consider if we need to select Id and Name from Master and last two dates for each Id from Details table.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D      
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

• SQL FIDDLE

The above query generates the following result.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
x------x---------x--------------x-------x

See, it generated results for last two dates with last two date’s Id and then joined these records only in the outer query on Id, which is wrong. To accomplish this, we need to use CROSS APPLY.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

• SQL FIDDLE

and forms the following result.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
x------x---------x--------------x-------x

Here’s how it works. The query inside CROSS APPLY can reference the outer table, where INNER JOIN cannot do this (it throws compile error). When finding the last two dates, joining is done inside CROSS APPLY i.e., WHERE M.ID=D.ID.

Calculate the last day of the month (SQL)

select dateadd( s, -1, dateadd( mm, datediff( m, 0, getdate() ) + 1, 0 ) );

To understand how it works we have to look at the dateadd() and datediff() functions.

DATEADD(datepart, number, date)  
DATEDIFF(datepart, startdate, enddate)

If you run just the most inner call to datediff(), you get the current month number since timestamp 0.

select datediff(m, 0, getdate() );  
1327

The next part adds that number of months plus 1 to the 0 timestamp, giving you the starting point of the next calendar month.

select dateadd( mm, datediff( m, 0, getdate() ) + 1, 0 );
2010-09-01 00:00:00.000

Finally, the outer dateadd() just subtracts one second from the beginning timestamp of next month, giving you the last second of the current month.

select dateadd( s, -1, dateadd( mm, datediff( m, 0, getdate() ) + 1, 0 ) );
2010-08-31 23:59:59.000

From Stack Overflow

Coining the where conditions on a sql query

CREATE PROCEDURE [dbo].[sspTASGetPMIDetails]
(
@FirstName NVarchar(100),
@SurName NVarchar(100),
@HospitalNo NVarchar(100)
)
AS
BEGIN
SET NOCOUNT ON
/* Variable Declaration */
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)

/* Build the Transact-SQL String with the input parameters */
Set @SQLQuery = 'select top 1000
pat.InternalNo,
pat.CaseNoteNo,
pat.DistrictNo,
pat.SurName,
pat.Forenames,
CONVERT(VARCHAR(10),pat.DOB,105) as DOB,
pat.HomePhone,
pat.WorkPhone,
pat.Address1,
pat.Address2,
pat.Address3,
pat.Address4,
pat.PostCode,
pat.NHSNo
from pmiDetails pat'

/* check for the condition and build the WHERE clause accordingly */

If (@FirstName Is Not Null OR @SurName Is Not Null OR @HospitalNo Is Not Null)
Set @SQLQuery = @SQLQuery + ' WHERE'

If @FirstName Is Not Null
Set @SQLQuery = @SQLQuery + ' (pat.Forenames LIKE @FirstName)'

If (@SurName Is Not Null AND @FirstName Is Not Null)
Set @SQLQuery = @SQLQuery + ' And (pat.SurName LIKE @SurName)'

If (@SurName Is Not Null AND @FirstName Is Null)
Set @SQLQuery = @SQLQuery + ' (pat.SurName LIKE @SurName)'

If (@HospitalNo Is Not Null AND @FirstName Is Null AND @SurName Is Null)
Set @SQLQuery = @SQLQuery + ' (pat.CaseNoteNo LIKE @HospitalNo OR pat.DistrictNo LIKE @HospitalNo OR pat.NHSNo LIKE @HospitalNo)'

If @HospitalNo Is Not Null
Set @SQLQuery = @SQLQuery + ' AND (pat.CaseNoteNo LIKE @HospitalNo OR pat.DistrictNo LIKE @HospitalNo OR pat.NHSNo LIKE @HospitalNo)'

/* Specify Parameter Format for all input parameters included
in the stmt */
Set @ParamDefinition = ' @FirstName NVarchar(100),
@SurName NVarchar(100),
@HospitalNo NVarchar(100)'

/* Execute the Transact-SQL String with all parameter value
Using sp_executesql Command */
Execute sp_Executesql @SQLQuery,
@ParamDefinition,
@FirstName,
@SurName,
@HospitalNo

RETURN
SET NOCOUNT OFF
END