Thursday, January 14, 2016

Selects 2

Use CommunityAssist;

Select EmployeeKey, EmployeeMonthlySalary, 
   EmployeeMonthlySalary * 12 as [Annual Salary]
   From Employee;

Select Avg(EmployeeMonthlySalary) from Employee
Where Not EmployeeMonthlySalary =0;

Select Sum(EmployeeMonthlySalary) from Employee;
Select Min(EmployeeMonthlySalary) from Employee;
Select Max(EmployeeMonthlySalary) from Employee;
Select Count(EmployeeMonthlySalary) from Employee;

Select * From ServiceGrant;

Select ServiceKey, sum(GrantAllocation) as Total from ServiceGrant
Where ServiceKey > 2
Group by ServiceKey
having sum(GrantAllocation) > 2500;

Select ServiceKey, sum(GrantAllocation) as Total from ServiceGrant
Group by ServiceKey
having sum(GrantAllocation) > 2000
Order by total desc;

Select Distinct Year(GrantDate) from ServiceGrant;
Select Distinct Month(GrantDate) from ServiceGrant;

Select Year(GrantDate) as Year, Month(GrantDate) as [Month], 
case Month(GrantDate)
when 8 then 'August'
when 9 then 'September'
end as [word Month],
Sum(GrantAllocation) as Total from ServiceGrant
Group by Year(GrantDate), Month(GrantDate);

Select Distinct DatePart(mm,GrantDate), Datepart(dd,GrantDate) from serviceGrant
Select Distinct DatePart(hour, GrantDate) From ServiceGrant
Where grantKey=2;

Select GrantDate, GrantReviewDate, DateDiff(dd,GrantDate,GrantReviewDate) 
as [Processing Time]
From ServiceGrant;

Select DateDiff(minute,'1/14/2016','1/14/2020');

Select GetDate() as Today;

Select DateAdd(dd, 365, GetDate());

Select * from Employee;

Select EmployeeKey, EmployeeHireDate
From Employee
Where Year(EmployeeHireDate) between 2003 and 2008;

--###-##-####
Select substring(EmployeeSSNumber,1,3) + '-' 
+ substring(EmployeeSSNumber,4,2) + '-' 
+ substring(EmployeeSSNumber,6,4)  From Employee;

Select format(cast(EmployeeSSNumber as int),'000-00-0000') from Employee;

No comments:

Post a Comment