-1

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
Shakeer Hussain
  • 2,230
  • 7
  • 29
  • 52
  • 1
    `JOIN` to your Calendar Table, and `COUNT` the number of Tuesdays? – Thom A Jul 05 '22 at 14:38
  • @Larnu How to find Tuesday is present in between given two days? – Shakeer Hussain Jul 05 '22 at 14:42
  • `YourCalendarTable.CalendarDayName = 'Tuesday'` or `YourCalendarTable.CalendarDayNumber = 2` (Assuming Tuesday is day `2` in your Calendar Table.) – Thom A Jul 05 '22 at 14:45
  • 1
    FYI, SQL Server 2012 isn't much better [than 2008]; it has 7 days of (extended) support left. – Thom A Jul 05 '22 at 14:46
  • 1
    You do have a [calendar table](https://www.sqlservercentral.com/steps/calendar-tables)? – Sean Lange Jul 05 '22 at 14:48
  • 1
    Your output seems a little off to me. In 2016 the number of Tuesdays between January 1 and January 10 is only 1. – Sean Lange Jul 05 '22 at 15:05
  • SeanLange There are more records. I just added two sample records – Shakeer Hussain Jul 05 '22 at 15:19
  • 1
    That doesn't explain the data though, Shakeer . As @SeanLange said, there is only 1 Tuesday between 01 January 2016 and 10 January 2016, and that's 05 January 2016; so why do have a value of `2` for `count`? Like wise, between 01 January and 25 January 2016 there are only **3** Tuesdays, not 4. – Thom A Jul 05 '22 at 15:32
  • And why is the date for the second row in your data the second Tuesday that is found in the date range? There are more questions about what you want here than there are answers. – Sean Lange Jul 05 '22 at 15:37
  • I updated sample result with correct count – Shakeer Hussain Jul 05 '22 at 15:45
  • Why is the date for the second row January 12? You need to explain some of this stuff so others can help you. How do you decide what date to return in the output? – Sean Lange Jul 05 '22 at 15:51

2 Answers2

1

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.

SMor
  • 2,830
  • 4
  • 11
  • 14
  • I should have mentioned I changed the sample data to 2021 to match my existing calendar logic. – SMor Jul 05 '22 at 17:42
-1

T-SQL Code:

CREATE PROCEDURE tuesdayCount @id_number INT AS
    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
            IF (FORMAT(@S_Date, '%a') = 'Tue')
                INSERT INTO TuesdayDates VALUES(@S_Date, 1)
            SET @S_Date = DATEADD(DAY,1, @S_Date)
    END

EXECUTE tuesdayCount @id_number=1;
EXECUTE tuesdayCount @id_number=2;

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.

Ozan Sen
  • 2,477
  • 2
  • 4
  • 16