Finding Designation Of An Employee On A Given Date
Jul 23, 2005
Hi all,
I have two tables
CREATE TABLE [JEMP] (
[EMPID] [int] NOT NULL ,
[DESIGID] [int] NULL , -- CURRENT DESIGNATION OF EMPLOYEE
[DOB] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
CREATE 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]
GO
INSERT 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 using
promotion and master table . I am using the following query to get the
result
select isnull( ( select top 1 newdesigid from JPRO where
empid=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.
Hi, I have been struggling trying to design a query that will alow be to select the most recent date in a table and I'm obviously not having much luck
This is basically the table layout, note each employee can have multiple rows with different dates
Employee_ID
Last_Name
First_Name
Evaluation_Date
Evaluation_Score
1
Jones
Tom
01/04/07
40
1
Jones
Tom
01/.12/07
50
1
Jones
Tom
04/01/08
60
2
Smith
Ed
02/14/05
70
2
Smith
Ed
03/18/06
80
3
Brown
John
06/23/04
80
3
Brown
John
12/23/04
79
3
Brown
John
01/07/06
50
3
Brown
John
10/22/08
69
What I'd like to do would be to write some thing that would return the following, just the last date of the evaluation & whatever relevant data is in the table
Employee_ID
Last_Name
First_Name
Evaluation_Date
Evaluation_Score
1
Jones
Tom
04/01/08
60
2
Smith
Ed
03/18/06
80
3
Brown
John
10/22/08
69
I've looked at select distinct and the date operatives with out any success.
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30 208203492013-09-032014-09-01 308203492012-09-042013-09-02 408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
I'm setting up an ASP.NET application using SQL Server 2000 which will be used by hundreds of clients. Among the tables in the database, each client should only have access to his own records.
Here's where I'm stuck...
Should I
1. Create a separate database for each client and duplicate the table set. In this case, the connection string would be slightly different based on the client logging in (i.e. the DB name will change, but that should be it). While all tables would contain a fairly small amount of records (e.g. 15,000 - 250,000), this would require several hundred databases, one for each client, on the server. Note, as clients come and go, DBs would have to be deleted / created on the fly programmatically.
or...
2. Create a single database and in each table have a field which uniquely identifies the client (e.g. clientId)? This would require that all queries include a "clientId" specification so as to retrieve/update/delete the proper rows.
I know that some tables will contain several million records in total across the hundreds of client. Others will contain perhaps only 100,000 in total.
I am unaware of the performance issues in SQL Server 2000 and wanted to get advice on this issue.
I do figure SQL Server 2000 will tackle large datasets, but is it better to keep all data within a single database with large numbers of records, or separate them as indicated above?
the requirements are to return person ID with the most recent leaving date from a scheduled dept, who has previously arrived at an unscheduled dept within 7 days, but just now my query shows all the previous leaving dates within 7 days of that unscheduled dept arrival, I only want the last leaving date before the arrival at the unscheduled dept:
So for instance looking at a copy of one person's date below I have:
PersonID Last Dept Arrival Date To Last Dept Leaving Date From Last Dept Next Arrival Date to Unscheduled Dept ======== ================= ========================= =========================== ===================================== 0106573268Dept 5 2013-03-01 2013-03-03 2013-03-05 0106573268Dept 6 2013-02-27 2013-02-27 2013-03-05 0106573268dept 2 2013-02-26 2013-02-26 2013-03-05
In the data above I only want to return the first row, which is the most recent leaving date before arrival at an unscheduled dept.
My query is much the same as before except my inline view is looking at the data for last scheduled leaves from depts in my inline view and also the outer query returning all arrivals to the unscheduled dept:
SELECT b.personID ,b.dept AS "Last leaving dept" ,b.arrival_Date as "arrival Date To Last dept" ,b.leaving_Date AS "leaving Date From Last dept", a.[arrival Date] as "Next arrival Date to AREA_GH" FROM Unscheduled_Arrival a INNER JOIN (SELECT * FROM scheduled_Leaves where [leaving date] is not null) b ON a.Person_ID = b.Person_ID
We received a Payment from a customer on '10/10/2007 10:30:00'. i am trying to calculate the commission we would receive from that payment. the commission rate can be edited. so i have to find what the commission rate was when that payment was received.
I have a CommisionAudit table that tracks changes in commission rate with the following values.
ID | Commission Change | UpdatedOn ---------------------------------------------- 1 | Change from 20->25 | 03/07/2007 09:00:00 ---------------------------------------------- 2 | Change from 25->35 | 10/09/2007 17:00:00 ---------------------------------------------- 3 | Change from 35->20 | 01/10/2007 16:00:00 ---------------------------------------------- 4 | Change from 20->26 | 11/10/2007 10:00:00 ----------------------------------------------
with this payment, as the commission rate had been changed on 01/10/2007 it would obviously be 20%(ID 3). But I need to write sql to cover all eventualities i.e. Before the first and after the last. any help would be most welcome.
Is there a data type in SQL Server for entering hyperlinks to websites? MS Access has a hyperlink data type but I can't find a corresponding SQL Server data type which I can use.
I have a table containing several date fields in which certain events happened, and I need to be able to find the expiration date for renewal. The expiration dates are at the end of the 6th, 12th, or 24th month after the event occurred, depending on the event.
Is there a way to return in a query the last day of month x following a date field? For instance, if the date '3/12/2006' is stored in the field for a 12-month expiration, I need to return '3/31/2007'.
I want to find out the earliest [First_Post_Date] for any parentdid
My query (See below) Produces the following results SELECT ParentID ,[First_Post_Date] ,[FDMSAccountNo] FROM [FDMS].[dbo].[Dim_Outlet] where ParentID = '878595212886' Order by ParentID desc
I wanted to determine the date of the last Saturday with respect to the current date so that I could create a temp table with date ranges numbered 1-4 e.g. up to that date, post link below: URL....I now want to make this function that any time I pass it a day name (mon-sun) it will calculate the date of the previous (mon-sun) with respect to the current day. This date will then be the last date in my range.
So for example I run the function and pass it 'Friday' then I want last date in my range to be the 25th of July (today being Friday so need last Friday), I pass it Thursday I want it to give me the 31st of July etc. I need last date of that date but if I call Friday and today is Friday then need last friday; I think this is the trickier part of it.
This calculation I would image would need to use a combination of datediff dateadd etc. but really don't know how to do this dynamically.Currently I have a function that I pass the last date for the date range I want for my weeks range, I also give it the numbere of weeks I want in my range and also pass the date from the query that I want to know what week range it is in.
ALTER FUNCTION [dbo].[daterangeweeknumber] ( -- Add the parameters for the function here @refDate datetime, @Weeks INT, @lastdate datetime
]code]....
But now I want just to be able to pass it the day name - in this instance would be 'Wednesday' and want it to return date ranges with last date as Wed the 30th of July. Reason I want to use day name is that people will be running these reports on a certain day - for instance on Wednesday and last day to be Monday - and this will mean they do not have to keep changing the date in the call to function.
Hi,Can anyone help please?select notefield, modifiedonFROM Table1WHERE id = '100426' and(statusfield like '%criteria1%' ORstatusfield like '%criteria2%')Produces a list of records based upon the criteria. I would like to beable to only show the newest dated record, from the modifiedon field.I've tried max(modified) on, but as I am using an aggregate function inthe query I have to use GROUP BY, which notefield does not like as thisis a ntext field.(I get:Server: Msg 306, Level 16, State 2, Line 1The text, ntext, and image data types cannot be compared or sorted,except when using IS NULL or LIKE operator.)Any ideas please?
Is there a way to find out when the last modification or change to data in a table occured ? How about the last change of a view ?
We'd like to be able to extract data to another table based on a view. Then, at a later date, check to see if the first table or view has changed since the last extract. If not, then we don't need to re-extract the data, we know it hasn't changed.
Aim – Find the latest #Account.[Last_Post_Date] for the Fdmsaccountno and populate the latest date for that Parentid. In a new column Called “Parent Last Post Date"
Desired results FdmsaccountnoParentidLast_Post_DateParent Last Post Date 8780201218848782333428892014010520140105 8780201278818782333428892014010320140105 8780201288898782333428892013123120140105 8787018098808782333428892013121720140105 8780201208868782333428892013120420140105 8782333428898782333428892013120120140105 8787018088828782333428892013113020140105 8780201158868782333428892013112020140105 8787570508848782333428892013101120140105
My query is
select #Account.Fdmsaccountno, #Account.Parentid, #Account.[Last_Post_Date], from #Account inner join [Dim_Outlet] on #account.FDMSAccountNo = [Dim_Outlet].FDMSAccountNo where #Account.Parentid = '878233342889' order by [Last_Post_Date] desc
Hi, I am using SQL Server 2000 and have the following questions:
1. How do I know the last updated (data) date using system objects or any other method? 2. How do I know the last modified date of a table using system objects or any other method? 3. How do I know when a table is last accessed
I have two columns in my table. Both the columns contains datetime datatypes. I need to write a stored procedure which will calculate the date difference between the two columns and if it exceeds more than 5 days then that record should get deleted. How to do it?Thanx
I know I am missing something basic, here. I have a date field in a table. The field is 'allowed Nulls'. When a certain thing happens in the program, the date is filled in. At various times, I need to do a query to find all the rows that have no dates entered.
What do I use in my where clause? SQL server does not like 'where date = null.'
I have found a lot of examples of problems where I have just a single date column, and then I find the gaps in between that, but I'm having difficulty finding examples where it works with start and end date columns...
In my query I have a date field and using that date field I want to pass the date to a function, or whatever is most appropriate, then compare that date with a range of the last 4 weeks, which will be numbered 1-4, 1 being earliest and 4 being latest, and then return the week number.
I've determined I need some sort of look up but will need to be dynamic - thinking maybe a temp table, first using the date the report is run on and counting 28 days back from the most recent last saturday then setting number to 1 for first 7 days then 2 for next 7 days etc.
I have a table of employee assignments that I'm narrowing down to a specific group. Employees have multiple assignments (job positions essentially) and each has start and end dates. I need to identify the date range(s) that are covered by the assignments in my data set. Here's an example of data for one person ID. NULL in the end_date just means it is an active assignment. The pos_id column isn't necessary, but it define the data I'm looking at. The end result won't use it.
In this case I want results to say that ID 999 has a range from 9/2/2011 to NULL. There are no gaps in the date range. Or to say it differently, there's always an assignment starting the next day after an end_date. Or an assignment that overlaps the end and beginning of another assignment.
Here's another example where there is a gap in the ranges.
There would be 2 result rows for this with a range from 2011-09-02 to 2013-01-06 and a second for 2013-09-01 to NULL.
The end result would be to have a row per date range for every ID. I've written a script that will find the ranges but it is a painful RBAR solution that takes forever to run. Every different path I've gone down trying to solve it ends in evaluating row by row grouped by the Person ID. The assignments are not always continuous, so I can't use a MAX and MIN and there may be 1 or more gaps in the dates to account for.
I'm trying to find if any part of a date range in my table between orig_start_rent and stop_rent falls within a period I specify in two variables: startPeriod and endPeriod
For example if I specify 2013-11-01 as startPeriod and 2013-11-30 as endPeriod, then if any part of the date range between orig_start_rent and stop_rent(stop-rent can be null if hire is open) falls within that period, I want that to be picked up and assigned the value of 1 in my case statement for OnHire. My code is not picking everything up however - using the example above, a record with orig_start_rent of 2013-05-23 and stop_rent of 2013-11-18 is being assigned 0 when it should be 1. My code here:
declare @startPeriod as smalldatetime declare @endPeriod as smalldatetime set @startPeriod = '2013-11-01' set @endPeriod = '2013-11-30'; select dticket, orig_start_rent, stop_rent, case when orig_start_rent >= @startPeriod and orig_start_rent <= @endPeriod then 1 when orig_start_rent < @endPeriod and stop_rent is null then 1 else 0 end [OnHire] from deltickitem
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);
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.
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
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.
