SQL Server - Calculate next five business days

188 Views Asked by At

I have a requirement where I have to calculate next 5 business days from today date for calculating projected open sales.

I have to apply this as a filter in my sql query. I am attaching my query below which includes weekends. It would be helpful if some one suggests me a solution to find out the next 5 business days excluding weekends (Saturday and Sunday)

select 
s.salesid,
l.itemid,
l.SHIPPINGDATEREQUESTED as 'Ship Date', 
Case when not l.salesqty  = 0 then (l.LINEAMOUNT/l.SALESQTY) * l.REMAINSALESPHYSICAL else 0 end as 'Projected Open Sales', 

from dbo.salestable s 
inner join dbo.salesline l
on s.salesid = l.salesid and s.dataareaid = l.dataareaid and s.partition = l.partition
inner join dbo.inventtable i on l.itemid = i.itemid and l.DATAAREAID = i.DATAAREAID and l.partition = i.PARTITION
inner join dbo.ECORESTRACKINGDIMENSIONGROUPITEM ti on l.itemid = ti.itemid and l.DATAAREAID = ti.ITEMDATAAREAID and l.PARTITION = ti.PARTITION
inner join dbo.ECORESTRACKINGDIMENSIONGROUP g on ti.TRACKINGDIMENSIONGROUP = g.recid

where  
      s.SHIPPINGDATEREQUESTED > = getdate() and
      s.SHIPPINGDATEREQUESTED <= getdate() + 5 and
      and s.salestype in (0,3) 
      and l.salesstatus in (0,1) 
      and not l.salesqty = 0
      order by s.SHIPPINGDATEREQUESTED asc
0

There are 0 best solutions below