Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [The Birth Day Puzzle] – Write a query which will find the Date of Birth of employees whose birthdays lies between May 7 and May 15

Sample Input

EmpId EmpName BirthDate
1 Pawan 04-12-1983
2 Zuzu 28-11-1986
3 Parveen 07-05-1977
4 Mahesh 13-01-1983
5 Ramesh 09-05-1983

Expected Output

EmpName BirthDate
Parveen 07-05-1977
Ramesh 09-05-1983

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script Use the below script to generate the source table and fill them up with the sample data.


--Create table

CREATE TABLEEmpBirth
(
 EmpId INT  IDENTITY(1,1) 
,EmpName VARCHAR(50) 
,BirthDate DATETIME 
)

--Insert Data
INSERT INTO EmpBirth(EmpName,BirthDate)
SELECT 'Pawan' , '12/04/1983'
UNION ALL
SELECT 'Zuzu' , '11/28/1986'
UNION ALL
SELECT 'Parveen', '05/07/1977'
UNION ALL
SELECT 'Mahesh', '01/13/1983'
UNION ALL
SELECT'Ramesh', '05/09/1983'

--Verify data
SELECT EmpId,EmpName,BirthDate FROM EmpBirth

Update May 14 | Solution


--

/************   SOLUTION 1    | Pawan Kumar Khowal     ****************/

;WITH CTE AS 
(  
		SELECT EmpId , EmpName,BirthDate , MONTH(BirthDate) AS Mont, DAY(BirthDate) AS days
        FROM EmpBirth
)
SELECT EmpId , EmpName,BirthDate FROM CTE WHERE Mont = 5 AND days >= 7 AND days <= 15


/************   SOLUTION 2    | Pawan Kumar Khowal     ****************/

;WITH CTE  
AS ( 
	SELECT EmpId , EmpName,BirthDate,
	CASE 
      WHEN  MONTH(BirthDate) < 10 THEN  '0' + CAST(MONTH(BirthDate) AS VARCHAR(1))
			ELSE 
				CAST(MONTH(BirthDate) AS VARCHAR(2))
      END 
	+ CASE WHEN DAY(BirthDate) < 10 THEN '0' + CAST(DAY(BirthDate) AS VARCHAR(1))
            ELSE
                  CAST(DAY(BirthDate) AS VARCHAR(2))
            END FormattedDOB
FROM EmpBirth
)
SELECT EmpId , EmpName,BirthDate FROM CTE WHERE FormattedDOB BETWEEN '0507' AND '0515'



--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com