Analytics

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-02-2012
11:53 PM

09-02-2012
11:53 PM

Hi

I have a table with a list of delivery dates for orders that have failed to go out. I need to report on how many working days these are passed that delivery date.

For example I have a table like this:

OrderNo, RequestedDeliveryDate

Order1 , 2012-08-27

Order2 , 2012-08-31

So if I was to run this today (2012-09-03) I need an output of:

OrderNo, RequestedDeliveryDate,DaysLate

Order1 , 2012-08-27 , 5

Order2 , 2012-08-31 , 1

Any help or direction on how to solve this will be a fantastic.

Thanks

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-03-2012
01:51 AM

09-03-2012
01:51 AM

How do you define "working days"?

Always Mo - Fr, Mo-Sa, exclude Holidays?

If you need to consider Holidays you would need a kalendar table to maintain these.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-05-2012
05:38 PM

09-05-2012
05:38 PM

Hi ulrich,

Working days a Monday to Friday. I do not need to consider holidays.

Thanks.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-06-2012
05:40 AM

09-06-2012
05:40 AM

so let's assume the following table

create volatile table vt_dates

as

(

select c1.calendar_Date from_dt, c2.calendar_date to_dt

from sys_calendar.calendar c1

cross join

sys_calendar.calendar c2

where c1.calendar_Date between current_date - 30 and current_Date

and c2.calendar_Date between current_date - 30 and current_Date

and c2.calendar_date > c1.calendar_date

) with data primary index (from_dt)

on commit preserve rows;

You have two choices:

1. simple logic but join on calendar table

select from_dt,

to_dt,

sum(case when c.day_of_week in (1,7) then 0 else 1 end) - (case when sum(case when c.day_of_week in (1,7) then 0 else 1 end) = 0 then 0 else 1 end) as count_of_weekdays

from vt_dates v

join sys_calendar.calendar c

on c.calendar_Date between v.from_dt and v.to_dt

group by 1,2

order by 1,2

2. direct calculation with not too obvious formula

select from_dt,

to_dt,

case when day_of_week(from_dt) = 7 then 2

when day_of_week(from_dt) = 1 then 1

else 0

end + from_dt as next_work_dt,

case when day_of_week(to_dt) = 7 then -1

when day_of_week(to_dt) = 1 then -2

else 0

end + to_dt as prev_work_dt,

(prev_work_dt - next_work_dt)+1 as diff,

(case when diff mod 7 = 0 then 1 else 0 end) as correct_fw,

(diff - diff mod 7) / 7 as full_week,

case when diff < 0

then 0

else diff - (full_week*2) - 1

end +

case when day_of_week(prev_work_dt) < day_of_week(next_work_dt)

then -2

else 0

end +

case when diff mod 7 = 0 then 2 else 0 end

as num_of_weekdays

from vt_dates v

order by 1,2

If your table is big and the number of workdays can also be big it might be worth to consider solution two.

A SQL UDF might be usefull to have a central logic for it - even if the UDF code becomes more unreadable due to repeating cases...

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-06-2012
06:15 PM

09-06-2012
06:15 PM

Hi ulrich,

Solution 1 works wonderfully.

Thanks so much this has solved somthing that has stumpt me for a while now.