CREATE TABLE Projects
(
id int identity primary key,
[ProjectName] varchar(14), [StartDate] datetime, [FinishDate] datetime)
;
INSERT INTO Projects
([ProjectName], [StartDate], [FinishDate])
VALUES
('Test Project 1', '2015-10-01 00:00:00', '2015-12-31 00:00:00'),
('Test Project 2', '2015-09-01 00:00:00', '2015-12-31 00:00:00'),
('Test Project 3', '2015-09-01 00:00:00', '2015-12-31 00:00:00'),
('Test Project 4', '2016-01-01 00:00:00', '2016-03-31 00:00:00')
;
CREATE TABLE Resources
(
id int identity primary key,
[Resource] varchar(7), [WeeklyHours] decimal(4,2))
;
INSERT INTO Resources
([Resource], [WeeklyHours])
VALUES
('Staff 1', 1.07),
('Staff 4', 1.07),
('Staff 2', 2.00),
('Staff 3', 1.07)
;
CREATE TABLE ProjectResources
([ProjectID] int, [ResourceID] int, [FTE] decimal(4,2), [AllocStart] date, [AllocEnd] Date)
;
INSERT INTO ProjectResources
([ProjectID], [ResourceID], [FTE])
VALUES
(1, 1, 1.0),
(1, 2, 0.5),
(2, 1, 1),
(2, 2, 1),
(2, 3, 1),
(3, 1, 1),
(3, 3, 1),
(3, 4, 1),
(4, 1, 0.5),
(4, 2, 0.5)
;
CREATE TABLE days (
PKDate date NOT NULL PRIMARY KEY CLUSTERED,
-- Years
calendar_year smallint,
-- Quarters
calendar_quarter tinyint,
calendar_quarter_desc varchar(10),
-- Months
calendar_month tinyint,
calendar_month_name_long varchar(30),
calendar_month_name_short varchar(10),
-- Weeks
calendar_week_in_year tinyint,
calendar_week_in_month tinyint,
-- Days
calendar_day_in_year smallint,
calendar_day_in_week tinyint, -- The first of the month
calendar_day_in_month tinyint,
mdy_name_long varchar(30),
mdy_name_long_with_suffix varchar(30),
day_name_long varchar(10),
day_name_short varchar(10),
-- Continuous Y/M/D, starts with the first day = 1 and keeps going. Used for various dateadd functions.
continuous_year tinyint,
continuous_quarter smallint,
continuous_month smallint,
continuous_week smallint,
continuous_day int,
-- Custom
description varchar(100),
is_weekend tinyint, -- Tinyint and not bit so you can add the 1's.
is_holiday tinyint, -- Tinyint and not bit so you can add the 1's.
is_workday tinyint, -- Tinyint and not bit so you can add the 1's.
is_event tinyint) -- Used to indicate any special event days.
GO
-- Create the table, with dates ranging from 2010 to 2020. Change to suit your needs.
Declare @dt_start date = '2015-01-01', @dt_end date = '2020-12-31', @total_days int, @i int = 0
SELECT @total_days = DATEDIFF(d, @dt_start, @dt_end)
WHILE @i <= @total_days
begin
INSERT INTO days (PKDate)
SELECT CAST(DATEADD(d, @i, @dt_start) as DATE)
SET @i = @i + 1
end
;
-- These values can be generated with single SQL Server functions
UPDATE days
SET
calendar_year = YEAR(PKDate),
calendar_quarter = DATEPART(q, PKDate),
calendar_month = DATEPART(m, PKDate),
calendar_week_in_year = DATEPART(WK, PKDate),
calendar_day_in_year = DATEPART(dy, PKDate),
calendar_day_in_week = DATEPART(Weekday, PKDate),
calendar_day_in_month = DATEPART(d, PKDate),
day_name_long = datename(weekday, PKDate)
-- These values need either logic, customization in functions, or customization based on client needs.
UPDATE days
SET
is_weekend = CASE DATEPART(weekday, PKDate) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END,
calendar_quarter_desc = 'Q' + CAST(calendar_quarter as char(1)) + ' ' + CAST(calendar_year as char(4)),
calendar_month_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_year as CHAR(4)),
mdy_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + ', ' + CAST(calendar_year as CHAR(4)),
day_name_short = LEFT(datename(weekday, PKDate),3)
-- Month name: The first three letters of the month.
UPDATE days
SET calendar_month_name_short = LEFT(DATENAME(month, PKDate),3), is_holiday = 0
;
record some dates as holidays which will be removed (along with weekends) from allocaton calculations
-- record "bank holidays" these are some UK dates from http://www.telegraph.co.uk/history/11622952/bank-holiday-facts.html
UPDATE days
SET is_holiday = 1
where PKDate IN (
'20150101','20150403','20150406','20150504','20150525','20150831','20151225','20151228'
,'20160101','20160325','20150328','20150502','20150530','20150829','20151226','20151227'
)
With the 4 tables and data, we can combine these to calculate forward load, which I have done per project, per resource , per month (& it could be done per week or quarter for example). The query I used:
select
p.id
, p.ProjectName
, r.Resource
, pr.fte
, d.calendar_year
, d.calendar_month
, format(max(coalesce(pr.AllocStart, p.StartDate)),'yyyy-MM-dd') AllocFrom
, format(max(coalesce(pr.AllocEnd, p.FinishDate)),'yyyy-MM-dd') AllocTo
, sum(8 * pr.FTE) AllocHrs
from ProjectResources pr
inner join Projects p on pr.projectid = p.id
inner join Resources r on pr.resourceid= r.id
inner join dbo.days d on d.pkdate between coalesce(pr.AllocStart, p.StartDate) and coalesce(pr.AllocEnd, p.FinishDate)
and d.is_weekend = 0 and d.is_holiday = 0
group by
p.id
, p.ProjectName
, r.Resource
, pr.fte
, d.calendar_year
, d.calendar_month
order by
p.id
, p.ProjectName
, r.Resource
, pr.fte
, d.calendar_year
, d.calendar_month
;
and the result of that query for the sample data is:
| id | ProjectName | Resource | fte | calendar_year | calendar_month | AllocHrs |
|----|----------------|----------|-----|---------------|----------------|----------|
| 1 | Test Project 1 | Staff 1 | 1 | 2015 | 10 | 176 |
| 1 | Test Project 1 | Staff 1 | 1 | 2015 | 11 | 168 |
| 1 | Test Project 1 | Staff 1 | 1 | 2015 | 12 | 168 |
| 1 | Test Project 1 | Staff 4 | 0.5 | 2015 | 10 | 88 |
| 1 | Test Project 1 | Staff 4 | 0.5 | 2015 | 11 | 84 |
| 1 | Test Project 1 | Staff 4 | 0.5 | 2015 | 12 | 84 |
| 2 | Test Project 2 | Staff 1 | 1 | 2015 | 9 | 176 |
| 2 | Test Project 2 | Staff 1 | 1 | 2015 | 10 | 176 |
| 2 | Test Project 2 | Staff 1 | 1 | 2015 | 11 | 168 |
| 2 | Test Project 2 | Staff 1 | 1 | 2015 | 12 | 168 |
| 2 | Test Project 2 | Staff 2 | 1 | 2015 | 9 | 176 |
| 2 | Test Project 2 | Staff 2 | 1 | 2015 | 10 | 176 |
| 2 | Test Project 2 | Staff 2 | 1 | 2015 | 11 | 168 |
| 2 | Test Project 2 | Staff 2 | 1 | 2015 | 12 | 168 |
| 2 | Test Project 2 | Staff 4 | 1 | 2015 | 9 | 176 |
| 2 | Test Project 2 | Staff 4 | 1 | 2015 | 10 | 176 |
| 2 | Test Project 2 | Staff 4 | 1 | 2015 | 11 | 168 |
| 2 | Test Project 2 | Staff 4 | 1 | 2015 | 12 | 168 |
| 3 | Test Project 3 | Staff 1 | 1 | 2015 | 9 | 176 |
| 3 | Test Project 3 | Staff 1 | 1 | 2015 | 10 | 176 |
| 3 | Test Project 3 | Staff 1 | 1 | 2015 | 11 | 168 |
| 3 | Test Project 3 | Staff 1 | 1 | 2015 | 12 | 168 |
| 3 | Test Project 3 | Staff 2 | 1 | 2015 | 9 | 176 |
| 3 | Test Project 3 | Staff 2 | 1 | 2015 | 10 | 176 |
| 3 | Test Project 3 | Staff 2 | 1 | 2015 | 11 | 168 |
| 3 | Test Project 3 | Staff 2 | 1 | 2015 | 12 | 168 |
| 3 | Test Project 3 | Staff 3 | 1 | 2015 | 9 | 176 |
| 3 | Test Project 3 | Staff 3 | 1 | 2015 | 10 | 176 |
| 3 | Test Project 3 | Staff 3 | 1 | 2015 | 11 | 168 |
| 3 | Test Project 3 | Staff 3 | 1 | 2015 | 12 | 168 |
| 4 | Test Project 4 | Staff 1 | 0.5 | 2016 | 1 | 80 |
| 4 | Test Project 4 | Staff 1 | 0.5 | 2016 | 2 | 84 |
| 4 | Test Project 4 | Staff 1 | 0.5 | 2016 | 3 | 88 |
| 4 | Test Project 4 | Staff 4 | 0.5 | 2016 | 1 | 80 |
| 4 | Test Project 4 | Staff 4 | 0.5 | 2016 | 2 | 84 |
| 4 | Test Project 4 | Staff 4 | 0.5 | 2016 | 3 | 88 |
& now that I have used the dbms to generate the numbers I could use a spreadsheet to transpose the data into monthly columns instead of rows for presentation and probably some graphs.ASKER
ASKER
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
TRUSTED BY
This site has several conceptual methods for you to consider. Please correct me if I'm wrong, but this is a large undertaking and the question covers a lot of ground. I am sending this link to give you ideas. Maybe it will help you get started and assist with generating specific questions, Maybe it will help you more easily see ways to do what you want to do. Then again, with all your excel experience, maybe this is old info.
I started out thinking, I'll ask what type of analysis is needed and so forth, but you may change your answers after reading the link. It covers functions like DAVERAGE, DCOUNT, DCOUNTA, creating histograms, and Progress S-curves. I wonder what you think of it.
http://www.toolboxforplanning.com/search/label/Excel%20Tutorial
By the time you review it, maybe someone who does a lot of this will be responding to you here.