Menu

Showing posts with label Tips and Tricks. Show all posts
Showing posts with label Tips and Tricks. Show all posts

Tuesday, 1 October 2013

Fetching Date records in SQL


Queries on date related data are very common in all kind of applications.But when we query on date columns we should be aware of how it works in background.If data of format date is stored in column which is varchar datatype, we should know when implicit inversion would take place.Otherwise we could get unexpected results.


--create table
CREATE TABLE #GET_DATE(DT VARCHAR(50))

--insert date values
INSERT INTO #GET_DATE VALUES ('11/01/2011'),('12/01/2012'),('10/01/2013')

SELECT * FROM #GET_DATE

Output:
DT
----------
11/01/2011
12/01/2012
10/01/2013

--check for dates less than or equal to '10/01/2013' (3 results expected)
SELECT *
FROM #GET_DATE
WHERE CONVERT(VARCHAR(50),DT,101)<=CONVERT(VARCHAR(50),GETDATE(),101)

Output:
DT
----------
10/01/2013   --wrong output

--with implicit conversion of DT column
SELECT *
WHERE DT<=GETDATE()

Output:
DT
----------
11/01/2011
12/01/2012
10/01/2013

In the first query it is actually checking string values and not a date. So, the result what we got is based on string values comparison.

Wednesday, 23 January 2013

How To Get Previous Date/Next Date in SQL Server


--How To Get Previous Date/Next Date

     If you are working on date related queries, you may have came across scenarios where you wanted find out what is the immediate previous date or next date.

         In the following example I have explained how to do it. 
. 
CREATE TABLE DEPT (Srno INT,Department VARCHAR(100),CreatedDate   DATETIME)

INSERT INTO DEPT VALUES
(1,'HR',' 07/12/2012 10:30'),
(2,'HR','07/12/2012 10:41') ,
(3,'HR','08/12/2012 07:08'),
(4,'Development','07/12/2012 10:30') ,
(5,'Development','07/12/2012 11:33') ,
(6,'Testing',' 07/12/2012 17:18') ,
(7,'Testing',' 07/12/2012 10:31') ,
(8,'Testing','07/12/2012 11:38')

SELECT * FROM DEPT

--query output
Srno        Department   CreatedDate            
----------- ------------ -----------------------
1           HR           2012-07-12 10:30:00.000
2           HR           2012-07-12 10:41:00.000
3           HR           2012-08-12 07:08:00.000
4           Development  2012-07-12 10:30:00.000
5           Development  2012-07-12 11:33:00.000
6           Testing      2012-07-12 17:18:00.000
7           Testing      2012-07-12 10:31:00.000
8           Testing      2012-07-12 11:38:00.000

--get Next Date
SELECT T1.Srno,T1.Department,T1.CreatedDate,MIN(T2.CreatedDate) AS NextDate
FROM DEPT T1
LEFT OUTER JOIN DEPT T2
ON T1.Department=T2.Department AND T1.CreatedDate<T2.CreatedDate
GROUP BY T1.Srno,T1.Department,T1.CreatedDate

--Result
Srno        Department   CreatedDate             NextDate
----------- ------------ ----------------------- -----------------------
1           HR           2012-07-12 10:30:00.000 2012-07-12 10:41:00.000
2           HR           2012-07-12 10:41:00.000 2012-08-12 07:08:00.000
3           HR           2012-08-12 07:08:00.000 NULL
4           Development  2012-07-12 10:30:00.000 2012-07-12 11:33:00.000
5           Development  2012-07-12 11:33:00.000 NULL
6           Testing      2012-07-12 17:18:00.000 NULL
7           Testing      2012-07-12 10:31:00.000 2012-07-12 11:38:00.000
8           Testing      2012-07-12 11:38:00.000 2012-07-12 17:18:00.000

--get Previous Date
SELECT T1.Srno,T1.Department,T1.CreatedDate,MAX(T2.CreatedDate) AS PreviousDate
FROM DEPT T1
LEFT OUTER JOIN DEPT T2
ON T1.Department=T2.Department AND T1.CreatedDate>T2.CreatedDate
GROUP BY T1.Srno,T1.Department,T1.CreatedDate

--Result
Srno        Department     CreatedDate             PreviousDate
----------- -------------- ----------------------- -----------------------
1           HR             2012-07-12 10:30:00.000 NULL
2           HR             2012-07-12 10:41:00.000 2012-07-12 10:30:00.000
3           HR             2012-08-12 07:08:00.000 2012-07-12 10:41:00.000
4           Development    2012-07-12 10:30:00.000 NULL
5           Development    2012-07-12 11:33:00.000 2012-07-12 10:30:00.000
6           Testing        2012-07-12 17:18:00.000 2012-07-12 11:38:00.000
7           Testing        2012-07-12 10:31:00.000 NULL
8           Testing        2012-07-12 11:38:00.000 2012-07-12 10:31:00.000

