Author Message

I have a dB of weekly activities that an employee works on. Their percent of
time is being store for each project by a weekending friday date. All time =
100% for each week.

I have to create reports on this data and here is what my company wants to
see.

1) for each department what the percentage breakdown is by project for a
given span of weeks
2) accross all departments what the percentage breakdown is by project for a
given span of weeks
3) for each department by product the percentage breakdown for a given span
of weeks
4) accross all department by product the percentage breakdown for a given
span of weeks

I have been working on this for a few days now. I can get a single week for
a single dept to equal 100%.  What makes this tricky is when employees leave
or are added to depts. This is why the department id is stored in the
activities table below.  This way each activities really belongs to a
specific dept.  I have found what I need to do for example one above is to
sum up each project for a particular week and divide it by the num of
employees to get a departmental percent of time spent on each project. Then
on top of that if they need to span weeks I need to sum up each project from
there and divide by the number of weeks. This is the only way I have been
able to get the totals to = 100%.

Thanks to all you gurus that'll help to take a shot at this one. Maybe I am
missing something simple.  Joe C. are you out there?

Here is the db scheme I have to work with and FYI it cannot be change short
term, I am open to hear suggestions as to a better layout long term :

/****** Object:  Table dbo.departments    Script Date: 4/19/2001 10:45:25 AM
******/
CREATE TABLE "dbo"."departments" (
"ID" "int" IDENTITY (1, 1) NOT NULL ,
"DeptNum" varchar (10) NOT NULL ,
CONSTRAINT "PK___4__10" PRIMARY KEY  CLUSTERED
(
"ID"
)
)
GO

/****** Object:  Table dbo.products    Script Date: 4/19/2001 10:45:25 AM
******/
CREATE TABLE "dbo"."products" (
"ID" "int" IDENTITY (1, 1) NOT NULL ,
"Name" varchar (50) NOT NULL ,
CONSTRAINT "PK___1__10" PRIMARY KEY  CLUSTERED
(
"ID"
)
)
GO

/****** Object:  Table dbo.projects    Script Date: 4/19/2001 10:45:25 AM
******/
CREATE TABLE "dbo"."projects" (
"ID" "int" IDENTITY (1, 1) NOT NULL ,
"Name" varchar (100) NOT NULL ,
"Code" varchar (50) NOT NULL ,
"ProductID" "int" NOT NULL ,
CONSTRAINT "PK___2__10" PRIMARY KEY  CLUSTERED
(
"ID"
),
CONSTRAINT "FK_projects_2__10" FOREIGN KEY
(
"ProductID"
) REFERENCES "dbo"."products" (
"ID"
)
)
GO

/****** Object:  Table dbo.activities    Script Date: 4/19/2001 10:45:25 AM
******/
CREATE TABLE "dbo"."activities" (
"ID" "int" IDENTITY (1, 1) NOT NULL ,
"EmpID" varchar (10) NOT NULL ,
"WeekEnding" "datetime" NOT NULL ,
"Percent" "int" NOT NULL ,
"ProjectID" "int" NOT NULL ,
"DepartmentID" "int" NULL ,
CONSTRAINT "PK___3__10" PRIMARY KEY  CLUSTERED
(
"ID"
),
CONSTRAINT "FK_activities_1__10" FOREIGN KEY
(
"ProjectID"
) REFERENCES "dbo"."projects" (
"ID"
),
CONSTRAINT "FK_activities_1__15" FOREIGN KEY
(
"DepartmentID"
) REFERENCES "dbo"."departments" (
"ID"
)
)
GO

/****** Object:  Table dbo.departments_projects    Script Date: 4/19/2001
10:45:26 AM ******/
CREATE TABLE "dbo"."departments_projects" (
"DepartmentID" "int" NOT NULL ,
"ProjectID" "int" NOT NULL ,
CONSTRAINT "FK_departments_projects_1__10" FOREIGN KEY
(
"DepartmentID"
) REFERENCES "dbo"."departments" (
"ID"
),
CONSTRAINT "FK_departments_projects_2__10" FOREIGN KEY
(
"ProjectID"
) REFERENCES "dbo"."projects" (
"ID"
)
)
GO

Mon, 06 Oct 2003 23:04:07 GMT

First, create a view with Weeks, employees and totaltime
then another one on employees, weeks projects and totaltimeperproject.
another wiev is the one on the top of other 2. (maybe a tored procedure
then?)

Quote:
> I have a dB of weekly activities that an employee works on. Their percent
of
> time is being store for each project by a weekending friday date. All time
=
> 100% for each week.

> I have to create reports on this data and here is what my company wants to
> see.

