include weekends on sql query

debugcn Published at Dev

Waelhi

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'

Sandeep

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]

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-3

Comments

0 comments

From Dev

Related Related

Article

include weekends on sql query

include weekends on sql query

How can I exclude weekends and holidays in SQL Server query

sql query to include null

to include a header into result of sQL query

Include zeros in SQL count query?

SQL Include zero rows in query

to include a header into result of sQL query

SQL query to DataTable to include headers

How to include IF ELSE in SQL Query

Exclude weekends from my query

SQL Query to find the past 3rd business day excluding holidays and weekends

Exclude / ignore weekends in Oracle SQL

How to include variable in a SQL query in C#

How to include only the last match in an SQL query

Generalize SQL query to include data for all Users

Generalize SQL query to include data for all Users

Include Search Parameters Custom Paging SQL Query

Modify an SQL query to include additional parameters

How do I exclude weekends in SQL?

or query with include

SQL Server Query: Using JOIN to include NULL values

Why does a SQL query with != 0 not include NULL values?

pentaho cde include/exclude where clause in sql query

How do I run a foreach against an array and include it in an SQL query?

SQL Server Include user function result into select query

SQL Server Query: Using JOIN to include NULL values

Activerecord: how to query using find_by_sql and include with a variable

MS Access SQL Query does not include specified expression as aggregated function

pentaho cde include/exclude where clause in sql query

SQL Server: how to include count in the select query with where clause?