Number of weekdays between two dates - StartDate question

Question

I need to calculate the number of weekdays between a StartDate and EndDate. I use the following which works fine but my StartDate needs to be the 'TradeDate' in the table, and not a specific date set for the StartDate. How can I change the 'SET @StartDate' correctly to pick the 'TradeDate' for each record in the table?

Declare @StartDate DATEtime
Declare @EndDate DATEtime
SET
@StartDate = ? ? ?
SET
@EndDate = GETDATE()
SELECT
(DATEDIFF(dd, @StartDate, @EndDate) -1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -(
CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END
) -(
CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END
) as DaysOutstanding,
DealTicketReference,
PortfolioCode
from
tbl
where
StatusId = '1'
and Type = '0'

Yogesh Sharma · Accepted Answer · 2020-02-24T11:22:02.080

0

You can use SELECT statement :

SELECT (DATEDIFF(dd, TradeDate, @EndDate) -1) -
       (DATEDIFF(wk, TradeDate, @EndDate) * 2) -
       (CASE WHEN DATENAME(dw, TradeDate) = 'Sunday' 
             THEN 1 ELSE 0 
        END) -
       (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' 
             THEN 1 ELSE 0 
        END) as DaysOutstanding,
        DealTicketReference, PortfolioCode
from tbl
where StatusId = '1' and Type = '0';

If StatusId & Type has always numeric values then dont need to make literal string. Just pass StatusId = 1 & Type = 0

edited Feb 24 '20 at 11:22

answered Feb 24 '20 at 11:16

Yogesh Sharma

49,870
5
26
52

I tried that but it's not recognising TradeDate. Error: Invalid column name 'TradeDate' but TradeDate is definitely the correct value. Example: TradeDate 2020-02-04 00:00:00.000 – GLOBALJJ Feb 24 '20 at 11:56
@GLOBALJJ. . . That error says you don't have `TradeDate` in given table `tbl`. So, you make sure `TradeDate` is available in table. – Yogesh Sharma Feb 24 '20 at 12:03

Number of weekdays between two dates - StartDate question

1 Answers1