I am trying to do assignment but i am getting double values.I am choosing April Month as standard to compute days remaining and days taken. For Filtering holidays BenefitTypeID is equal to 1. Please help me out. Here is question and query that i have written:
ERD DIAGRAM for database is at SQL Server equivalent to GROUP_CONCAT()
1.Number of benefits days allotted to each employee
2.Number of benefit days taken year-to-date
3.Number of benefit days remaining in the calendar year
4.Number of holidays allotted to each employee
5.Number of holidays taken year-to-date
6.Number of holidays remaining in the calendar year
For this assignment, write a query that produces the results described above. Assume that you are running the report for the 2013 calendar year. As in the previous report, in the real-world, you would likely use the getdate() function to determine the current date and run the report from the beginning of the current year until the present time. For this assignment, however, you may hardcode the year 2013 in your query and retrieve all of the data for that year.
SELECT
E.LastName+ ' '+E.FirstName As [Employee Name],
count (B.BenefitTypeID)as [No of Benefit Days],
SUM(CASE WHEN BT.BenefitTypeID='1' THEN 1 ELSE 0 END) As [Number of Holidays],
SUM(CASE WHEN B.BT_Month='4' AND B.BT_Year='2013' THEN 1 ELSE 0 END) As [Number of Days Taken],
SUM(CASE WHEN B.BT_Month!='4' and B.BT_Year!='2013' THEN 1 ELSE 0 END) As [Number of Days Remaining],
SUM(CASE WHEN BT.BenefitTypeID='1' and B.BT_Month='4' and B.BT_Year='2013' THEN 1 ELSE 0 END) As [Number of Holidays Taken],
SUM(CASE WHEN BT.BenefitTypeID='1' and B.BT_Month!='4' and B.BT_Year!='2013' THEN 1 ELSE 0 END) As [Number of Holidays Remaining]
FROM BenefitsTaken B
JOIN Employees E
ON( B.EmpID = E.EmpID )
LEFT Join BenefitTypes BT
ON(B.BenefitTypeID=BT.BenefitTypeID)
Group By E.LastName,E.FirstName,BT.BenefitTypeID,B.BT_Month,B.BT_Year
Here are query results. Employee names should be appear once, Thanks
Employee Name No of Benefit Days Number of Holidays Number of Days Taken Number of Days Remaining Number of Holidays Taken Number of Holidays Remaining
------------------------------------------------------------- ------------------ ------------------ -------------------- ------------------------ ------------------------ ----------------------------
Doe James 1 0 1 0 0 0
Doe James 1 0 0 0 0 0
Jones Mark 2 2 0 0 0 0
Jones Mark 1 0 1 0 0 0
Jones Mark 1 0 0 0 0 0
Rice Luke 1 0 1 0 0 0
Rice Luke 1 0 0 0 0 0
Smith Matthew 1 1 1 0 1 0
Smith Matthew 1 0 1 0 0 0
(9 row(s) affected)
