CODEWITHSUNDEEP
sqlsql-server-2012
Berlin
Berlin

Reputation: 33

SQL query to find available future dates except weekends

I have table called "detail" where i am storing start date and end date of jobs.I have one more table called "leaves" which is also have leave startdate and leave enddate fields.I need to find the nearest available dates of a user without weekends and leave dates. 

DECLARE @PackagerLastAssignedDate DATETIME

SELECT @PackagerLastAssignedDate = MAX(EndDate)  FROM detail WHERE userId  = 1 

SELECT lveStartDate,lveEndDate  FROM Leaves WHERE UserId = 1 and lveStartDate > @PackagerLastAssignedDate

Thanks In advance Berlin.M

Upvotes: 2

Views: 403

Answers (1)

Devart
Devart

Reputation: 121902

Try this one -

DECLARE 
      @DateFrom DATETIME
    , @DateTo DATETIME

SELECT 
      @DateFrom = '20130101'
    , @DateTo = '20130202'

SELECT [Date]
FROM (
    SELECT [Date] = DATEADD(DAY, sv.number, t.DateFrom)
    FROM (
        SELECT 
              DateFrom = @DateFrom
            , diff = DATEDIFF(DAY, @DateFrom, @DateTo)
    ) t
    JOIN [master].dbo.spt_values sv ON sv.number <= diff
    WHERE sv.[type] = 'p'
) t2
WHERE DATENAME(WEEKDAY, [Date]) NOT IN ('Saturday', 'Sunday')
    AND NOT EXISTS (
        SELECT 1 
        FROM dbo.Leaves l
        WHERE l.UserId = 1
            AND t2.[Date] BETWEEN l.lveStartDate AND l.lveEndDate 
    )

Upvotes: 1

Related Questions