SQL Server dates

to get different type of dates based on a date.

select
getdate()
, cast(cast(getdate() as date) as datetime) today_00hrs
, dateadd(dd,-1, cast(getdate() as date)) as yesterday_date
, cast(dateadd(dd,-1, cast(getdate() as date)) as datetime) as yesterday_00hrs
, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear
, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) as StartOfMonth
, (DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)) StartOfLastMonth
, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0) as Startof3monthsback
, DATEPART(week, GETDATE())weekofdate
, DATEADD(yy,-1,getdate() ) LastYearSameDateTime
, (DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)) LastMonth_1stday00hrs
, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) CurrentMonth_1stday00hrs
,  DATENAME(year, ’12:10:30.123′)
,DATENAME(month, ’12:10:30.123′)
,DATENAME(day, ’12:10:30.123′)
,DATENAME(dayofyear, ’12:10:30.123′)
,DATENAME(weekday, ’12:10:30.123′);

float to nvarchar in sql server

when you need to select from one column of datatype float(which has phone number) and insert it in another column as nvarchar- cast wont work

cast : cast([Column_Name] as nvarchar(50)). The result will be in format 9.07235e+009 instead of a 10 digit number (phone number).

use SELECT STR([Column_Name],10,0) ** This is SQL Server solution, for other servers check their docs

Recursive SQL query to find the parent record or first and last or latest records

When we have a scenario to pick up first and  last record/hierarchy from a table which maintains history of changes(EmployeeHistory_Table).

 

Hierarchy_Table

role hierarchies
Trainee_System_Engineer
System_Engineer
Senior_Software_Eng
Team_Leader
Manager
Senior_Exec

 

EmployeeHistory_Table

emp_name current_role new_role
Dan Trainee_System_Engineer System_Engineer
Dan System_Engineer Senior_Software_Eng
Dan Senior_Software_Eng Manager
Tom System_Engineer Senior_Software_Eng
Tom Senior_Software_Eng Team_Leader
Jack System_Engineer Team_Leader
Jack Team_Leader Manager
Jack Manager Senior_Exec

 

expected output:

emp_name started_role current_role
Jack System_Engineer Trainee_System_Engineer
Tom System_Engineer Team_Leader
Dan Trainee_System_Engineer Manager

 

below is the sql for this kind of scenario:

CREATE TABLE #emp_RoleChanges (
emp_name VARCHAR(30),
current_role VARCHAR(30) ,
new_role VARCHAR(30)
);

INSERT INTO #emp_RoleChanges (emp_name , current_role , new_role )
VALUES
(‘Dan’, ‘Trainee_System_Engineer’, ‘System_Engineer’),
(‘Dan’, ‘System_Engineer’, ‘Senior_Software_Eng’),
(‘Dan’, ‘Senior_Software_Eng’, ‘Manager’),
(‘Tom’, ‘System_Engineer’, ‘Senior_Software_Eng’),
(‘Tom’, ‘Senior_Software_Eng’, ‘Team_Leader’),
(‘Jack’, ‘System_Engineer’, ‘Team_Leader’),
(‘Jack’, ‘Team_Leader’, ‘Manager’),
(‘Jack’, ‘Manager’, ‘Trainee_System_Engineer’)
;

select * from #emp_RoleChanges
;
WITH q AS (
SELECT 1 AS LEVEL, emp_name , current_role , new_role
FROM #emp_RoleChanges
UNION ALL
SELECT LEVEL + 1, q.emp_name, q.current_role, u.new_role
FROM q
INNER JOIN #emp_RoleChanges u ON u.current_role = q.new_role and u.emp_name = q.emp_name
)
–select * from q
–order by 2,1

SELECT –q.LEVEL,
q.emp_name , q.current_role started_role, q.new_role current_role
FROM q
where q.level = (select max(p.level) from q p where p.emp_name = q.emp_name)