How to extract the total number of work days in a month?

A

Alexey Fedorov

I am creating a report using Reporting Services. The report should contain a
list of employees and the amount of work done during a month (Actual work)
for each employee as well as the total number of work days in the month. As
all information should be extracted from Project Server database, I need to
create stored procedures to do so. All managers of the company use Project
Professional to access Project Server database.

So far I have not had any problems with the list of employees and actual
work done during a month. But I encountered a problem with extracting the
total number of work days in a month. Obviously, to calculate it, I need to
subtract the number of non-work days from the total number of days in a
month. Project Professional displays such information in the Calendar, which
means that the data can be extracted from the Project Server database, but I
can not find where the data is stored and how it can be consolidated. I have
already examined the docs carefully and tried to identify the query with SQL
Profiler, but without success.

Could you please help me to extract the total number of work days in a
month?
 
M

Mike Glen

Hi Alexey ,

Try posting on the server newsgroup. Please see FAQ Item: 24. Project
Newsgroups. FAQs, companion products and other useful Project information
can be seen at this web address: http://project.mvps.org/faqs.htm

Mike Glen
Project MVP
 
A

Alexey Fedorov

Hi Mike,
I have already posted my message on the server newsgroup.

Thanks,
Alexey
 
E

Ed Morrison

Alexey,
Calendar data is first stored in a binary field in the database. However,
after a project is opened and saved, the msp_calendar and msp_calendar_data
tables are populated. Provided you are not making frequent changes to any
of the calendars, you could use these tables.

--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com
FAQ - http://www.projectserverexperts.com
 
A

Alexey Fedorov

Hi Ed,


I can see that the MSP_CALENDAR_DATA tables stores information on work and
non work days and exceptions. If you view the contents of this table in the
Project Server database, you will see that:


Á) Any exceptions (public holidays and holidays moved to other dates) are
stored as periods (from CD_FROM_DATE to CD_TO_DATE), the field CD_WORKING
shows if the period is work (1) or non-work (0) time, the files
CD_DAY_OR_EXCEPTION is always 0 for exceptions. Each exception is
represented by one string.


Â) Data on work and non-work (week-end) days are stored as follows:
CD_DAY_OR_EXCEPTION = 1, 2, 3... (1- Sunday, 2- Monday, 3- Tuesday etc.),
the field CD_WORKING shows if the day is a work (1) or non-work (0) one, the
fields CD_FROM_DATE and CD_TO_DATE are NULL; The fields CD_FROM_TIME1-5 and
CD_TO_TIME1-5 contain the start and end time of periods within a day (for
example, for a work day, work periods are stored: from 8.00 a.m. to 12.00
a.m. and from 01.00 p.m. to 05.00 p.m.). That means that the system stores
information on whether a day of a week is work or non-work one (as opposed
to a certain date being a work or non-work day).


Consequently, we can count the number of exceptions during a time period, as
we the start and end dates of exceptions are stored. But how can we count
the number of weekend days in a period, if we use only the tables
MSP_CALENDARS and MSP_CALENDAR_DATA? I can not find the link between date
and day of week in these tables, but I need to know which dates are
weekends.


Thank you very much in advance,

Alexey
 
Top