Adjusting Employee Hours

I wrote a post earlier, but my manager has asked that I do it
differently. I have a table with employee hours for particular
projects. (I work for a telemarketing company)
Table1:
Project Task Emp Hours
A1 TPV 1 5
A1 TPV 2 2
A3 AUDIT 3 4
TOTAL: 11
A2 ITM 4 10
A2 ITM 5 15
TOTAL: 25
I need to figure out the adjusted hours for these employees. The end
result should look like this:
Project Task Emp Hours AdjHours
A1 TPV 1 5 0
A1 TPV 2 2 0
A3 AUDIT 3 4 0
A2 ITM 4 10 14.4
A2 ITM 5 15 21.6
I need to add the hours for the TPV and Audit records and adjust
against the ITM records by doing the following calculation:
Emp1, 2, 3 hours (tpv and audit) = 11 hours
Emp4, 5 hours = 25 hours
Employee 4: 10 (hours) / 25(project A2) = .4 * 11 (total TPV+Audit
(project A1+A3)) = 4.4 + 10 (emp4 hours). Adjusted hours for emp4 =
14.4
Employee 5: 15 (hours) / 25(project A2) = .6 * 11 (total TPV+Audit
(project A1+A3)) = 6.6 + 15 (emp5 hours). Adjusted hours for emp5 =
21.6
I cannot hardcode anything. I need to use the table2 to figure out the
projects I am totaling and the projects to adjust against.
Table2:
Project Task AdjProject Billable PercentBillable
A1 TPV A2 1 100%
A2 ITM 0 0
A3 AUDIT A2 1 100%
Billable = 1 tells me that projects A1 and A3 need to be adjusted
against project A2.
Please, can anyone help?
Thanks,
NinelHi
The principles are the same as your previous post.
SELECT p.Project, p.Emp, p.Task, p.Hours,
CASE WHEN p.Task in ('Audit','TPV') THEN 0 ELSE p.hours + (p.Hours/t.total1)
* t.total2 END
FROM #ProjectTime P,
( SELECT CAST(SUM(CASE WHEN Task in ('Audit','TPV') THEN 0 ELSE Hours END)
AS DECIMAL(8,3)) as Total1,
CAST(SUM(CASE WHEN Task in ('Audit','TPV') THEN Hours ELSE 0 END) AS
DECIMAL(8,3)) as Total2
FROM #ProjectTime ) t
John
"ninel" wrote:

> I wrote a post earlier, but my manager has asked that I do it
> differently. I have a table with employee hours for particular
> projects. (I work for a telemarketing company)
> Table1:
> Project Task Emp Hours
> A1 TPV 1 5
> A1 TPV 2 2
> A3 AUDIT 3 4
> TOTAL: 11
> A2 ITM 4 10
> A2 ITM 5 15
> TOTAL: 25
> I need to figure out the adjusted hours for these employees. The end
> result should look like this:
> Project Task Emp Hours AdjHours
> A1 TPV 1 5 0
> A1 TPV 2 2 0
> A3 AUDIT 3 4 0
> A2 ITM 4 10 14.4
> A2 ITM 5 15 21.6
> I need to add the hours for the TPV and Audit records and adjust
> against the ITM records by doing the following calculation:
> Emp1, 2, 3 hours (tpv and audit) = 11 hours
> Emp4, 5 hours = 25 hours
> Employee 4: 10 (hours) / 25(project A2) = .4 * 11 (total TPV+Audit
> (project A1+A3)) = 4.4 + 10 (emp4 hours). Adjusted hours for emp4 =
> 14.4
> Employee 5: 15 (hours) / 25(project A2) = .6 * 11 (total TPV+Audit
> (project A1+A3)) = 6.6 + 15 (emp5 hours). Adjusted hours for emp5 =
> 21.6
> I cannot hardcode anything. I need to use the table2 to figure out the
> projects I am totaling and the projects to adjust against.
> Table2:
> Project Task AdjProject Billable PercentBillable
> A1 TPV A2 1 100%
> A2 ITM 0 0
> A3 AUDIT A2 1 100%
> Billable = 1 tells me that projects A1 and A3 need to be adjusted
> against project A2.
> Please, can anyone help?
> Thanks,
> Ninel
>

Adds the group to the database But Security EM

I am trying to add domain group with sql server security privileges to a SQL
Server database.
I view the database with SQL Server Enterprise Manager in the database and
users
and notice that domain group is add (sp_addgroup) but this not applied to
the SQL Server security domain group.
Not using the SQL Server Enterprise Manager. How do get the domain group
with
privileges applied to both places (database user , security).> I view the database with SQL Server Enterprise Manager in the database and
> users
> and notice that domain group is add (sp_addgroup) but this not applied to
> the SQL Server security domain group.
sp_addgroup adds a new database role. It is not used to grant an existing
Windows group database access. Also, sp_addgroup is provided only for
backwards compatibility. Use sp_addrole instead.

> Not using the SQL Server Enterprise Manager. How do get the domain group
> with
> privileges applied to both places (database user , security).
From Query Analyzer:
USE MyDatabase
--grant group permissions to connect to SQL Server
EXEC sp_grantlogin 'MyDomain\MyGroup'
--grant group permissions to use this database
EXEC sp_grantdbaccess 'MyDomain\MyGroup'
To setup object security, you can either grant permissions directly to the
Windows account or grant permissions to a SQL Server role and control
security via role membership
--grant object permissions directly
GRANT SELECT ON MyTable TO [MyDomain\MyGroup]
--grant object permissions to role
GRANT SELECT ON MyTable TO [MyDatabaseRole]
--add group to role
EXEC sp_addrolemember 'MyDatabaseRole', 'MyDomain\MyGroup'
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:229E0AAA-AEED-400D-B082-3425919A0F85@.microsoft.com...
>I am trying to add domain group with sql server security privileges to a
>SQL
> Server database.
> I view the database with SQL Server Enterprise Manager in the database and
> users
> and notice that domain group is add (sp_addgroup) but this not applied to
> the SQL Server security domain group.
> Not using the SQL Server Enterprise Manager. How do get the domain group
> with
> privileges applied to both places (database user , security).
>
>

addling linked server

Hi ,
how do i actually created a linked server in Enterprise
Manager ?
go to the Linked Server , do i choose impersonation ,
no security context or the remote user
usin impersonation means the other server needs to set
up as allowing impersonation
using the remote user means i'll need to create a login
over at the other side ?
have tried but still cannot connect
i have used the Query Analyzer though it like this
sp_addlinkedserver 'dbABC' and it says added
when i run query analyzer to get records using
dbABC.server123.dbo.xxx it says "General network
error ..."
appreciate any advise
thks & rdgsHate to say this, but..
You need to do some more reading on Linked Servers.
There are a lot of little items that need to be checked when you create a
linked server.
The server name, provider name (MSDAORA for example), the data source,
provider string.. catalog)
The second step in the process is to determine your security settings.. Use
sp_addlinkedsrvlgoin.
Once you gotten through the readings on those and linking server in general,
you should have a lot better idea of how it is done.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:05f501c4917d$3a0c4a40$a401280a@.phx.gbl...
> Hi ,
> how do i actually created a linked server in Enterprise
> Manager ?
>
> go to the Linked Server , do i choose impersonation ,
> no security context or the remote user
> usin impersonation means the other server needs to set
> up as allowing impersonation
> using the remote user means i'll need to create a login
> over at the other side ?
> have tried but still cannot connect
>
> i have used the Query Analyzer though it like this
> sp_addlinkedserver 'dbABC' and it says added
> when i run query analyzer to get records using
> dbABC.server123.dbo.xxx it says "General network
> error ..."
> appreciate any advise
> thks & rdgs|||What is the target for the linked server? Is it SQL Server? Depending on
what security context you need to have the queries run on your linked
server target, you can select 1 of the 4 options on the security tab. Click
the Help button (in SQL Enterprise Manager -> Configure Linked Server's
Security tab) to see what each of these options means.
The easiest 1 of the 4 options you can use is the last one "Be made using
this security context". This option requires you to setup a login on the
linked server target and provide the login name and password in the linked
server configuration dialog in Enterprise Manager. (Keep in mind that if
you're using SQL Server as the targer for your linked server, choosing this
option will mean that you have to configure your target SQL Server to be
configured for SQL and Windows Authentication).
Refer to the following topic for additional information :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
_1_server_24tv.asp
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Adding RDL to manager via .NET

