Show only past 3 Business Days

Transact-SQL
21

Don't know how to do that
Plus, for this, really unnecessary I think
Since any data you have that contains dates is all that's needed here to test the codes.

Any sample data with a few dates of the following is all that's needed to test the date filters

5/1/2018
5/2/2018
5/3/2018
5/4/2018
5/5/2018
5/6/2018
5/7/2018

and apply this to the above date field

CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHistoryHeader.OrderDate) } IN (1 , 7) THEN dbo.DAYSADDNOWK(OrderDate , 1) ELSE DATEADD(dd , 0 , DATEDIFF(dd , 0 , dbo.SO_SalesOrderHistoryHeader.OrderDate)) END As BizDate

then apply the code bitsmed provided

22

if that is all that is needed then please provide that small sample data as DDL and DML?

23

Again, I don't know how to do that.

It would be so much faster if you have a table with 7 dates as mentioned

thank you

24

create table #SO_SalesOrderHeader
OrderDate Date()

insert into #SO_SalesOrderHeader
Values (5/1/2018, 5/2/2018, 5/3/2018, 5/4/2018, 5/5/2018, 5/6/2018, 5/7/208);

1 Like
25

did you test this in your version of SQL server?

26

Using your sample data (corrected to syntax that works):

create table #SO_SalesOrderHeader(OrderDate Date);

insert into #SO_SalesOrderHeader
 Values ('2018-05-01'),('2018-05-02'),('2018-05-03'),('2018-05-04'),('2018-05-04'),('2018-05-06'),('2018-05-07');

This:

select orderdate
      ,datename(weekday,orderdate) as dayname
  from #so_salesorderheader
 where orderdate>=dateadd(day
                         ,case datepart(weekday,current_timestamp)
                             when 1 then -4
                             when 5 then -3
                             when 6 then -3
                             when 7 then -3
                             else        -5
                          end
                         ,cast(current_timestamp as date)
                         )
   and orderdate<cast(current_timestamp as date)
;

gives me

orderdate   dayname
2018-05-03  Thursday
2018-05-04  Friday
2018-05-05  Saturday
2018-05-06  Sunday
2018-05-07  Monday

Is this what you want?

27

Thanks,

and against this, the code that produce past 3 business days

Today is 5/8/2018
so the code needs to produce
5/7/2018
5/4/2018
5/3/2018

since 5/5 and 5/6 are weekends ... not business days

28 
select orderdate
      ,datename(weekday,orderdate) as dayname
  from #so_salesorderheader
 where orderdate>=dateadd(day
                         ,case datepart(weekday,current_timestamp)
                             when 1 then -4
                             when 5 then -3
                             when 6 then -3
                             when 7 then -3
                             else        -5
                          end
                         ,cast(current_timestamp as date)
                         )
   and orderdate<cast(current_timestamp as date)
   and datepart(weekday,orderdate) not in (1,7)
;
1 Like
29

Perfect! I just had to figure out how to apply

thank you!

30

OK, I can't figure out how to change it for the last business week?

31

So, considering the following dates, which dates would you like to see?

create table #SO_SalesOrderHeader(OrderDate Date);

insert into #SO_SalesOrderHeader
 Values ('2018-04-17')
       ,('2018-04-18')
       ,('2018-04-19')
       ,('2018-04-20')
       ,('2018-04-21')
       ,('2018-04-22')
       ,('2018-04-23')
       ,('2018-04-24')
       ,('2018-04-25')
       ,('2018-04-26')
       ,('2018-04-27')
       ,('2018-04-28')
       ,('2018-04-29')
       ,('2018-04-30')
       ,('2018-05-01')
       ,('2018-05-02')
       ,('2018-05-03')
       ,('2018-05-04')
       ,('2018-05-05')
       ,('2018-05-06')
       ,('2018-05-07')
       ,('2018-05-08')
       ,('2018-05-09')
;
32

Always yesterday + 4 days ago since there are 5 business days in a week

5/8
5/7
5/4
5/3
5/2

33 
select orderdate
      ,datename(weekday,orderdate) as dayname
  from #so_salesorderheader
 where orderdate>=dateadd(day
                         ,case datepart(weekday,current_timestamp)
                             when 1 then -6
                             when 7 then -5
                             else        -7
                          end
                         ,cast(current_timestamp as date)
                         )
   and orderdate<cast(current_timestamp as date)
   and datepart(weekday,orderdate) not in (1,7)
;
1 Like
34

Thank you so much!

A lot to learn

Thanks again!