One entry per individual with multiple entries

Hi,
I have a table that consists of sales by employee by month and because there
are more than one sales category, there might be multiple entries per
employee per month. "Hours" represent the TOTAL number of hours worked for
a
particular month and it is the same for all sales categories within a month.
I'm looking for a way to have the first entry per month be populated with th
e
number of hours worked for that particular month (i.e. 160) and all others b
e
blank or 0. What would be the best way to achieve that?
Month Empl_ID Category Sales Hours
01-05 12345 A 30 160
01-05 12345 B 32 160
02-05 12345 A 44 165
02-05 12345 C 13 165
02-05 12345 E 5 165
Thanks,> I'm looking for a way to have the first entry per month be populated with
> the
> number of hours worked for that particular month (i.e. 160) and all others
> be
> blank or 0. What would be the best way to achieve that?
That would just be a kludge around a flawed design. Far better to remove the
redundant hours worked column. If the hours are only to be recorded at the
employee/month level then they belong in a separate table.
Assuming for the moment that your table design is set in stone, you could
perhaps do something like this to reset the other hours to zero:
UPDATE sales
SET hours = 0
WHERE EXISTS
(SELECT *
FROM sales AS S
WHERE S.month = sales.month
AND S.emp_id = sales.emp_id
AND S.category < sales.category) ;
(untested)
Wouldn't you rather fix the design?
David Portas
SQL Server MVP
--
"Pasha" <Pasha@.discussions.microsoft.com> wrote in message
news:9712A4A2-EA55-4AD1-9D88-6AF1C8DE92DF@.microsoft.com...
> Hi,
> I have a table that consists of sales by employee by month and because
> there
> are more than one sales category, there might be multiple entries per
> employee per month. "Hours" represent the TOTAL number of hours worked
> for a
> particular month and it is the same for all sales categories within a
> month.
> I'm looking for a way to have the first entry per month be populated with
> the
> number of hours worked for that particular month (i.e. 160) and all others
> be
> blank or 0. What would be the best way to achieve that?
> Month Empl_ID Category Sales Hours
> 01-05 12345 A 30 160
> 01-05 12345 B 32 160
> 02-05 12345 A 44 165
> 02-05 12345 C 13 165
> 02-05 12345 E 5 165
>
> Thanks,|||On Tue, 4 Oct 2005 15:07:03 -0700, Pasha wrote:

>Hi,
>I have a table that consists of sales by employee by month and because ther
e
>are more than one sales category, there might be multiple entries per
>employee per month. "Hours" represent the TOTAL number of hours worked for
a
>particular month and it is the same for all sales categories within a month
.
>I'm looking for a way to have the first entry per month be populated with t
he
>number of hours worked for that particular month (i.e. 160) and all others
be
>blank or 0. What would be the best way to achieve that?
>Month Empl_ID Category Sales Hours
>01-05 12345 A 30 160
>01-05 12345 B 32 160
>02-05 12345 A 44 165
>02-05 12345 C 13 165
>02-05 12345 E 5 165
>
>Thanks,
Hi Pasha,
You need to normalize this design. The current design allows one to
store contradicting data. What if Hours is NOT the same on all rows for
an employee in a month?
Here's how your tables should look:
CREATE TABLE Table1 -- Use a better name
(Month datetime NOT NULL, -- Maybe other datatype
Empl_ID int NOT NULL,
Hours int NOT NULL,
PRIMARY KEY (Month, Empl_ID),
-- FOREIGN KEY (Empl_ID) REFERENCES Personnel(Empl_ID),
CHECK (Hours >= 0),
)
CREATE TABLE Table1 -- Use a better name
(Month datetime NOT NULL, -- Maybe other datatype
Empl_ID int NOT NULL,
Category char(1) NOT NULL,
Sales int NOT NULL,
PRIMARY KEY (Month, Empl_ID, Category),
FOREIGN KEY (Month, Empl_ID) REFERENCES Table1 (Month, Empl_ID),
CHECK (Sales >= 0),
CHECK (Category IN ('A','B','C','D','E')),
)
For now, the kludge to set Hours to 0 for all but the "first" (based in
category) in the month is:
UPDATE BadTable
SET Hours = 0
WHERE EXISTS (SELECT *
FROM BadTable AS a
WHERE a.Empl_ID = BadTable.Empl_ID
AND a.Month = BadTable.Month
AND a.Category < BadTable.Category)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||This is a fact table for OLAP cube, so the design is good for the cube. I
was thinking about having an dentity column, minimum of which would determin
e
the first entry...
"David Portas" wrote:

> That would just be a kludge around a flawed design. Far better to remove t
he
> redundant hours worked column. If the hours are only to be recorded at the
> employee/month level then they belong in a separate table.
> Assuming for the moment that your table design is set in stone, you could
> perhaps do something like this to reset the other hours to zero:
> UPDATE sales
> SET hours = 0
> WHERE EXISTS
> (SELECT *
> FROM sales AS S
> WHERE S.month = sales.month
> AND S.emp_id = sales.emp_id
> AND S.category < sales.category) ;
> (untested)
> Wouldn't you rather fix the design?
> --
> David Portas
> SQL Server MVP
> --
> "Pasha" <Pasha@.discussions.microsoft.com> wrote in message
> news:9712A4A2-EA55-4AD1-9D88-6AF1C8DE92DF@.microsoft.com...
>
>|||Well it doesn't look much like a fact table but if it is then one option is
to normalize and then construct the fact table in a view.
David Portas
SQL Server MVP
--
"Pasha" <Pasha@.discussions.microsoft.com> wrote in message
news:3BD96ACF-A4A0-456C-8E5D-D58C5336DB06@.microsoft.com...
> This is a fact table for OLAP cube, so the design is good for the cube. I
> was thinking about having an dentity column, minimum of which would
> determine
> the first entry...
>
> "David Portas" wrote:
>|||This is by no means "good" for a cube. Every row in the fact table should
be of the same "grain" and each column in the row should be to that grain.
For this to be a proper fact table, one of two things should be true:
Either hours should be at the same level as category (so a-hours + b-hours +
e-hours total hours, or you need to split this into two fact tables, one at
the grain of a category per month, the other at hours per month. Of course
the actual shape of the fact table would be based on your source data.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Pasha" <Pasha@.discussions.microsoft.com> wrote in message
news:3BD96ACF-A4A0-456C-8E5D-D58C5336DB06@.microsoft.com...
> This is a fact table for OLAP cube, so the design is good for the cube. I
> was thinking about having an dentity column, minimum of which would
> determine
> the first entry...
>
> "David Portas" wrote:
>sql