Dynamic SQL Sp_executesql And Employee ID List Varchar

Jan 11, 2008

For the example stored procedure below, lets say I want to use the "sp_executesql" stored procedure instead of "EXECUTE".

 CREATE PROCEDURE [dbo].[spGetEmployees]
@managerId int,
@employeeIdList nvarchar( 200 )
AS

EXECUTE
(
'SELECT *
FROM [dbo].[hrEmployees]
WHERE [ManagerID] = ' + CAST( @managerId AS nvarchar ) + '
AND [EmployeeID] IN (' + @employeeIdList + ')'
)

 

 

I want to rewrite it something like this. Please see MSDN documentation ( http://msdn2.microsoft.com/en-us/library/ms188001.aspx ) for sp_executesql stored procedure usage.DECLARE @selectStatement nvarchar(500)
SET @selectStatement = 'SELECT * FROM [dbo].[hrEmployees] WHERE [ManagerID] = @paramManagerID AND [EmployeeID] IN (' + @employeeIdList + ')'


DECLARE @paramList nvarchar(500)
SET @paramList = '@paramManagerID int'

EXECUTE sp_executesql @selectStatement, @paramList, @paramManagerID = @managerId

 

Reason for using "sp_executesql" is the performance gain.
However, as you can see, the @employeeIdList cannot be included as part of the Parameter List ( @paramList )
like the @managerId since it **has** to be passed in as a varchar ( example: @employeeIdList = '1,2,3,4' ).

My Question 
Is there a way to include it as a parameter instead of it being part of the embedded dynamic SQL syntax?

View 1 Replies


ADVERTISEMENT

Sp_executesql - Error Converting Data Type Varchar To Datetime

Oct 13, 2006

Hi,

I am havin problems with the following giving a message

Msg 8114, Level 16, State 4, Line 0

Error converting data type varchar to datetime.



exec sp_executesql

@stmt=N'UPDATE CUSTOMER

SET [REQUEST] = @19, [DISC_EXPRY] = @28, [GROUP1] = @29, [GROUP2] = @31, [PR_LEVEL] = @48, [MOD_DATE] = @55, [MEM_CODE] = @63, [MEM_DATE] = @64, [HO_MOD] = @66, [LASTUPDATE] = @78

WHERE [ID] = @Old_1'

, @params=N'@19 nvarchar(4),@28 DateTime,@29 nvarchar(5),@31 nvarchar(5),@48 nvarchar(1),@55 DateTime,@63 nvarchar(7),@64 DateTime,@66 Integer,@78 Float,@Old_1 Integer',

@19= 'NRMA',@28= '1752-09-14',@29= 'ALBUM',@31= 'FRAME',@48= 'A',@55= '2006-10-10',@63= '1003.50',@64= '2006-10-10',@66= 3,@78= 39000.190633,@Old_1= 454636









however, as soon as I remove the datetime fields it works



exec sp_executesql

@stmt=N'UPDATE CUSTOMER

SET [REQUEST] = @19, [GROUP1] = @29, [GROUP2] = @31, [PR_LEVEL] = @48, [MEM_CODE] = @63, [HO_MOD] = @66, [LASTUPDATE] = @78

WHERE [ID] = @Old_1'

, @params=N'@19 nvarchar(4),@29 nvarchar(5),@31 nvarchar(5),@48 nvarchar(1),@63 nvarchar(7),@66 Integer,@78 Float,@Old_1 Integer',

@19= 'NRMA',@29= 'ALBUM',@31= 'FRAME',@48= 'A',@63= '1003.50',@66= 3,@78= 39000.190633,@Old_1= 454636



what am I doing wrong with the datetime parameters?





Grimhael

View 4 Replies View Related

Activating Dynamic Sql Whereby Sp_executesql

Apr 2, 2008

Hi everyone,
The following code to run dynamic sql:

DECLARE @params NVARCHAR(4000)
DECLARE @portion INT
SET @portion=6
DECLARE @mydynamic NVARCHAR(4000)
SELECT @mydynamic = ' SELECT TOP @portion * FROM server.databse.dbo.table'
SELECT @params = N'@portion INT '
EXEC sp_executesql @mydynamic,@params, @portion

yields the following error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@portion'.

anyone has any idea why ? How it should be corrected?
Thanks

View 11 Replies View Related

SP_EXECUTESQL / Dynamic TSQL

Sep 4, 2007

Here is the query..
@ENTITY, @ FIELD, @KEYID, @VALUE comes dynamically using cursor. Here in this example I have took one sample and assigned it a value to do sanity check.



DECLARE @ENTITY nvarchar (100)

SET @ENTITY ='AccidentDimension'


DECLARE @FIELD nvarchar (100)

SET @FIELD = 'UHReceivedDate'


DECLARE @KEYID nvarchar (100)

SET @KEYID = '1074958'


DECLARE @VALUE varchar (100)

SET @VALUE = '10JAN2020'


DECLARE @FLAG NVARCHAR(50);

SET @FLAG = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'LastUpdateFlag';


DECLARE @KeyName NVARCHAR(50);

SET @KeyName = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'



DECLARE @KeyValue INT

DECLARE @SQL1 NVARCHAR (1000)

SET @SQL1 = ' SET @KeyValueOUT = Select '+ @KeyName + ' FROM ClaimManagementFact WHERE ClaimKey = ' + @KEYID +

' GROUP BY ' + @KeyName + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(IncidentOnlyClaimCount) > 0 )'