Let me know if you know any other methods.

Tuesday, 22 January 2013

Find 2nd,3rd and 5th highest salary of employee in SQL Server



CREATE TABLE EMP(EmpID INT,EmpName VARCHAR(10),Salary  INT)

INSERT INTO EMP VALUES
(1,'Suresh',7000),
(2,'Prasanthi',8000),
(3,'Mahesh',9000),
(4,'Sai',10000),
(5,'Nagaraju',11000),
(6,'Mahendra',12000),
(7,'Sanjay',13000),
(8,'Santhosh',14000),
(9,'Raju',15000),
(10,'Phani',10000),
(11,'Kumar',12000),
(12,'Prasad',9000),
(13,'Siva',12000),
(14,'Madhav',14000),
(15,'Donthi',11000)

SELECT * FROM EMP ORDER BY SALARY

EmpID       EmpName    Salary
----------- ---------- -----------
1           Suresh     7000
2           Prasanthi  8000
3           Mahesh     9000
12          Prasad     9000
10          Phani      10000
4           Sai        10000
5           Nagaraju   11000
15          Donthi     11000
11          Kumar      12000
13          Siva       12000
6           Mahendra   12000
7           Sanjay     13000
8           Santhosh   14000
14          Madhav     14000
9           Raju       15000


--Find 2nd,3rd and 5th highest salary of employee in SQL Server
SELECT T1.*
FROM EMP T1
WHERE T1.EMPID IN (SELECT T1.EMPID
FROM EMP T2
WHERE T2.SALARY>=T1.SALARY
HAVING COUNT(DISTINCT SALARY) IN (2,3,5))
ORDER BY T1.SALARY DESC

--Result
EmpID       EmpName    Salary
----------- ---------- -----------
8           Santhosh   14000
14          Madhav     14000
7           Sanjay     13000
5           Nagaraju   11000
15          Donthi     11000


--Method can also be used find out the 'n'th highest Salaried employee
--Here I have explained how to find 5th highest Salaried employee
SELECT T1.*
FROM EMP T1
WHERE T1.EMPID IN (SELECT T1.EMPID
FROM EMP T2
WHERE T2.SALARY>=T1.SALARY
HAVING COUNT(DISTINCT SALARY)=4)

EmpID       EmpName    Salary
----------- ---------- -----------
6           Mahendra   12000
11          Kumar      12000
13          Siva       12000



Also Check Out Other Methods: 
How to find multiple employee(s) having 'n'th highest salary >>
How to find employee having 'n'th highest Salary>>

 

Monday, 21 January 2013

How to find multiple employee(s) having 'n'th highest salary (when more than one employee are having same salary)


CREATE TABLE EMP_SALARY(EMP_ID INT,SALARY INT)

INSERT INTO EMP_SALARY VALUES(1,10000),(2,5000),(3,6000),(4,6000),(5,5000),(6,6000)

SELECT * FROM EMP_SALARY

--Output:
EMP_ID      SALARY
----------- -----------
1           10000
2           5000
3           6000
4           6000
5           5000
6           6000

--1st method using 'WITH TIES' along with 'TOP'
SELECT TOP 1 WITH TIES A.*
FROM EMP_SALARY A
WHERE A.EMP_ID IN ( SELECT TOP 2 WITH TIES B.EMP_ID
                    FROM EMP_SALARY B
                    ORDER BY B.SALARY DESC)
ORDER BY A.SALARY

--Result:
EMP_ID      SALARY
----------- -----------
3           6000
4           6000
6           6000

--2nd method using 'COUNT'
SELECT A.*
FROM EMP_SALARY A
WHERE 2=(SELECT COUNT(DISTINCT B.SALARY)
         FROM EMP_SALARY B
         WHERE B.SALARY>=A.SALARY)

--Result:
EMP_ID      SALARY
----------- -----------
3           6000
4           6000
6           6000

--3rd method using 'DENSE_RANK'
SELECT A.EMP_ID,A.SALARY
FROM (SELECT EMP_ID,SALARY,
             DENSE_RANK() OVER(ORDER BY SALARY DESC) AS SALARY_ORD
      FROM EMP_SALARY)A
WHERE A.SALARY_ORD=2

--Result:
EMP_ID      SALARY
----------- -----------
3           6000
4           6000
6           6000

                        How to find employee having 'n'th highest Salary>>