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

Old join syntaxis its support by SQL Server 2000?

A question...
The old join syntaxis works in SQL Server 2000??...
I mean, this example works in SQL Server 2000??...
SELECT *
FROM Employee e, Departmend d
WHERE d.departmentId *= e.departmentIdI would think most of the previous SQL 6.5 etc. should be
upward compatible. I use INNER JOINS etc. and haven't
run across problems between the versions. What is the
previous version of SQL are you referring to?

BobbyJ

Originally posted by ericka
A question...

The old join syntaxis works in SQL Server 2000??...
I mean, this example works in SQL Server 2000??...

SELECT *
FROM Employee e, Departmend d
WHERE d.departmentId *= e.departmentId|||I'm using SQL Server 6.5 but I want to migrate to SQL Server 7.0 and I want to left like that my joins...; but I want to know if this joins (=*, *=) it'll in Sql Server 2000...

Originally posted by BobbyJ
I would think most of the previous SQL 6.5 etc. should be
upward compatible. I use INNER JOINS etc. and haven't
run across problems between the versions. What is the
previous version of SQL are you referring to?

BobbyJ|||Ok. Don't quote me but I think it should. Let's see what others respond
with.....|||use

select *
from employee e
left join departmet d on (d.departmentid = e.departmentid)

radzi.

"BobbyJ" wrote in message
news:2430436.1043361575@.dbforums.com...|||For Books Online:

Transact-SQL Joins
In earlier versions of Microsoft SQL Server 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax.

The SQL-92 standard does support the specification of inner joins in either the FROM or WHERE clause. Inner joins specified in the WHERE clause do not have the same problems with ambiguity as the Transact-SQL outer join syntax.

All this to say that the Inner/Outer Right/Left syntax is more reliable and at somepoint the "*" syntax will no longer be supported.|||'ambiguous" cauze the user is id***ot and does not know what he is doing...

ORACLE 8 does not know RIGHT JOIN and uses only *=

did I get any "ambiguous" joins with Oracle? NO!

LEFT JOIN is more powerful than *= but that's it. It is not different or ambiguous, it can do just more.... NOT LESS nor DIFFERENT!

To your question... if your SQL works with =* then it WILL work just fine....

jiri|||RE:
Q1 [Does the old join syntax work in SQL Server 2000?]
A1 As others have already noted, for the most part, such older syntax is (still) supported.

Some additional points that maybe helpful:

i Consider carefully checking for any potentially problematic changes in behavior between versions. There are not very many, however if any do apply they may present some issues for your environment. Several relate to character handling and related function differences e.g.(empty string 6.x literals ' ' interpreted as a space, CHARINDEX and PATINDEX processing to generate null results, etc.).

ii Often it is possible to have the Enterprise Manager query building functionality, (not query analyzer), 'rewrite' older *= joins in the newer format (by pasting in the old format sql and clicking on verify syntax).

iii If troublesome version related behavior issues (mentioned in i above) are found to exist, you may wish to consider temporarily implementing a 6x db compatibility setting (the setting may be easily changed). This may allow significant use of 7.0 features / advantages while allowing 6.x issues to be addressed (and conveniently tested / verified) over a longer time frame.

For Example:

-- To view the current dbcmptlevel of Pubs
exec sp_dbcmptlevel
@.dbname = 'Pubs'
Go

-- To set to 6.5:
exec sp_dbcmptlevel
@.dbname = 'Pubs',
@.new_cmptlevel = 65
Go

-- Check the dbcmptlevel change to 6.5 settings
exec sp_dbcmptlevel
@.dbname = 'Pubs'
Go|||P.S. Was the timestamp access issue (RE: 6.5 timestamp column access in 7.0) resolved satisfactorily?