Wednesday, December 25, 2013

Efficient Way of Handling SCD Type 2 - Using Merge (Upsert) Statement

I feel happy that people around the globe reach my blog when they google for " SQL Server Upsert ". You can read the Basic Merge Post here. It was for beginners who want to use Merge for the first time and also for referral purpose. In that post, I only discussed about changed overwrites (Slowly Changing Dimension (SCD) Type 1), and now in this post, we shall see how to use Merge for SCD Type 2 (Dates and Flag) implementation.

Source Table: DEPT
CREATE TABLE [dbo].[DEPT]
(
            [DEPTNO] [numeric](2, 0) NULL,
            [DNAME] [varchar](14) NULL,
            [LOC] [varchar](13) NULL
)

Destination Table: DEPT_SCD2DATE
CREATE TABLE [dbo].[DEPT_SCD2DATE]
(
            [ID] [int] IDENTITY(1,1) NOT NULL,
            [DEPTNO] [numeric](2, 0) NULL,
            [DNAME] [varchar](14) NULL,
            [LOC] [varchar](13) NULL,
            [STARTDATE] [datetime] NULL,
            [ENDDATE] [datetime] NULL,
            [FLAG] [bit] NULL
)

Merge Code:


















INSERT INTO DEPT_SCD2DATE (DEPTNO, DNAME, LOC, STARTDATE, ENDDATE, FLAG)
SELECT DEPTNO, DNAME, LOC, STARTDATE, ENDDATE, FLAG FROM
(MERGE DEPT_SCD2DATE D
            USING DEPT S
                        ON (D.DEPTNO = S.DEPTNO)
            WHEN NOT MATCHED
            THEN
                        INSERT VALUES (S.DEPTNO, S.DNAME, S.LOC, GETDATE(), '2999-12-31', 1)
            WHEN MATCHED AND D.FLAG = 1 AND (D.DNAME <> S.DNAME OR D.LOC <> S.LOC)
            THEN
                        UPDATE SET D.FLAG = 0, D.ENDDATE = GETDATE()
                        OUTPUT $ACTION ACTIONOUT, S.DEPTNO, S.DNAME, S.LOC,
                        GETDATE() AS STARTDATE, '2999-12-31' AS ENDDATE, 1 AS FLAG
) AS MERGE_OUT
WHERE MERGE_OUT.ACTIONOUT = 'UPDATE';


Observation - Before Run 1:

Observation - After Run 1:

Observation - Before Run 2:

Observation - After Run 2:

Conclusion: Slowly Changing Dimension Type 2 is a method to maintain complete historical Data and Merge (Upsert) handles it easily. Hope this helps! Happy TSQL'ing :)

Monday, December 23, 2013

Nth Highest Salary in Employee Table

-- Query to find the Nth Highest Salary in Employee Table

SELECT EMPNO, ENAME, SAL
FROM
(
SELECT DENSE_RANK() OVER (ORDER BY SAL DESC) AS N,
EMPNO, ENAME, SAL FROM EMP
) DT
WHERE N = 3

I used N=3, as I need the third highest salary. We can check the data for various ranks.


Also check how the below window functions return data differently.

SELECT ROW_NUMBER() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP
SELECT RANK() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP
SELECT DENSE_RANK() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP

Tuesday, December 17, 2013

Update Statement with Custom Requirement

Hi Guys! hope you all doing great! Today I came up with a post on an interview question based on EMPLOYEE Table.

Task: Update Salaries of the Employees as per below requirement.

DEPTNO
INCREMENT
10
100
20
200
30
300
40
400
X0
X00

We can notice there is a correlation between DEPTNO and INCREMENT. For DEPTNO 10, its 100, DEPTNO 20, its 200, and so on.......for DEPTNO X0 its X00.

The structure of EMPLOYEE table is as below:
Input: SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMPLOYEE;
















Solution: Using WHILE Loop
DECLARE @INCREMENT INT = 100, @DEPTNO INT
DECLARE @TV TABLE (DEPTNO INT, FLAG BIT)

INSERT INTO @TV
SELECT DISTINCT DEPTNO, FLAG = 0 FROM EMPLOYEE

SET @DEPTNO = (SELECT TOP 1 DEPTNO FROM @TV WHERE FLAG = 0)

WHILE (@DEPTNO IS NOT NULL)
BEGIN
            UPDATE EMPLOYEE
            SET SAL = SAL + @INCREMENT
            WHERE DEPTNO = @DEPTNO

            UPDATE @TV
            SET FLAG = 1
            WHERE DEPTNO = @DEPTNO AND FLAG = 0

            SET @INCREMENT = @INCREMENT + 100
            SET @DEPTNO = (SELECT TOP 1 DEPTNO FROM @TV WHERE FLAG = 0)
END

Output: SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMPLOYEE;

















Conclusion: Compare both the screen shots and note that, the salaries are updated as per the requirement table mentioned on the top of this post. Hope this helps! Happy TSQL'ing ;)

Sunday, December 15, 2013

Advanced T-SQL Coding Example

Here is a T-SQL task that needs a non-traditional (I repeat, Non Traditional.) way of coding. It should reach the expectations of Senior T-SQL Coding Standards and obviously highly tuned for performance.

Input Data: Here is the sample input data of Employees.



















Output Needed: The expected output is as shown in figure below.



















You guessed it right! Both detail and aggregate level data in a single output. 

Let me add, "the objective of this post here is about discussing the standard of T-SQL coding we use to achieve the task".

Traditional Way:
The below code is what comes to ones mind to achieve the task in a traditional way.

SELECT A.EMPNO, A.ENAME, A.SAL, B.SumSalDeptWise, C.SumSalWholeOrg
FROM EMP A
INNER JOIN
            (SELECT DEPTNO, SUM(SAL) AS SumSalDeptWise
                        FROM EMP
                        GROUP BY DEPTNO) B
            ON A.DEPTNO = B.DEPTNO
CROSS JOIN
            (SELECT SUM(SAL) AS SumSalWholeOrg FROM EMP) C


Now let's see the Non-Traditional and Highly Tuned Version of Code.
Non Traditional Way: (The future T-SQL Code)

SELECT
            EMPNO, ENAME, SAL,
            SUM(SAL) OVER (PARTITION BY DEPTNO) AS SumSalDeptWise,
            SUM(SAL) OVER () AS SumSalWholeOrg
FROM EMP;

Conclusion: Found the difference? The future of T-SQL coding is changing with newer versions of SQL Server. Hope someone gets benefitted with this post ! Happy TSQL'ing :)



Multiplication Table Using WHILE Loop

Spending sometime for T-SQL on weekends made me write a stored procedure that returns Multiplication Table for any given number.

Stored Procedure Code:

CREATE PROCEDURE dbo.usp_MultiplicationTable
@N INT
AS
BEGIN
SET NOCOUNT ON;

            DECLARE @I INT = @N, @J INT = 1
            DECLARE @TV TABLE (RESULT VARCHAR(20))

            WHILE (@J <= 10)
            BEGIN
                        INSERT INTO @TV
                        SELECT CAST(@I AS VARCHAR) + CHAR(32) + '*' + CHAR(32) +
                        CAST(@J AS VARCHAR) + CHAR(32) + '=' + CHAR(32) + CAST(@I * @J AS VARCHAR)
                       
                        SET @J = @J + 1
            END
            SELECT * FROM @TV;

END

Execution Code:
On passing '2' as the input value to the stored procedure, it returned below output:
















Hope this helps someone!

Conclusion: T-SQL is like a girl friend. You get close to it, it loves back. You go away, it starts hating you. So Happy TSQLing ;)