CODEWITHSUNDEEP
sqlsql-server
Programmer2015
Programmer2015

Reputation: 55

Need help on DATE query in SQL server?

I have two tables in SQL Server:

  1. EMP
  2. On_Vacation with colums EmpId, LeaveType, StartingFrom, EndingTo,RejoiningDate

The On_Vacation table stores the information of employees who are on leave.

enter image description here

I'm trying to query the table in such a way that my query has the following columns:

EmpId, 24-1-2016, 25-1-2016, 26-1-2016, 27-1-2016, 28-1-2016

The result query columns are the dates of this week. If Employee is not on leave on these dates, it should write available. Otherwise it should write the leave type.

I'm very new to this type of queries, kindly help me experts..

Upvotes: 0

Views: 76

Answers (2)

Adam Jacobson
Adam Jacobson

Reputation: 564

You can't change the column name without dynamic sQL (which complicates matters). Here's another suggestion: what is you change your date table to includes the name of the day of the week, like this:

CREATE TABLE DaysWeeks 
  (CalYear SMALLINT NOT NULL,
   WeekNumber TINYINT NOT NULL,
   CalDate DATE NOT NULL,
   DayOfWeekNumber TINYINT,
   DayOfWeekName VARCHAR(9)
   CONSTRAINT PK_DaysWeeks PRIMARY KEY CLUSTERED (CalYear, WeekNumber, CalDate)
   )

with values like this:

INSERT INTO dbo.DaysWeeks
        ( CalYear, WeekNumber, CalDate,DayOfWeekNumber,DayOfWeekName )
VALUES  ( 2016, 4, '01/24/2016',1,'Sunday'),
        ( 2016, 4, '01/25/2016',2,'Monday'),
        ( 2016, 4, '01/26/2016',3,'Tuesday'),
        ( 2016, 4, '01/27/2016',4,'Wednesday'),
        ( 2016, 4, '01/28/2016',5,'Thursday'),
        ( 2016, 4, '01/29/2016',6,'Friday'),
        ( 2016, 4, '01/30/2016',7,'Saturday')

Now, you can have a query which pivots based on the day of the week:

WITH cte AS (
SELECT EmpDays.Employee, EmpDays.CalDate, EmpDays.DoWName, ISNULL(v.Leave_Type,'Available') AS Available
FROM dbo.On_Vacation v
RIGHT OUTER JOIN
 (SELECT e.EmpID AS Employee, dw.CalDate AS CalDate, dw.DayOfWeekName AS DoWName
  FROM dbo.DaysWeeks dw,
      dbo.Employee e
      WHERE dw.CalYear = 2016 AND dw.WeekNumber = 4) AS EmpDays
ON
   v.EmpID = EmpDays.Employee
   AND v.StartingFrom <= empdays.CalDate 
   AND v.EndingTo >= empdays.CalDate
)
SELECT * FROM cte 
PIVOT (MAX(cte.Available) FOR DoWName IN (['Sunday'],['Monday'],['Tuesday'],['Wednesday'],['Thursday'],['Friday'],['Saturday'])

If your really need the actual dates in your columns, I would adjust this to use dynamic SQL. But before doing that (which, IMHO, makes the code much harder to read and maintain, not that this is so straightforward), I'd ask how you were going to present the data and therefore whether that might be handled in the report or presentation layer.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You would do this with conditional aggregation:

select e.empid,
       coalesce(max(case when '2016-01-24' between v.startdate and v.enddate 
                         then leave_type end),
                'available') as [2016-01-14],
       coalesce(max(case when '2016-01-25' between v.startdate and v.enddate 
                         then leave_type end),
                'available') as [2016-01-15],
       . . .
from emp e left join
     from vacation v
     on e.empid = v.empid
group by e.empid;

Upvotes: 1

Related Questions