Hi
is it possible to add a RDL-Report via a Window-Application to the report manager?
Thanks in advance!
Regards
AlexConsier using an RSS file with RS.exe, if you're okay with doing this via command-line
--
Message posted via http://www.sqlmonster.com

Adding new lines into results in the

I used to be able to enter new lines into the result pane cell for text
(and varchar) data in Enterprise Manager, but now that I am using SQL
2005 Management Studio, this feature is gone.

Is there any way to do this?

Also, copying to/from excel chops off part of the text in a cell and is
very infuriating.

Any help would be appreciated.

Dan(monkeyboydan@.gmail.com) writes:
> I used to be able to enter new lines into the result pane cell for text
> (and varchar) data in Enterprise Manager, but now that I am using SQL
> 2005 Management Studio, this feature is gone.
> Is there any way to do this?
> Also, copying to/from excel chops off part of the text in a cell and is
> very infuriating.

Time to learn to write INSERT and UPDATE statements, I see!

There are plenty of differences between the tools in SQL 2000 and SQL 2005.
Keep in mind that Open table is intended to be a fairly simple tool to
view and edit data. For more heavy-duty stuff, you would use an application.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the reply.

Your point is noted and I am quite happy to do insert and update
statements but there are occasions where a little ad-hoc editing and
copying and pasting is useful and this isn't possible any more and it
seems a bit silly 'cause it makes the 'open table' functionality
vritually pointless is a lot of occasions.

