i have question in query creation [Resolved]

Posted by Sudheep.Grandhe under Sql Server on 4/24/2015 | Points: 10 | Views : 410 | Status : [Member] | Replies : 2
1. Table will log the websites traffic, table has website_name,page name,IP address, visited date columns. Write a query to get website visited in last 30 days,total number of visit for each website,total number of unique page view, total number of unique visitors (using IP address).



2. Employee table has Employee ID,Employee name,Joining Salary columns. Salary_hike table columns are Employee ID,Salary Hike percentage,Year .

Write a query to get total salary paid to employee during 2000 to 2015 year.

Best,
Sudheep.



Responses

Posted by: Bandi on: 4/27/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Below is the answer for second question

--Table Creation
DECLARE @Employee table(EmployeeID int,EmployeeName varchar(100),JoiningSalary int)
DECLARE @Salary_hike table (EmployeeID int,SalaryHikePercentage int,Year int)

-- Sample data for both tables
insert into @EMPLOYEE
SELECT 1, 'AAA', 180 union all
SELECT 2, 'BBB', 200 union all
SELECT 3, 'CCC', 150 union all
SELECT 4, 'DDD', 100

insert into @Salary_hike
SELECT 1, 10, 2000 union all
SELECT 1, 10, 2001 union all
SELECT 1, 10, 2002 union all
SELECT 1, 10, 2003 union all
SELECT 1, 10, 2004 union all
SELECT 1, 10, 2005 union all
SELECT 1, 40, 2010 union all
SELECT 1, 20, 2015

--Write a query to get total salary paid to employee during 2000 to 2015 year

--Below Query is for getting Total pais salary based on employee
DECLARE @RevisedSal bigint , @EmployeeID int = 1

SELECT @RevisedSal = ISNULL(@RevisedSal, JoiningSalary)+ ( ISNULL(@RevisedSal, JoiningSalary)*SalaryHikePercentage)/100
from @Employee e
join @Salary_hike eh on e.employeeID= eh.employeeID
where e.EmployeeID = @EmployeeID
and year between 2000 and 2015

SELECT @RevisedSal TotalSalPaid, @EmployeeID as EmployeeID


Output:
TotalSalPaid EmployeeID
529 1


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sudheep.Grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 4/27/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Answer for first question


select websiteName, count(visit_date), count(distinct page_name), count( distinct IP_address) 
from website traffic
where visit_date >= dateadd( dd, -30, getdate())
group by websiteName


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sudheep.Grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response