x

working days betwen 2 different date fields

i am struggling to get the number of working days between 2 separate date fields and need some help please. i am trying to create a report that states if we are responding to letters received within 2 days. i have created a working day calendar but get it to work out the working days between letter received and letter respond as they are 2 separate date fields. please help Regards

more ▼

asked Apr 08, 2010 at 07:33 AM in Default

avatar image

Jams Lawrence
13 1 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

DECLARE @WorkingDays TABLE([day] [datetime]) DECLARE @Response TABLE([ResponseID] [int],[Received] [datetime],[Sent] [datetime]) INSERT @WORKINGDAYS SELECT '2010-04-01 00:00:00.000' UNION SELECT '2010-04-02 00:00:00.000' UNION SELECT '2010-04-05 00:00:00.000' UNION SELECT '2010-04-06 00:00:00.000'

INSERT @Response
SELECT '1','2010-04-02 00:00:00.000','2010-04-05 00:00:00.000' UNION
SELECT '2','2010-04-05 00:00:00.000','2010-04-05 00:00:00.000' UNION
SELECT '3','2010-04-05 00:00:00.000','2010-04-06 00:00:00.000' UNION
SELECT '4','2010-04-01 00:00:00.000','2010-04-06 00:00:00.000'

SELECT DISTINCT ResponseID, COUNT(ResponseID) OVER (PARTITION BY RESPONSEID) [DAYS TO RESPOND]
FROM @RESPONSE R
JOIN @WORKINGDAYS WD ON (WD.[day] BETWEEN R.Received AND R.Sent)

more ▼

answered Apr 08, 2010 at 08:39 AM

avatar image

Scot Hauder
6.5k 13 16 22

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1095

asked: Apr 08, 2010 at 07:33 AM

Seen: 483 times

Last Updated: Apr 08, 2010 at 07:33 AM

Copyright 2018 Redgate Software. Privacy Policy