Solved

# Days Remaining Formula -- Excel vs MSRS

Posted on 2012-08-21
526 Views
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
Question by:finance_teacher
1 Comment

LVL 100

Accepted Solution

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.