> 1) for each department what the percentage breakdown is by project for a
> given span of weeks
> 2) accross all departments what the percentage breakdown is by project for
a
> given span of weeks
> 3) for each department by product the percentage breakdown for a given
span
> of weeks
> 4) accross all department by product the percentage breakdown for a given
> span of weeks

> I have been working on this for a few days now. I can get a single week
for
> a single dept to equal 100%.  What makes this tricky is when employees
leave
> or are added to depts. This is why the department id is stored in the
> activities table below.  This way each activities really belongs to a
> specific dept.  I have found what I need to do for example one above is to
> sum up each project for a particular week and divide it by the num of
> employees to get a departmental percent of time spent on each project.
Then
> on top of that if they need to span weeks I need to sum up each project
from
> there and divide by the number of weeks. This is the only way I have been
> able to get the totals to = 100%.

> Thanks to all you gurus that'll help to take a shot at this one. Maybe I
am
> missing something simple.  Joe C. are you out there?

> Here is the db scheme I have to work with and FYI it cannot be change
short
> term, I am open to hear suggestions as to a better layout long term :

> /****** Object:  Table dbo.departments    Script Date: 4/19/2001 10:45:25
AM
> ******/
> CREATE TABLE "dbo"."departments" (
>  "ID" "int" IDENTITY (1, 1) NOT NULL ,
>  "DeptNum" varchar (10) NOT NULL ,
>  CONSTRAINT "PK___4__10" PRIMARY KEY  CLUSTERED
>  (
>   "ID"
>  )
> )
> GO

> /****** Object:  Table dbo.products    Script Date: 4/19/2001 10:45:25 AM
> ******/
> CREATE TABLE "dbo"."products" (
>  "ID" "int" IDENTITY (1, 1) NOT NULL ,
>  "Name" varchar (50) NOT NULL ,
>  CONSTRAINT "PK___1__10" PRIMARY KEY  CLUSTERED
>  (
>   "ID"
>  )
> )
> GO

> /****** Object:  Table dbo.projects    Script Date: 4/19/2001 10:45:25 AM
> ******/
> CREATE TABLE "dbo"."projects" (
>  "ID" "int" IDENTITY (1, 1) NOT NULL ,
>  "Name" varchar (100) NOT NULL ,
>  "Code" varchar (50) NOT NULL ,
>  "ProductID" "int" NOT NULL ,
>  CONSTRAINT "PK___2__10" PRIMARY KEY  CLUSTERED
>  (
>   "ID"
>  ),
>  CONSTRAINT "FK_projects_2__10" FOREIGN KEY
>  (
>   "ProductID"
>  ) REFERENCES "dbo"."products" (
>   "ID"
>  )
> )
> GO

> /****** Object:  Table dbo.activities    Script Date: 4/19/2001 10:45:25
AM
> ******/
> CREATE TABLE "dbo"."activities" (
>  "ID" "int" IDENTITY (1, 1) NOT NULL ,
>  "EmpID" varchar (10) NOT NULL ,
>  "WeekEnding" "datetime" NOT NULL ,
>  "Percent" "int" NOT NULL ,
>  "ProjectID" "int" NOT NULL ,
>  "DepartmentID" "int" NULL ,
>  CONSTRAINT "PK___3__10" PRIMARY KEY  CLUSTERED
>  (
>   "ID"
>  ),
>  CONSTRAINT "FK_activities_1__10" FOREIGN KEY
>  (
>   "ProjectID"
>  ) REFERENCES "dbo"."projects" (
>   "ID"
>  ),
>  CONSTRAINT "FK_activities_1__15" FOREIGN KEY
>  (
>   "DepartmentID"
>  ) REFERENCES "dbo"."departments" (
>   "ID"
>  )
> )
> GO

> /****** Object:  Table dbo.departments_projects    Script Date: 4/19/2001
> 10:45:26 AM ******/
> CREATE TABLE "dbo"."departments_projects" (
>  "DepartmentID" "int" NOT NULL ,
>  "ProjectID" "int" NOT NULL ,
>  CONSTRAINT "FK_departments_projects_1__10" FOREIGN KEY
>  (
>   "DepartmentID"
>  ) REFERENCES "dbo"."departments" (
>   "ID"
>  ),
>  CONSTRAINT "FK_departments_projects_2__10" FOREIGN KEY
>  (
>   "ProjectID"
>  ) REFERENCES "dbo"."projects" (
>   "ID"
>  )
> )
> GO

Tue, 07 Oct 2003 12:03:26 GMT

 Page 1 of 1 [ 2 post ]

Relevant Pages