CODEWITHSUNDEEP
sqlsql-servert-sql
Steve
Steve

Reputation: 93

t-SQL Grouping by Day and Hours

I need to determine the number of sales for the week and weekend but the weekend is to be counted as between 6pm Friday and 9am Monday.

For instance, if I have the below data:

2017-02-09 14:00
2017-02-09 19:00
2017-02-10 17:15
2017-02-10 18:22
2017-02-11 11:00
2017-02-11 16:00
2017-02-12 19:30
2017-02-13 08:00
2017-02-14 14:00

I would get the following:

Weekday: 4
Weekend: 5

Its fairly easy to get the sales per day using something like:

select count(*) as total, dateadd(DAY,0, datediff(day,0, created) as created
from sales
group by dateadd(DAY,0, datediff(day,0, created))

But I cant think how to combine the hours and days to get the information I require.

Upvotes: 0

Views: 96

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

This is just a complicated case statement. You can do this using outer apply and use the value for aggregation:

select weekpart, count(*)
from sales s outer apply
     (values (case when datename(dw, created) in ('Tuesday', 'Wednesday', 'Thursday') then 'Weekday'
                   when datename(dw, created) = 'Monday' and 
                        datepart(hh, created) >= 9
                   then 'Weekday'
                   when datename(dw, created) = 'Friday' and 
                        datepart(hh, created) < 18
                   then 'Weekday'
                   else 'Weekend'
               end)
      ) v(weekpart)
group by v.weekpart;

This intentionally uses datename() for the weekdays. The day-of-week is affected by internationalization settings. All English-speaking countries have the same names for the week days, but local conventions may affect the date when the week starts. I also figure that for a non-English setting, the code is pretty clear as to why it won't work -- in a way that comparing to "1" is not.

Upvotes: 2

devoh
devoh

Reputation: 897

Try something like that :

SELECT week_or_weekend, count(*)
FROM
(
    SELECT 1 as cnt, CASE WHEN 
    DATEPART(DW, created) in (7,1)
    OR
    (DATEPART(DW, created) 6 AND DATEPART(hour, created) >= 18)
    OR
    (DATEPART(DW, created) 2 AND DATEPART(hour, created) < 9)
    THEN 'WEEKEND'
    ELSE 'WEEK'
    END week_or_weekend 
    FROM sales
) q
GROUP BY week_or_weekend

Explanation: DW = 7 stands for Saturday, 1 for Sunday, 6 for Friday, 2 for Monday I add 1 as cnt for clarification by it's useless unless you add a distinct in the sub-query. You could do it in 1 query but you would have to repeat twice the big 'case when end' statement.

Upvotes: 1

Ofir Winegarten
Ofir Winegarten

Reputation: 9365

with CTE as
(
select created,
  case when 
   (datepart(dw,[created]) = 6 and  datepart(hh,created) >= 18) or
   (datepart(dw,created)=7 or datepart(dw,created)=1) or 
   (datepart(dw,[created]) = 2 and  datepart(hh,created) <= 9)
   then 'Weekend'
  else 'Weekday' end as weekType
 from Table1)
 select weekType, count(1)
 from CTE
 group by weekType

Check it here: http://sqlfiddle.com/#!6/4fbb0/8

Upvotes: 1

JohnHC
JohnHC

Reputation: 11195

This will get you started:

with CTE as
(
select dateadd(hh, datepart(hh,created), dateadd(dd,0, datediff(dd,0,created))) as created_dayhour, 
       someothercolumn, 
       datepart(dw,created) as create_day, 
       datepart(hh, created) as create_hour
from sales
)
select created_dayhour, count(someothercolumn)
from CTE
group by created_dayhour

Upvotes: 0

Related Questions