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