Analytics

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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.

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.