CODEWITHSUNDEEP
sqlsql-server
db702
db702

Reputation: 568

Roll weekend counts into monday counts

I have a query like this:

select date, count(*)
from inflow 
where date >= dateadd(year, -2, getdate())
group by date
order by date

I need to exclude Saturday and Sunday dates, and instead add their counts into the following Monday. What would be the best way to do this? Do I need to exclude Saturday, Sunday, and Mondays, then add them on with a join to a different query? The query above is a simplified version, this is a relatively big query, so I need to keep efficiency in mind.

Upvotes: 1

Views: 138

Answers (2)

R. Anderson
R. Anderson

Reputation: 313

You state for performance, however without knowing the full picture it's quite hard to understand how to optimise the query.

While I've been working on this, I noticed Gordon Linoff's answer, however I'll continue to write my version up as well, we both following the same path, but get to the answer a little different. 

WITH DateData (date, datetoapply)
AS
(
    SELECT 
        [date],
        CASE DATEPART(w, [date])
            WHEN 5 THEN DATEADD(d, 2, [date])
            WHEN 6 THEN DATEADD(d, 1, [date])
            ELSE date
        END as 'datetoapply'
    FROM inflow 
    WHERE [date] >= dateadd(year, -2, getdate())
)
SELECT datetoapply, count(*)
FROM DateData
GROUP BY datetoapply
ORDER BY datetoapply

While I could not get Gordon's query working as expected, I can confirm that "DATEPART(w, [date])" performs much better than "DATENAME(weekday, [date])", which if replaced in the query above increases the server processing time from 87ms to 181ms based on a table populated with 10k rows in Azure.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Well, this is a somewhat brute-force approach:

select date,
       (case when datename(weekday, date) = 'Monday')
             then cnt + cnt1 + cnt2
             else cnt
        end) as cnt
from (select date, count(*) as cnt,
             lag(count(*), 1, 0) over (order by date) as prev_cnt,
             lag(count(*), 2, 0) over (order by date) as prev_cnt2
      from inflow 
      where date >= dateadd(year, -2, getdate())
      group by date
     ) d
where datename(weekday, date) not in ('Saturday', 'Sunday')
order by date;

Note: This is assuming English-language settings so the datename() logic works.

An alternative method without subqueries;

select v.dte, count(*) as cnt
from inflow i cross apply
      (values (case when datename(weekday, i.date) = 'Saturday'
                    then dateadd(day, 2, i.date)
                    when datename(weekday, i.date) = 'Sunday'
                    then dateadd(day, 1, 9.date)
                    else i.date
               end)
      ) v.dte
where i.date >= dateadd(year, -2, getdate())
group by v.dte
order by date;

Upvotes: 1

Related Questions