CODEWITHSUNDEEP
sqlsql-serverdatabase
Brandon Brown
Brandon Brown

Reputation: 29

SQL combine weekend dates to one total

I am trying to combine weekend dates by using datename.

TABLE

So I use DATENAME and convert the dates to the day of the week.

Then I can count them to get it to look like this

But Im unsure how to group these so it looks like

Upvotes: 0

Views: 688

Answers (3)

George Joseph
George Joseph

Reputation: 5922

You would group by the datename(weekday,) to get you the necessary output.

create table t(date_of_hire date);

insert into t values('2018-11-25');
insert into t values('2018-11-24');
insert into t values('2018-11-25');

select datename(weekday,date_of_hire) as day_of_week,count(*) as cnt
  from t
  group by datename(weekday,date_of_hire)

Upvotes: 1

forpas
forpas

Reputation: 164064

To get the total weekend days

SELECT COUNT(*) as totalweekdays FROM tablename 
WHERE 
DATENAME(weekday, datecolumn) = 'Saturday'   
OR
DATENAME(weekday, datecolumn) = 'Sunday'

Upvotes: 0

SteveB
SteveB

Reputation: 799

Here's a couple of ways you can do it. You will notice the COUNT version you have to return NULL so the COUNT() function won't count it. You can also use SUM(). There are other ways to do it as well.

DECLARE @x TABLE (mydate DATE);

INSERT INTO @x SELECT '11-25-2018'; --Sunday
INSERT INTO @x SELECT '11-24-2018'; --Saturday
INSERT INTO @x SELECT '11-25-2018'; --Sunday
INSERT INTO @x SELECT '11-23-2018'; --Friday

SELECT count(CASE WHEN DATEPART(dw, mydate) BETWEEN 2 and 6 THEN NULL ELSE 1 END ) AS CountOfWeekendDays
    ,sum(CASE WHEN DATEPART(dw, mydate) BETWEEN 2 and 6 THEN 1 ELSE 0 END) AS SumOfWeekDays
    ,sum(CASE WHEN DATEPART(dw, mydate) BETWEEN 2 and 6 THEN 0 ELSE 1 END) AS SumOfWeekendDays
FROM @x

Upvotes: 2

Related Questions