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
ADVERTISEMENT
Nov 22, 2006
We are experiencing problems in presenting Unicode data stored in MSSQL as Shift-jis format in ASP application.
In MSSQL we are storing kanji text in database with a field type as nchar. Other application loads these tables using codepage 932. In MSSQL data is stored in Kanji correctly.
In ASP application, when we are trying to read data from these tables, Kanji characters are coming as '?????'. We are using shift-jis character set in ASP.
Templates for field and column header uses shift-jis, but a record set coming from MSSql is Unicode.
Is there a way to convert from Unicode to Shift-jis in MSSql or in ASP .
Please help!!
View 1 Replies
View Related
Jul 23, 2005
I asked the DBA to start the Server with options -T1204 and -T3605 andhere's what I get. I need help deciphering this. This happens when wehave 5 usrs performing concurrent actions and for the life of me cannotdecipher this. WHat is a Node, what is the branch ?WHat are the ECIDs. I cannot even easily find out what process blockswhat. Does anyone have any experience with these. ANy and all help willbe appreaciated. I have posted the output of one of the many deadlocksencountered. ThanksDeadlock encountered .... Printing deadlock information2005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid4 Wait-for graph2005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid4 Node:12005-01-11 08:49:59.49 spid4 Port: 0x42bf0280 Xid Slot: 0, EC:0x46d09590, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket2005-01-11 08:49:59.49 spid4 SPID: 61 ECID: 0 Statement Type:SELECT Line #: 6512005-01-11 08:49:59.49 spid4 Input Buf: RPC Event:Procedure_GetLatestValues;12005-01-11 08:49:59.49 spid4 Coordinator: EC = 0x46d09590, SPID:61, ECID: 0, Not Blocking2005-01-11 08:49:59.49 spid4 Consumer List::2005-01-11 08:49:59.49 spid4 Consumer: Xid Slot: 0, EC =0x46d09590, SPID: 61, ECID: 0, Not Blocking2005-01-11 08:49:59.49 spid4 Producer List::2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 1, EC =0x4732c098, SPID: 61, ECID: 6, Blocking2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 2, EC =0x46824098, SPID: 61, ECID: 5, Blocking2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 3, EC =0x47296098, SPID: 61, ECID: 7, Blocking2005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid4 Node:22005-01-11 08:49:59.49 spid4 PAG: 7:1:405367CleanCnt:3 Mode: IX Flags: 0x22005-01-11 08:49:59.49 spid4 Grant List 1::2005-01-11 08:49:59.49 spid4 Grant List 3::2005-01-11 08:49:59.49 spid4 Owner:0x4ed4b8e0 Mode: IXFlg:0x0 Ref:1 Life:02000000 SPID:64 ECID:02005-01-11 08:49:59.49 spid4 SPID: 64 ECID: 0 Statement Type:UPDATE Line #: 12005-01-11 08:49:59.49 spid4 Input Buf: RPC Event:Standard_InsertNamePair;12005-01-11 08:49:59.49 spid4 Requested By:2005-01-11 08:49:59.49 spid4 ResType:LockOwner Stype:'OR' Mode:S SPID:61 ECID:6 Ec:(0x4732C098) Value:0x4aafe660 Cost:(0/A6C)2005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid4 Node:32005-01-11 08:49:59.49 spid4 PAG: 7:1:405373CleanCnt:1 Mode: IX Flags: 0x22005-01-11 08:49:59.49 spid4 Grant List 1::2005-01-11 08:49:59.49 spid4 Owner:0x4ac00160 Mode: IXFlg:0x0 Ref:1 Life:02000000 SPID:61 ECID:02005-01-11 08:49:59.49 spid4 Requested By:2005-01-11 08:49:59.49 spid4 ResType:LockOwner Stype:'OR' Mode:U SPID:64 ECID:0 Ec:(0x472F3590) Value:0x46c24a40 Cost:(0/A38)2005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid4 -- next branch --2005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid4 Node:12005-01-11 08:49:59.49 spid4 Port: 0x42bf0280 Xid Slot: 0, EC:0x46d09590, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket2005-01-11 08:49:59.49 spid4 Coordinator: EC = 0x46d09590, SPID:61, ECID: 0, Not Blocking2005-01-11 08:49:59.49 spid4 Consumer List::2005-01-11 08:49:59.49 spid4 Consumer: Xid Slot: 0, EC =0x46d09590, SPID: 61, ECID: 0, Not Blocking2005-01-11 08:49:59.49 spid4 Producer List::2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 1, EC =0x4732c098, SPID: 61, ECID: 6, Blocking2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 2, EC =0x46824098, SPID: 61, ECID: 5, Blocking2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 3, EC =0x47296098, SPID: 61, ECID: 7, Blocking2005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid4 Node:62005-01-11 08:49:59.49 spid4 PAG: 7:1:405367CleanCnt:3 Mode: IX Flags: 0x22005-01-11 08:49:59.49 spid4 Wait List:2005-01-11 08:49:59.49 spid4 Owner:0x4aafe660 Mode: SFlg:0x0 Ref:1 Life:00000000 SPID:61 ECID:62005-01-11 08:49:59.49 spid4 Requested By:2005-01-11 08:49:59.49 spid4 ResType:LockOwner Stype:'OR' Mode:S SPID:61 ECID:5 Ec:(0x46824098) Value:0x4edc4d80 Cost:(0/A6C)2005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid4 -- next branch --2005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid4 Node:12005-01-11 08:49:59.49 spid4 Port: 0x42bf0280 Xid Slot: 0, EC:0x46d09590, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket2005-01-11 08:49:59.49 spid4 Coordinator: EC = 0x46d09590, SPID:61, ECID: 0, Not Blocking2005-01-11 08:49:59.49 spid4 Consumer List::2005-01-11 08:49:59.49 spid4 Consumer: Xid Slot: 0, EC =0x46d09590, SPID: 61, ECID: 0, Not Blocking2005-01-11 08:49:59.49 spid4 Producer List::2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 1, EC =0x4732c098, SPID: 61, ECID: 6, Blocking2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 2, EC =0x46824098, SPID: 61, ECID: 5, Blocking2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 3, EC =0x47296098, SPID: 61, ECID: 7, Blocking2005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid4 Node:92005-01-11 08:49:59.49 spid4 PAG: 7:1:405367CleanCnt:3 Mode: IX Flags: 0x22005-01-11 08:49:59.49 spid4 Wait List:2005-01-11 08:49:59.49 spid4 Owner:0x4aafe660 Mode: SFlg:0x0 Ref:1 Life:00000000 SPID:61 ECID:62005-01-11 08:49:59.49 spid4 Requested By:2005-01-11 08:49:59.49 spid4 ResType:LockOwner Stype:'OR' Mode:S SPID:61 ECID:7 Ec:(0x47296098) Value:0x42bef7c0 Cost:(0/A6C)2005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid4 -- next branch --2005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid42005-01-11 08:49:59.49 spid4 Node:12005-01-11 08:49:59.49 spid4 Port: 0x42bf0280 Xid Slot: 0, EC:0x46d09590, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket2005-01-11 08:49:59.49 spid4 Coordinator: EC = 0x46d09590, SPID:61, ECID: 0, Not Blocking2005-01-11 08:49:59.49 spid4 Consumer List::2005-01-11 08:49:59.49 spid4 Consumer: Xid Slot: 0, EC =0x46d09590, SPID: 61, ECID: 0, Not Blocking2005-01-11 08:49:59.49 spid4 Producer List::2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 1, EC =0x4732c098, SPID: 61, ECID: 6, Blocking2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 2, EC =0x46824098, SPID: 61, ECID: 5, Blocking2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 3, EC =0x47296098, SPID: 61, ECID: 7, Blocking2005-01-11 08:49:59.49 spid4 Victim Resource Owner:2005-01-11 08:49:59.49 spid4 ResType:LockOwner Stype:'OR' Mode: USPID:64 ECID:0 Ec:(0x472F3590) Value:0x46c24a40 Cost:(0/A38)2005-01-11 08:50:15.11 spid4
View 5 Replies
View Related
Jun 27, 2014
I have a table history of Employee data.
id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to
View 4 Replies
View Related
Mar 11, 2015
I have table like :
emp
eno ename deptnno sal
1 a 10 50000
2 b 20 100000
3 c 20 150000
4 d 30 200000
5 e 30 2500000
Here how can I get all employee details excluding junc datas?
View 1 Replies
View Related
Dec 2, 2006
Hello there,I am having a small problem which been challenging me for few days andneed help or advice.I am trying to calculate the day-shift for employees based on the timethey started and finish working, I will only have 2 shifts 1 or 2 .Shift one changes based on the location however any thing else is shift2. The problem I am having is when someone signed in after midnight; Ineed to report his time under shift 2 for the previous day date. So ifhe signs at 12:30 AM on 12-12-2006, I need to report that as shift 2 on12-11-2006 and that’s where my problem is. Is there a way to subtractthe date by 1. I am using SQL Server and here is a simplified tables Iam working with:Employee tableEmployeID LocationID StartTime EndTime123 555 11:00:00 AM 3:00:00 PM183 559 7:00:00 AM 11:00:00 AM…Shift tableShiftNumber LocationID StartTime EndTime1 555 7:00:00AM 2:00:00PM2 555 2:00:00PM 12:00:00AM1 559 6:00:00AM 4:00:00PM…..So I am trying something likeCASE WHEN (select ShiftNumber FROM Shift WHERE LocationID=TR.LocationIDAND(TR.StartTime>=StartTimeANDTR.EndTime<= EndTime)) =1 THEN 1ELSEWHEN (select ShiftNumber FROM Shift WHERE LocationID=TR.LocationIDAND(TR.StartTime>=StartTimeANDTR.EndTime<= EndTime)) =2 THEN 2ELSE?????????????? NEED HELP HERE WHEN EMPLOYEE SIGN AFTER MIDNIGHTEND)FROMEmployee TRINNER JOIN Shift ON LocationID = TR.LocationID*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies
View Related
Dec 27, 2007
Hello,
I have a BIDS project with 14 reports, all formatted the same way. On one report, any text box or image that is placed in the center or right of center in the form, shifts very far to the right of the tables and charts when it's rendered.
The text box placed at the top left of the form stays put. This is really wierd.
Has anyone ever heard of anything like this.
I have a couple of screen shots if you have a place I can put them.
Thanks
Dan
View 3 Replies
View Related
Feb 6, 2007
The default behaviour for pressing SHIFT + SPACE when editing text inside a cell in the Table Editor view in Microsoft SQL Server Management Studio Express is to change focus to select all cells comprising the current row. This behaviour lends itself to the default behaviour for pressing SHIFT + {LEFT | RIGHT | UP | DOWN } in changing the focus to select adjacent cells.
However, I find this is quite annoying as the SHIFT + SPACE combination is quite a common miss-type when entering text in UpperCaps or when entering text with spaces while holding the SHIFT key.
Anyone else have this gripe with MSSMSE? Anyone know how to disable this behaviour?
Simon
View 2 Replies
View Related
Apr 10, 2006
for the record, this is my first post and i'm not sure this is the right place to post, but it makes sence to me.
i've been spending a great deal of time with our new MS SQL server and love it but i've noticed there's a fairly large diff between the SQL i'm used to seeing in MS Access and the SQL that the server uses. as you can see by my below statement i'm starting to get the hang of this but i still run into new issues...
my question... what's wrong with my IIf statements? this is what the server spits out...
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'LIKE'.
[ACCPAC_OESHDT].[TERRITORY] is an nvarchar(6) and contains data such as '1', '2', '3', etc...
i've tried many many variants and always have the same outcom. i've tried casting as an int, rtrim to rid the value of any possible white space, expanded versions.
oh, almost forgot... my environment is... I open the SQL Management Studio then logon to SQL Database Engine and then create a New Query.
i'm stuck, please advise. ;-)
SELECT
ACCPAC_OESHDT.YR,
ACCPAC_OESHDT.PERIOD,
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[1-4]','Group A',
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[5-7]','Group B',
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[89]','Group C','_Unknown Territory'))) AS TERRITORY,
SUM(CAST([SAMTSALES] AS MONEY)) AS Sales
FROM ACCPAC_OESHDT
GROUP BY
ACCPAC_OESHDT.YR,
ACCPAC_OESHDT.PERIOD,
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[1-4]','Group A',
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[5-7]','Group B',
IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[89]','Group C','_Unknown Territory')))
HAVING ((SUM(CAST([SAMTSALES] AS MONEY)))>0);
View 7 Replies
View Related
May 17, 2015
I have the following shift table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
[code]...
I need to identify which shift is currently running on by providing the current time.I used the following query to get the result. It is giving correct result for Morning & Afternoon Shift but failed to produce result for Night Shift.
SELECT ShiftName, Shiftid
FROM WHShifts
WHERE DATEPART(HOUR, @Currenttime)>=(DATEPART(HOUR,CAST(ShiftInTime AS Datetime))) AND DATEPART(HOUR, @Currenttime)<=(DATEPART(HOUR,CAST(ShiftOutTime AS Datetime)))
Is there any way to find out the night shift from a given time.
View 15 Replies
View Related
Jul 13, 2015
We are maintaining 3 Shifts in our database. Problem in maintaining date and time for 3rd Shift. For example, today date is 13th July and third shift timing is 11 PM - 7 AM. Then I have to display the beginning date as 13/07/2015 11 PM and end date as 14/07/2015 7 AM. Please find the data(in seconds) available in database which I need to use for my calculation.
Date(Fcreacion)
Start time in Seconds(Hcreacion)
End time in seconds(Hcerrar)
turno(Shift)
[code]...
View 3 Replies
View Related
Nov 9, 2015
I am doing some analysis on shifts and what I want to do is say if the time from a timestamp field is between 00:00 and 05:59 then the figures belong to the day before. Eg. We have a night shift that work 22:00 - 06:00.
Here is my coding which logically looks sound to me but when I run it, it has an error:
SELECTe.ID,
CAST(e.TimestampasDate)asEventDate,
CAST(e.TimestampasTime)asEventTime,
CASE
WHENCAST(e.TimestampasTime)BETWEEN'00:00:01'and'05:59:59'
THENCAST(e.TimestampasDate)-1
ELSECAST(e.TimestampasDate)
END
FROMIntouch.Event
View 5 Replies
View Related
Apr 27, 2006
My application has a log table with a timestamp field which represents the time when the record was inserted.
We would like query the the table and group the results into date units based on the value of the timestamp.
The grouping specification is held in another table but only specified for a single week.
For example
WeekDay Start End
1 06:00:00 14:00:00
2 08:10:00 17:00:00
What this specification means is that when the log table is queried records with a timestamp that fall on a Monday between 06:00:00 and 14:00:00 should be grouped together, on Tuesday the group is records between 08:10:00 and 17:00:00 and so on...
The only way I can think of doing this is to generate a temporary table when the log table is queried that has a unique record for each time period for the span of the query and then attempt to join this table to the log table using the timestamp and then group based on the temporary table unique ID.
I'm not sure how to generate such a temporary table from the specification table so any help would be appreciated.
Thanks in advance.
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
Sep 9, 2015
I want to get the employee hierarchy like tree structure and reportes.
View 7 Replies
View Related
Mar 11, 2008
This seems simple enough, I'm trying to get a count of employees by different indicators. First total by job code, second by Union_Code.
So I'm looking to count every one by distinct job codes inside a distinct Union_code first, then total the number for that distinct union.
Any help would be appreciated.
SELECT emp.job_code,
tbljob.job_title,
emp.union_code
FROM emp
INNER JOIN
empjob ON empjob.emp_id = emp.emp_id
AND empjob.company = emp.company
AND empjob.job_code_id = 'PRIM'
INNER JOIN
tbljob ON tbljob.job_code = empjob.job_code
AND tbljob.company = empjob.company
WHERE (emp.company = 'ABC')
ORDER BY emp.union_code, empjob.job_code
View 5 Replies
View Related
Mar 13, 2007
Greetings,
I have a very wide report of more than 20 inches. I've placed several parameter values in the report header section so that the user can see what filters have been applied to the data. The testboxes shift their position several inches to the right when the report is run from the Report Manager.
Is there a way to make sure that a textbox is displayed at an absolute position? I thought maybe there would be a property on the report or body object that controls this but I don't see one.
Thanks for your help,
BCB
View 7 Replies
View Related
Jul 24, 2006
Hi, I am new to ASP and SQL. I have a sql database that has two tables. One is Employee Table and the other is Ticket Table and they are joined on EmployeeID.
Employee Table:
EmployeeID, FirstName, Lastname,
Tickets Table:
TicketID, EmployeeID, Status,Priority
I need a SQL statement that will list employee with the number of tickets he has open. For Example:
John Doe (10) . This list employee John Doe with 10 Tickets that have a status of Open.
I have tried count(*) , Max and everything I could find on web but I can not put the two together. I can get the total number of tickets that have a status of Open, I can even do a distinct statement that will show all employee's, however I can not figure out how to retreive both at the same time for each employee. Please help.
View 3 Replies
View Related
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
View Related
Apr 8, 2006
Dear Friends,
I am in problem & have to solve one query.
I have a one table with the employee time in & time out data, employee can go out & come in fequently in a day.
I want to know that how much time every emp have attend in the company per day.
Kindly, do reply as soon as possible.
I am enclosing data defination in txt file along with the data in the MS Excel file.
Thanx ....
View 7 Replies
View Related
Apr 3, 2014
I'm working on a project where I need to retrieve employees data and then combine the data into single row per employee.
Sample Data:
WITH SampleData (PERSON, [DATA], [FIELD]) AS
(
SELECT 1234,'04/02/2014','Date'
UNION ALL SELECT 1234,'123','Department'
UNION ALL SELECT 1234,80.0,'Rate'
)
SELECT *
FROM SampleData;
The results from the above are as follows:
PERSONDATA FIELD
123404/02/2014Date
1234123 Department
123480.0 Rate
The desired results would be:
PERSONDate Department Rate
123404/02/2014 123 80.0
View 7 Replies
View Related
