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)

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'

Answer 1

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]

Answer 2

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.

Answer 3

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..