EXECUTE sp_executesql @SQL1, @KeyValueOUT INT OUTPUT;


@KeyValue= @KeyValueOUT OUTPUT;


Select @KeyValue

A) What i want to do is store the value resulting from select statemenet by executing @SQL1 which is INT to @KeyValue. In previous thread I tried various thing but resulting in errors.

Thanks in advance for help

View 6 Replies View Related

Build Dynamic Query Using Sp_executesql

Sep 20, 2004

Hi there,

I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:

-- insert data into proper tables with extract date added
SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT
SurgeryKey,'+
@extractDate+',
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'

EXEC master..sp_executesql @SQLString

And here's the error message that I get:

Server: Msg 241, Level 16, State 1, Line 90
Syntax error converting datetime from character string.

I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.

Can anyone help?

Thanks

View 1 Replies View Related

Dynamic Code Activated By Sp_executesql Can Anyone Help Me With That One?

Apr 2, 2008

Hi everyone
I try to run Dynamic sql wherby sp_executesql as follows:



Code Snippet
DECLARE @params NVARCHAR(4000)
DECLARE @portion INT
SET @portion=6
DECLARE @mydynamic NVARCHAR(4000)
SELECT @mydynamic = ' SELECT TOP @portion * FROM server.database.dbo.mytable'
SELECT @params = N'@portion INT '
EXEC sp_executesql @mydynamic,@params, @portion






I get the following error message:



Code Snippet
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@portion'.




Any idea what is wrong with that code?Thanks

View 6 Replies View Related

SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule

Nov 23, 2007

Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.

So, is this possible using data driven subscriptions? Scenario is:

1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.

Any tips on how to get this working?

Thanks

Mark Smith

View 3 Replies View Related

Sp_executesql From App's Dynamic SQL Causing Performance Problems Vs. Query From SSMS

Aug 12, 2006

Okay, so I came across an odd performance issue that I'm wondering if some guru can help me out with.

I have a query that uses a paging algorithm that uses a paging algorithm and a table variable, then gets a page of data based on a join to that table variable. Here's a simplified query using the algoritm:

--declare table variable... not shown for brevity

--make sure we only store the least amount of records possible
SET ROWCOUNT ( @pageNumber + 1 ) * @pageSize

--insert into table variable
INSERT INTO @TableVariable( Key )
SELECT key FROM table
WHERE whatever = @p1

--we only want one page of data
SET ROWCOUNT @pageSize

--now get the page of data from the table
SELECT key FROM table
WHERE whatever = @p1
AND [TableVar Identity Column] > @pageNumber * @pageSize

The algorithm works great for our needs, BUT, I noticed something a little odd about its behavior during performance testing.

In particular, when I run the query using Sql Server Management Studio, where I manually DECLARE all the variables it ends up needing only 156 reads to complete the job. When I call it from the app using ADO.NET, however, I noticed it needs 310 reads! Huh?

I looked for differences, and the only one I could determine was that ADO.NET passes the query and uses sp_executesql and passes the parameters vs. declaring and setting them statically before executing the query. I confirmed that this was the issue by manually running sp_execute SQL and seeing that it took roughly the same number of reads (274) to process the query.

Naturally, I don't want the time it takes to perfrom my query to double, but and frankly I don't understand why there would be a difference in performance. Can anyone help me track down what is going on and suggest to me how to fix the problem.

