• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

Days Remaining Formula -- Excel vs MSRS

Does anyone have an easier way to get the below "3rd part" using MSRS ?
Maybe something like http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143019 ...

EXCEL
  ** =NETWORKDAYS(L1,L2,Holidays!A2:A40)
        ** L1 = 08/10/2012
        ** L2 = 12/28/2012
        ** Holidays sheet lists all holidays
-----------------------------------------------
MSRS

--1st part
select
(select MILESTONE_DATE
from PROJECT_MILESTONE_TAB
where PROJECT_ID = '282-090'
and MILESTONE_ID = '2')
- --2nd part
(select MILESTONE_DATE
from PROJECT_MILESTONE_TAB
where PROJECT_ID = '282-090'
and MILESTONE_ID = '1')
- --3rd part
(
select count(*)
from PUB_HOL_COMP_SCHED_DESC_TAB PHT
where PHT.PUB_HOL_COMP_DAY_TYPE = 'HOL'
and pht.ACCOUNT_DATE between
(select MILESTONE_DATE
from PROJECT_MILESTONE_TAB
where PROJECT_ID = '282-090'
and MILESTONE_ID = '1')
and
(select MILESTONE_DATE
from PROJECT_MILESTONE_TAB
where PROJECT_ID = '282-090'
and MILESTONE_ID = '2')
) as days

from dual
0
finance_teacher
Asked:
finance_teacher
1 Solution
 
mlmccCommented:
Can you change the holiday sheet to list all dates and label them as appropriate workday, weekend, holiday

select count(*)
from PUB_HOL_COMP_SCHED_DESC_TAB PHT
where PHT.PUB_HOL_COMP_DAY_TYPE <> 'HOL'
and pht.ACCOUNT_DATE between
(select MILESTONE_DATE
from PROJECT_MILESTONE_TAB
where PROJECT_ID = '282-090'
and MILESTONE_ID = '1')
and
(select MILESTONE_DATE
from PROJECT_MILESTONE_TAB
where PROJECT_ID = '282-090'
and MILESTONE_ID = '2')
) as days


mlmcc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now