Adding multiple role assignments all at once

Is there a way to add multiple role assignments to a report definition all at once?

Instead of going to the Report Manager, selecting each individual report definition, and then clicking on "New Role Assignment" multiple times to add users, is there a way to add multiple users all at once?

You can write a script and use rs.exe.

Here is a pointer to the tool. There should also be information for example scripts.

http://msdn2.microsoft.com/en-us/library/ms162839.aspx

Adding Group to MSSQL

As I understand it, a system group should show up in enterprise manager. But
when I create a new group in computer manager, the group doesn't show in up
in mssql. Am I doing this wrong?Got it,
Exec sp_grantlogin 'computername\groupname'
Thanks

Adding Group to MSSQL

As I understand it, a system group should show up in enterprise manager. But when I create a new group in computer manager, the group doesn't show in up in mssql. Am I doing this wrong?Got it,
Exec sp_grantlogin 'computername\groupname
Thanks

adding client server aliases

How can I import a client alias list in SQL Server 2005 Configuration Manager? The tool provides a way to export a list, but not to import a list. Adding 100 SQL Server instances via the GUI is time consuming and agonizing.

Thanks,

Justin Randall

Unfortunately, as you noticed, the functionality to import a list is missing.

If you think it would be a useful addition, please let your wishes/needs be known at:

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

|||I will make the suggestion. Thanks!

Adding and additional drive

On a database server, I have mapped a new drive letter to
a SAN so that I can backup my database. When I go into
Enterprise Manager, I can see the drive, but when I try to
create a new backup device, it gives me an error stating
that 'the directory in whch you want to create this file
does not exist'.
If I try to create a new data or log file for a database
on that server, I do not see the drive.
Should I just reboot the server or is it something else?
SQL Server will probably not see mapped drives... it is running under a
different account than what you used when you mapped the drive.
The BEST way to do this is to make sure that the account SQL Server is
running under has rights to the remote share.
Simply issue a backup using the UNC
BACKUP DATABASE master TO DISK = '\\RemoteServer\SomeShare\master.bak' WITH
INIT
Keith
"canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:225801c49a93$8ec19da0$a501280a@.phx.gbl...
> On a database server, I have mapped a new drive letter to
> a SAN so that I can backup my database. When I go into
> Enterprise Manager, I can see the drive, but when I try to
> create a new backup device, it gives me an error stating
> that 'the directory in whch you want to create this file
> does not exist'.
> If I try to create a new data or log file for a database
> on that server, I do not see the drive.
> Should I just reboot the server or is it something else?
>
|||http://support.microsoft.com/default...b;en-us;555128
Everything you ever wanted to know about backing up to remote shares.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:225801c49a93$8ec19da0$a501280a@.phx.gbl...
> On a database server, I have mapped a new drive letter to
> a SAN so that I can backup my database. When I go into
> Enterprise Manager, I can see the drive, but when I try to
> create a new backup device, it gives me an error stating
> that 'the directory in whch you want to create this file
> does not exist'.
> If I try to create a new data or log file for a database
> on that server, I do not see the drive.
> Should I just reboot the server or is it something else?
>

Adding and additional drive

On a database server, I have mapped a new drive letter to
a SAN so that I can backup my database. When I go into
Enterprise Manager, I can see the drive, but when I try to
create a new backup device, it gives me an error stating
that 'the directory in whch you want to create this file
does not exist'.
If I try to create a new data or log file for a database
on that server, I do not see the drive.
Should I just reboot the server or is it something else?SQL Server will probably not see mapped drives... it is running under a
different account than what you used when you mapped the drive.
The BEST way to do this is to make sure that the account SQL Server is
running under has rights to the remote share.
Simply issue a backup using the UNC
BACKUP DATABASE master TO DISK = '\\RemoteServer\SomeShare\master.bak' WITH
INIT
--
Keith
"canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:225801c49a93$8ec19da0$a501280a@.phx.gbl...
> On a database server, I have mapped a new drive letter to
> a SAN so that I can backup my database. When I go into
> Enterprise Manager, I can see the drive, but when I try to
> create a new backup device, it gives me an error stating
> that 'the directory in whch you want to create this file
> does not exist'.
> If I try to create a new data or log file for a database
> on that server, I do not see the drive.
> Should I just reboot the server or is it something else?
>|||http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
Everything you ever wanted to know about backing up to remote shares.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:225801c49a93$8ec19da0$a501280a@.phx.gbl...
> On a database server, I have mapped a new drive letter to
> a SAN so that I can backup my database. When I go into
> Enterprise Manager, I can see the drive, but when I try to
> create a new backup device, it gives me an error stating
> that 'the directory in whch you want to create this file
> does not exist'.
> If I try to create a new data or log file for a database
> on that server, I do not see the drive.
> Should I just reboot the server or is it something else?
>