Business Day Calculation Universe Level

[Former Member]

Hi,

Need suggestion on the following situations

We have two dates a & b a is the create time and b is the load time. We have to calculate business days between these two dates.

The Business day Indicator ( will be required to validate business holiday ) is present in another table called calendar.

The calendar tables contains date and whether the date is a business day or not.

In order to calculate the BD , we will have to join the first table With a and b date columns to the calendar table.

Issue is if I Join the calendar date with a then it will give Only whther the date a was a business day or not , If  I join b with calendar date it will only give b date was a business day or not.

Need suggestion On this as to how to calculate BDays between two dates whereras the business day indicator is in another table

[Former Member · Accepted solution]

Database is Teradata :

Table A .                                                                Calendar Table

Material ID

Entry date- 15^th May                                        Date- will become entry/Load date which I will Join

Load date- 18th May                                        Bday Indicator = Y Or N .

  So basically we require Material ID with business day count between there Entry and Load date. One more thing is that there are multiple entries to entry date on which I have to take the latest one

[Former Member · Accepted solution]

Hi,

Simply drag your table A and calendar on universe

Now create a complex join between two tables using between operator.

now please check the below query

SELECT

DISTINCT A.MATERIAL_ID,A.ENTRY_DATE,A.LOAD_DATE,

SUM(CASE WHEN HOLIDAY = 'Y' THEN 1 ELSE 0 END),

max(A.LOAD_DATE - A.ENTRY_DATE) FROM

A,

calendar table H

WHERE  H.DATE1 BETWEEN A.F_DATE  AND A.TO_DATE

GROUP BY A.ENTRY_DATE,A.LOAD_DATE;

Where A table is your table having material ID,entry_Date and load_date

& in calendar table you have Holiday(flag) & date1 column.

As in query  I have applied between condition on universe between both the table  & create objects as follows

total_days = max(A.LOAD_DATE - A.ENTRY_DATE)

holidays = SUM(CASE WHEN HOLIDAY = 'Y' THEN 1 ELSE 0 END)

Just drag all the objects on query and run the report

Now for getting materail ID with atest entry date you have to create extra variable on report like

rank_materail = rank(MATERIAL_ID;ENTRY_DATE)

that will get the rank for each materail ID and materail ID having maximum entry date will get rank = 1

now apply report level filter where rank_materail = 1

I hope this will help.

[Former Member · Accepted solution]

Hi Swapnil,

I did the same for One table, now my requirement is to do that for one more date.

I have created two tables for calendar and Joined them to the main table.

Problem is  Individually they are coming correct,

But if i take them together the calculation come more then what is expected.

We need  to calculate days based on different parameters.

For first the entry Point should be entry date to load date.

For second some other date to load date

Need to calculate days

Abbreviations

BDIND= Business Day indicator (Stored in calendar )

1.Entry to load date

2.Change date to load date  

Created two tables for calendar and Joined them to the main table.

The Sql that Comes in webi is below

SELECT

A, B, C, D,E, F

  SUM(Case When Calendar1.BDIND ='Y' then 1 Else 0 END),

  SUM(Case When Calendar2.BDIND ='Y' then 1 Else 0 END)

FROM

  CALENDAR_MAIN  Calendar1 RIGHT OUTER JOIN   Policy ON (Calendar1.calendar_date >= policy.Enter_time  AND Calendar1.calendar_date < policy.createtime)

   RIGHT OUTER JOIN CALENDAR_MAIN  calendar2 ON (calendar2.calendar_date >= Policy.change_time  AND calendar2.calendar_date < Policy.createtime)

WHERE

  (

   Policy.createtime  IN  ( {d '2011-05-01'}  )

   AND

   Policy.DESC  IN  ( 'A',' B'  )

  )

GROUP BY

  1,

  2,

  3,

  4,

  5,

  6,

  7,

[Former Member]

What environment are you on? SQL Server, Oracle, Teradata, other?

[Former Member]

Can you please show us some sample data in three tables and then explain what your require.

It would be great help for us if you provide the same

[rakeshkumar_bhure]

Hi Kultar,

You can edit the join condition in IDT, make a join with date a to calendar date, then edit it like - calendar date between date a and Date b.

Alternatively, you can create a derived table by writing appropriate SQL including above join.

Hope this would help you.

-Rakesh