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.
*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.
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!!
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
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 ***
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.
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?
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')))
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.
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)
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
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.
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.
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?
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
------------------------------------------ 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
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.
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.
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'
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?
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.
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