I assume that SQL Server Management Studio optimizes the execution path somehow, but I'm not sure how to gain the same benefit for my passed query. Can I enable something with hints? Is there something else going on that I should know about?

View 10 Replies View Related

Dynamic SQL - Date To Varchar

Jun 18, 2008

I cannot execute a dynamic SQL function when I convert a datetime to a string:

declare @date_key datetime
set @date_key = '5/1/08'

select * from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI'
,'Execute dbFinance..spCalc ''' + convert(varchar(20), @date_key, 101) +'''')

The code below produces the exact same text but DOES work:
select * from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI','Execute dbFinance..spCalc ''05/01/2008''')

View 6 Replies View Related

Dynamic List

May 1, 2007

Is there a way to create a dynamic list? I have two variables I want to combine with two constants to create a list of 4 entries that I could run through a loop.

View 3 Replies View Related

Dynamic Filedlength Of Varchar Fields In Source

Nov 21, 2006

I'm using SSIS to import data from a table (SQL) containing varchar fields. The problem is, that those varchar fields are changing over time (sometimes shrinking and sometimes expanding). I.e. from varchar(16) to varchar(20).

When I create my SSIS package, the package seem to store information about the length of each source-field. At runtime, if the field-length is larger then what the package expects an error is thown.

Is there anyway around this problem?

Oh, yeah... My destination fields are a lot wider then the source fields, so the problem is not that the varchar values doesn't fit in my destination table, but that the package expects the source to be smaller...

Regards Andreas

View 3 Replies View Related

Exec Sp_executesql Vs. Sp_executesql And Performance

Jul 23, 2005

This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?

View 3 Replies View Related

Issue With Executing Dynamic Sql With Inbound Paramter As Varchar Striong

Aug 4, 2007

Hi All:

I am trying to execute a dynamic sql, the dynamic sql makes use of an inbound paramter defined as varchar.

When I try to execute it fails, because it does not plavce the inbound paramter in quotes.

Any help would be appreciated.

In the bound search as an eaxmple can be" 'NY'

@P_SEARCH_VALUE='NY'

SET @V_SQL_FILTER = N' WHERE STATE = '+@P_SEARCH_VALUE

SET @V_SQL=@V_BASE_SQL+@V_SQL_FILTER

EXEC sp_executesql @V_SQL

Here is the v_sql out put:

SELECT TOP 100 * FROM V$ZIPCODE_LOOKUP_ALL WHERE STATE = NY

As you can see the sql will fail because the NY is not in quotes.

I tried using '@P_SEARCH_VALUE''' and other forms but could not get it work.

View 6 Replies View Related

Build SQL Query From Dynamic Checkbox List

Nov 26, 2003

Not sure if this is the place to post this, but here goes.

I need to setup an options screen where my customers can customize which locations will be stored for their user id when pulling reports. I have checkbox list that dynamically loads their locations. I need to store the selected checkbox items in my table and then each time they login in to run a report, it will use the stored Location values in my SQL query.

Synopsis:
Selected locations stored in table. When the report is ran, the location values are pulled and added to my queries WHERE clause.

Thanks.

View 1 Replies View Related

Dynamic List Of Tables In Integration Services

Apr 27, 2006

I need to report on data from several databases on several servers. They are all SQL Server 2005 databases. I am trying to created an Integration Services task to consolidate and transform this data for easy reporting. The problem I am having is one database in particular. It has tables like this:

tblLookupData_Customer1
tblLookupParseData_Customer1
tblLookupData_Customer2
tblLookupParseData_Customer2
tblLookupData_Customer3
tblLookupParseData_Customer3

I want to use only the tables of the form "tblLookupParseData*" for this list. I can do this in Stored Procedures by dynmacally building up the sql query. I cannot find out how to do this in Integration Services. When I make Datasource Views, they seem to expect me to pick from a list of known tables. This list of tables grows as Customers are added to the system.

NOTE: The way the tables are structured was NOT my idea. I hate storing "data" in the structure of the database. Many people also do this when they create "period" tables such as "CustomerData_05_2005". It speeds up writing the data, and querying a specific table, but it is a nightmare for reporting. I cannot change this as it is not in my responsibility.

View 1 Replies View Related

Dynamic CASE Statement Based On List Of Dates

Oct 5, 2007

I have the following table of data.  I need to take a date from a large table and do the following case:CASEWhen date < date(0)     Then '0'When date between date(0) and date(1)      Then '1'When date between date(1) and date(2)     Then '2'When date >= date(3)      Then '3'What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)I hope I am making sense.  Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date.  Any ideas how to manipulate these dates into a case statement?  Don't worry about the second table below.  I just wanted you to see why I need to return an int from the Case function.thanksMilton



