Please help, I have a below sample data. How to find week days "Tuesday" and count between two days.

CREATE TABLE EmpDetails1 (id INT, name VARCHAR(25),startdate datetime,enddate datetime) 
INSERT INTO EmpDetails1 VALUES(1,'TEST','01/01/2016','01/10/2016');
INSERT INTO EmpDetails1 VALUES(2,'TEST','01/01/2016','01/25/2016');

id name startdate enddate
1 Test 1 1/1/16 1/10/16
2 Test 2 1/1/16 1/25/16

output:

date   count
1/5/16 1
1/12/16 3

I have tried with below query but not getting correct result

SELECT name, 
DATENAME(WEEKDAY, startdate) as w1,
DATENAME(WEEKDAY, enddate) as w2,
startdate,enddate, count(*) OVER(PARTITION BY startdate,enddate) AS CountOfOrders  from EmpDetails1 group by  startdate , enddate,name

CodePudding user response：

As mentioned already, you need a calendar. You can generate one dynamically but far better to create a static one since it serves so many useful purposes. This is but one of many discussions about how to generate one.

A calendar is just a special form of a tally table and I use Itzik's discussion as a basis for that. Both of these concepts are things you need to understand in most SQL query writing environments.

Once you have a calendar, you simply join your data table to the calendar and filter as needed. I did not understand exactly what you were trying to accomplish so I simply create the set of rows for "Tuesday".

declare @EmpDetails1 table (id int, name varchar(20), startdate date, enddate date);

insert @EmpDetails1 (id, name, startdate, enddate) values 
(1, 'Test 1', '20210101', '20210110'), 
(2, 'Test 2', '20210116', '20210126');

select emp.*, cal.*, datename(weekday, cal.caldt) as [day of week(eng)]
  from @EmpDetails1 as emp 
 inner join calendar as cal 
on cal.caldt between emp.startdate and emp.enddate
   and datename(weekday, cal.caldt) = 'Tuesday'
order by emp.id, cal.caldt
;

Fiddle here to demonstrate. I must highlight the lazy usage of * as the column list but this is just a simple demo. Production code should generally always specify the columns needed completely.

If you examine the calendar table discussion, you will see that the day of week can be easily added to the table - it will never change. This will avoid the effort to calculate it in the query.

CodePudding user response：

Please check below for my solution. I tried to solve it using stored procedure in mysql; and I think you can apply the same in ms sql server.

My SQL Code:

DELIMITER //
    CREATE PROCEDURE tuesdayCount(IN id_number INT)
        BEGIN
            DECLARE S_Date DATETIME;
            DECLARE E_Date DATETIME;
            SET S_Date = (SELECT startdate FROM EmpDetails1 WHERE id = id_number);
            SET E_Date = (SELECT enddate FROM EmpDetails1 WHERE id = id_number);
            WHILE (S_Date <= E_Date) DO
                IF (DATE_FORMAT(S_Date, '%a') = 'Tue') THEN
                    INSERT INTO TuesdayDates VALUES(S_Date, 1);
                END IF;
                SET S_Date = DATE_ADD(S_Date,INTERVAL 1 DAY);
            END WHILE;
        
        END //
DELIMITER ;

CALL tuesdayCount(1);
OUTPUT
[Result 01][1]

CALL tuesdayCount(2);

OUTPUT
[Result 02][2]

    My TuesdayDates Table Definition:
CREATE TABLE TuesdayDates (date DATETIME, COUNT int);

Code Steps:

1. First line I created a procedure named tuesdayCount with an input parameter(id_number)

2. Then I declared 2 variables (S_Date and E_Date) with DATETIME data type, then set them equal to startdate and endnote column values in the first row.(Please take note of id_number sp parameter in the where clause.)

3. Then I defined a while loop and if_ test to ensure the date is really a Tuesday. If that's so, then I inserted the date value(S_Date) into TuesdayDates table which I created myself beforehand to put the result set there. (maybe not so logical; but I did it anyway.)

4. After defining the sp proc, I called my functions with id_number parameter which is in fact the row number of your data set (EmpDetails1 Table)

Not a perfect solution :) but I hope It helps somehow.