include weekends on sql query

Question

I have a database that stores the log-in and log-out of the employees but we don't have work on weekends. My supervisor want the DTR report format(I'm using RDLC report) include the weekends. (see attached image)

enter image description here

The image above is the expected output format for DTR. I just want to know how to include Weekends though my data are on weekdays only. Is it possible to do this using SQL Query? If yes, should I use looping in sql here?

SQL Code:

select user_id,log_date,login_time,logout_time 
from table_DTR
where user_id = 'USER1'
AND log_date BETWEEN '11/21/2014' AND '12/09/2014'

@DeepakPawar: yes. but my database don't have a weekends because we don't have work on weekends so if date is = weekends then display SATURDAY OR SUNDAY. I update my post above. — Waelhi, Dec 15 '14 at 06:26

Deep · Answer 1 · 2014-12-15T07:09:46.030

try below solution :

DECLARE @startdate DATE = '11/21/2014' -- your start date 
DECLARE @enddate DATE = '12/09/2014' -- your start date 
-- create list of all dates between min(log_date) and  MAX(log_date)
;WITH cte
     AS (SELECT @startdate AS log_date
         UNION ALL
         SELECT Dateadd(dd, 1, log_date) log_date
         FROM   cte
         WHERE  log_date < @enddate)
-- select the data using left outer join so that it will return missing dates too.
SELECT t1.user_id,
       c.log_date,
       t2.login_time,
       t2.logout_time
FROM   cte c
       CROSS JOIN (SELECT DISTINCT user_id
                   FROM   mytable) t1
       LEFT OUTER JOIN mytable t2
                    ON t2.user_id = t1.user_id
                       AND t2.log_date = c.log_date
ORDER  BY t1.user_id,c.log_date
OPTION(maxrecursion 1000)

It will return null in time columns for weekends. Note : if you are getting error : The statement terminated. The maximum recursion 100 has been exhausted before statement completion. then try using OPTION(maxrecursion 3000) or greater.

I get this error: `The statement terminated. The maximum recursion 100 has been exhausted before statement completion.` — Waelhi, Dec 15 '14 at 06:39

Sandeep · Accepted Answer · 2014-12-15T07:30:29.347

Use common table expression and generate date range with from and to date and than use CTE as left join to actual table. I haven't used user_id filter in left join so apply it to your query:

 DECLARE @TMEP TABLE
    (
        [Date] DATE,
        [IN] VARCHAR(10),
        [OUT] VARCHAR(10)
    )

    INSERT INTO @TMEP VALUES ('2014-11-11','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-11-12','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-11-13','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-11-14','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-11-15','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-11-18','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-11-19','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-11-20','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-11-21','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-11-22','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-11-25','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-11-26','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-11-27','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-11-28','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-11-29','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-12-1','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-12-2','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-12-3','7:30','5:30')
    INSERT INTO @TMEP VALUES ('2014-12-4','7:30','5:30')

    DECLARE @FromDate DATE 
    SET @FromDate = '2014-11-11 06:00:00.000'
    DECLARE @ToDate DATE
    SET @ToDate = '2014-12-11 06:00:00.000'

    ;WITH CTE_TableDate ([CTEDate]) as
    (
        SELECT @FromDate
        UNION ALL
        SELECT  DATEADD(DAY,1,CTEDate) FROM CTE_TableDate WHERE [CTEDate] < @ToDate
    )
    SELECT 
        CTE_TableDate.CTEDate,
        CASE WHEN DATEPART(DW, CTE_TableDate.CTEDate) = 7 THEN 'SATURDAY'
    WHEN DATEPART(DW, CTE_TableDate.CTEDate) = 1 THEN 'SUNDAY' 
    ELSE TEMP.[In] END AS [IN],
    CASE WHEN DATEPART(DW, CTE_TableDate.CTEDate) = 7 THEN 'SATURDAY'
    WHEN DATEPART(DW, CTE_TableDate.CTEDate) = 1 THEN 'SUNDAY' 
    ELSE TEMP.[OUT] END AS [OUT]
    FROM CTE_TableDate
    LEFT JOIN
    (
        select 
            [Date],
            [IN],
            [OUT] 
        from 
            @TMEP) TEMP
    ON
        CTE_TableDate.CTEDate = TEMP.[Date]

@Unknownymous2 i have tried it on my pc and its working. I have checked it on sql fiddle but seems problem with site because error message says "MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file" — Sandeep, Dec 15 '14 at 06:53
@Unknownymous2 i have checked on both sql server 2012 and 2014 on my pc. do you getting any error? — Sandeep, Dec 15 '14 at 07:07
hee: Msg 139, Level 15, State 1, Line 0 Cannot assign a default value to a local variable. Msg 139, Level 15, State 1, Line 0 Cannot assign a default value to a local variable. Msg 137, Level 15, State 2, Line 33 Must declare the scalar variable "@FromDate". Msg 137, Level 15, State 2, Line 35 Must declare the scalar variable "@ToDate". — Waelhi, Dec 15 '14 at 07:19
@Unknownymous2 you are missing declaring FromDate and ToDate variables which i have used in query. I am assuming you are using store procedure to generate report than assign from and to parameter values to FromDate and ToDate variables. — Sandeep, Dec 15 '14 at 07:24
no. I dont use stored procedure., I just copy paste your code to my sql server studio.. i didn't edit anything just to check.. but my actual code if this code of yours is working I will put this code on my code behind in visual studio.. vb.net. to select date range — Waelhi, Dec 15 '14 at 07:29
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/66890/discussion-between-sandeep-and-unknownymous2). — Sandeep, Dec 15 '14 at 07:31

score 1 · Answer 3 · edited May 23 '17 at 12:12

You can create a Calendar table as below:

CREATE TABLE dbo.Calendar
(
  dt DATE PRIMARY KEY, -- use SMALLDATETIME if < SQL Server 2008
  IsWorkDay BIT
);

DECLARE @s DATE, @e DATE;
SELECT @s = '2000-01-01' , @e = '2029-12-31';

INSERT dbo.Calendar(dt, IsWorkDay)
  SELECT DATEADD(DAY, n-1, '2000-01-01'), 1 
  FROM
  (
    SELECT TOP (DATEDIFF(DAY, @s, @e)+1) ROW_NUMBER() 
      OVER (ORDER BY s1.[object_id])
      FROM sys.all_objects AS s1
      CROSS JOIN sys.all_objects AS s2
  ) AS x(n);

SET DATEFIRST 1;

-- weekends
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE DATEPART(WEEKDAY, dt) IN (6,7);

-- Christmas
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE MONTH(dt) = 12
  AND DAY(dt) = 25
  AND IsWorkDay = 1;

and then use the same as

DECLARE @table_DTR TABLE  
( USER_ID VARCHAR(10),
 log_date DATE,
 login_time TIME,
 logout_time TIME)
INSERT INTO @table_DTR VALUES ('USER1','11/21/2014','7:55:00','5:00:00')



select CASE d.IsWorkDay WHEN 0 THEN datename(dw,d.dt)  else  DTR.user_id END AS user_id,
 d.dt AS log_date,
 DTR.login_time,
 DTR.logout_time 
from  dbo.Calendar d
LEFT JOIN @table_DTR DTR ON d.dt = DTR.log_date AND  DTR.user_id = 'USER1'
WHERE d.dt  BETWEEN '11/21/2014' AND '11/26/2014'

For detailed explanation on pros of Calendar table you can refer here..

include weekends on sql query

3 Answers3