Dates Table

Date

4/1/2003

1/1/2006

4/2/2007

Fee Table



Date
Period
Class
Fee

1
Daily
True
329

1
Half Day
True
178

1
OT
True
49

1
Hourly
True
41

1
Daily
False
156

1
Half Day
False
86

1
OT
False
27

1
Hourly
False
19

2
Daily
True
355

2
Half Day
True
192

2
OT
True
50

2
Hourly
True
44

2
Daily
False
171

2
Half Day
False
92

2
OT
False
28

2
Hourly
False
21

3
Daily
True
364

3
Half Day
True
197

3
OT
True
51

3
Hourly
True
45

3
Daily
False
175

3
Half Day
False
94

3
OT
False
29

3
Hourly
False
21

View 3 Replies View Related

TOP 1 For Each Employee

Jul 19, 2013

I need to select last order for each employees for homework. I use northwind database for testing. I can solve it by correlated subquery but the professor said me it is not optimized.

select orderid, customerid, employeeid, orderdate
from orders as o1
where orderdate = (select max(orderdate) from orders as o2 where o1.employeeid = o2.employeeid);

View 6 Replies View Related

Problems Moving Data Over 8000k In DB2 Varchar Column Into SQL Server Varchar(max) Using SSIS

Nov 20, 2007



I have looked far and wide and have not found anything that works to allow me to resolve this issue.

I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.

I get the error:
[OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".

[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".

[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?

View 10 Replies View Related

Update Employee ID Throughout Database

Aug 15, 2012

I have a database that has dozens of tables. Many of these tables reference the employee ID.For example tblDaysOff has a column employeeID that is matched on tblEmployees.ID, and there are many such tables.

Now the employee IDs are changing the way they are generated. Instead of a alphanumeric value being stored as a text value, all employee IDs will be uniqueidentifiers stored as text values.The question is, how can I change every instance of "somevalue" in every record in every column where the column name is "employeeID" in every table in the database to "differentvalue" where employeeID = "somevalue"?This is what I have cobbled together from multiple sources ... but there is a syntax error where @max is located.

Code:
USE CsDB
DECLARE @t TABLE(tRow int identity(1, 1), tSchemaName nvarchar(max), tTableName nvarchar(max))
INSERT INTO @t
SELECT SCHEMA_NAME(schema_id), t.name
FROM sys.tables AS t
JOIN sys.columns c ON c.object_id = t.object_id
WHERE c.name LIKE '%employeeID%'

[code]...

Obviously I don't want to run this and then have to try and recover the database when things go away.

View 14 Replies View Related

Sum Quantity With Like Employee And Itemname

Nov 8, 2014

I have a table Item_used like this

Itemname Employee Quantity
pencil samlopez 10
pencil samlopez 5

All I want is to make a report that sum all the quantity of the same items with the same employee like this

Itemname Employee Quantity
pencil samlopez 15

View 1 Replies View Related

The Data Types Varchar And Varchar Are Incompatible In The Modulo Operator

Jan 4, 2008

I am trying to create a store procedure inside of SQL Management Studio console and I kept getting errors. Here's my store procedure.




Code Block
CREATE PROCEDURE [dbo].[sqlOutlookSearch]
-- Add the parameters for the stored procedure here
@OLIssueID int = NULL,
@searchString varchar(1000) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @OLIssueID <> 11111
SELECT * FROM [OLissue], [Outlook]
WHERE [OLissue].[issueID] = @OLIssueID AND [OLissue].[issueID] = [Outlook].[issueID] AND [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
ELSE
SELECT * FROM [Outlook]
WHERE [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
END




And the error I kept getting is:

Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 18

The data types varchar and varchar are incompatible in the modulo operator.

Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 21

The data types varchar and varchar are incompatible in the modulo operator.

Any help is appreciated.

View 5 Replies View Related

How To Get Top Three Salary Getters From Table Employee

Mar 1, 2007

Dear All,i want to know how to get top three salary getters from the employee(eid , ename, salary) table
i tried this select  top 3  salary from employee order by salary desc       
but it gives me top three salary record say there is salary 1000,1200,1300,1300,1500then my query return me 1500,1300,1200 whereas i want to 1500,1300,1300,1200
how can i do it
please help
thanks 
  

View 1 Replies View Related

How To Get All Employees Under Any Perticular Manager Employee !

Jan 31, 2008

I am using SqlServer 2000 with asp.net 2.0, I have a table tbl_employees, with fields (empId, empName, empManagerId), with following data...



empId
empName
empManagerId

1
A


2
B
1

3
C
2

4
D
2

5
E
4
Now the question is that what should be the single line query or best solution if i want to get all employess under a perticular manager ?For example; Employees under 'A' are (B,C,D,E)    //(C,D,E are also indirectly under A)Emplloyess under 'B' are (C,D & E; E is also under B as his because his managwer 'D' is himself under 'B')
Please advise..Thanks alot.

View 7 Replies View Related

Advantages/Disadvantages B/w Being Consultant Vs Employee

Jul 12, 2000

Hi, I would appreicate your opinion/ feed back about Being consultant vs Being employee.

What are the advantages/disadvantages.
Does consultant makes more money than an employee and why so


Thanks for your response
Ali

View 2 Replies View Related

Monthly Absence Calculation For Employee

May 30, 2012

I need to calculate monthly absence days for an employee using SQL Server 2008.

Need to calculate the number of absence days for each month when start and end of the absence dates are given

INPUT: 01/15/2010 05/25/2010

OUTPUT:
Jan 16
Feb 28
Mar 31
APR 30
May 25

View 7 Replies View Related

Find Junk Patterns In Employee Name

Jul 6, 2012

string starts with 3 digit same number
111 H,777GGG,9999 H etc

string starts with NULL

string that starts with sequential digits
123g,897 k

string that starts with sequential alphabets
abcmki12, ghimkkk, rst123 5 etc

string only one character (digit or letter)

string only has same character repeated 3 times except for OOO

string only has three characters, 1 digit and two letters or 2 digit one letter

string that has only two characters one digit and one letter

View 1 Replies View Related

Pull Email Address From Employee ID

Jan 28, 2014

There is a Table DISPLAY_DETAILS in which a Column - DISPLAY_NAME, which displays data in it as :

Joe Barnard(123456);Paul Johnson(114454); - Display as Name(Employee Id).

There is a EMployee Master Table EMP_MASTER Which contains Employee Id and Email Address Columns.

From this: Joe Barnard(123456);Paul Johnson(114454); I need to get the Email Address for these Employee Ids - 123456 and 114454

View 3 Replies View Related

Month Wise Employee Count

Apr 26, 2007

Hi Folks,

Please help me in writing a query. I need to get current strength of employee's, monthwise (till each month).

Table Structure
----------------
Employee_Master

ED_EmpId ED_DOJ ED_Name
-------- ------- ---------
2006-01-02 SRINIVASA
2006-01-02 KAVITA
2006-01-02 VIVEK
2006-02-20 CHANDRA
2006-02-25 PARIMAL
2006-02-30 SATISH
........
2007-01-10SANJEEV SHARMA
2007-01-14 JITENDRA PRATAP SINGH
2007-03-15 SANDEEP
2007-03-02 SUNIL KUMAR SHARMA


I require the data in the below format..

For the year '2007-2008'

Month Count
----- -----
April(07) 10
May . 10
June . 10
July . 10
August . 10
Sept 10
Oct 10
Nov 10
Dec 10
Jan (08) 10
Feb . 10
March . 10



Thanks.

View 2 Replies View Related

Using Employee/Boss Self Referencing Table

Feb 28, 2008

I have an Employee table that has
EmployeeID (PK)
SupervisorID (which is really just another EmployeeID)
..random junk...


Now that part makes sense, everyone gets one and only one boss.

Their boss can change, and therefore the SupervisorID would be updated.

Now I have an EmployeeEvals table that has quarterly evaluation data.

I want to relate these two tables.

Eval table has
EvalID (PK)
ReviewedEmployeeID (the one being evaluated)
SupervisorID (the one doing the evaluation)

Now I need to link this back to the employee table (at least I think I do).

So I would want to relate it by the ReviewedEmployeeID going back to EmployeeID in the employee table and I also want the SupervisorID to do the same...

But of course that won't work because that would seem to indicate that a single record on the Employees table (say EmployeeID 55) should have a matching (or could) record in the Eval table that would look like
EvalID: 12345
ReviewedEmployeeID: 55
SupervisorID: 55

which of course wouldn't happen as an employee wouldn't evaluate themself.

How do I handle the relationships for this properly?

Do I just not link the SupervisorID back to anything?

View 2 Replies View Related

Finding Designation Of An Employee On A Given Date

Jul 23, 2005

Hi all,I have two tablesCREATE TABLE [JEMP] ([EMPID] [int] NOT NULL ,[DESIGID] [int] NULL , -- CURRENT DESIGNATION OF EMPLOYEE[DOB] [smalldatetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [JPRO] ([PromoID] [int] IDENTITY (1, 1) NOT NULL ,[EmpID] [int] NOT NULL ,[EffectiveDate] [smalldatetime] NOT NULL ,[NewDesigID] [int] NOT NULL , -- PROMOTED TO DESIGNATION[DesigID] [int] NULL -- PROMOTED FROM DESIGNATION) ON [PRIMARY]GOINSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(1,1,'1962-03-11 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(2,25,'1980-10-7 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(3,8,'1978-04-05 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(4,7,'1962-07-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(5,22,'1973-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(6,55,'1971-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(7,11,'1973-09-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(8,22,'1975-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(9,22,'1977-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(10,23,'1984-07-11 00:00:00')INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(3,'2002-15-11 00:00:00',7,20)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(3,'2003-03-01 00:00:00',8,7)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(4,'2002-01-04 00:00:00',20,22)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(4,'2005-05-01 00:00:00',7,20)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(5,'2001-10-01 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(6,'2001-08-01 00:00:00',55,NULL)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(7,'2003-10-01 00:00:00',11,8)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(8,'2001-09-01 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(9,'2002-01-05 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(10,'2002-11-01 00:00:00',24,25)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(10,'2003-11-15 00:00:00',23,24)--I wish to find the designation of employee on given date by usingpromotion and master table . I am using the following query to get theresultselect isnull( ( select top 1 newdesigid from JPRO whereempid=1 and effectivedate<'anygivendate' order by effectivedate desc ), (select desigid from empmast where empid=1) )It did give the result but looking for better method to solve this.With regardsJatinder

View 2 Replies View Related

Deciphering Employee Shift Data

Feb 11, 2008

Here is my current challenge that I realize I could do a few different ways but nothing efficient or flexible comes to mind. Hoping one of you guys can help me out with an elequent efficient T-SQL script.

Employee workstate information in our system is stored by segment. Segment 1 cooresponds to the entire shift while segments greater than 1 coorespond to subsequent breaks during the shift (Segment 1). i.e.

SegmentNumber SegmentStart SegmentEnd

1 10:00am 06:00pm
2 12:00pm 13:00pm
3 03:15pm 04:00pm


What I would like to do is generate continuous nonoverlapping records. i.e.

SegmentNumber SegmentStart SegmentEnd

1 10:00am 12:00pm
2 12:00pm 13:00pm
3 13:00pm 03:15pm
4 03:15pm 04:00pm
5 04:00pm 06:00pm


*Notice End has changed to match start of first break and so on. Also records have been added to fill gaps between breaks during the shift.

What adds to the challenge is that the segment number for a given employee/report day can be 1 (meaning no breaks) to any number (lots of breaks). The segment start and end times can be any increment. In addition Breaks can be divided into paid time and unpaid time but I imagine given a solution to the above I could apply it to another level down on my own.

Thank you in advance for your help!

View 3 Replies View Related

How To Combine 2 Tables Employee And Holiday

Dec 20, 2007

how to
how to combine 2 tables but like this

table 1
table Employee on work
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1

98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
---------------------------------------
table 2

table Employee on holiday
------------------------
empid start_date end_date shift
-----------------------------------------
12345678 11/04/2007 31/04/2007 10

98765432 01/04/2007 19/04/2007 10

------------------------------------------
how to create a view that show me and combine the 2 tables
all month from first day of the month until the end of the month like this
-----------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
12345678 11/04/2007 10
12345678 12/04/2007 10
12345678 13/04/2007 10
12345678 14/04/2007 10
.................................
.................................... ...................add the missing date until the end of the month
12345678 31/04/2007 10



98765432 01/04/2007 10
.................................... ...................add the missing date from the start of the month

98765432 02/04/2007 10
................................
..............................
98765432 16/04/2007 10
12345678 17/04/2007 10
98765432 18/04/2007 1
98765432 18/04/2007 10
98765432 19/04/2007 10
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
---------------------------------------


TNX

View 5 Replies View Related

Transact SQL :: Employee Hierarchy In Server

Sep 9, 2015

I want to get the employee hierarchy like tree structure and reportes.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved