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
I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the
calculations.
Oracle
TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')
this is a sfar as I have got with the sql version
SQLSERVER2005
= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )
visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.
using:
VS 2005 BI Tools
SQLServer 2005
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);
Hi,
I m Maran. I am trying to write a SQL Query to retrieve the following report format. But I'm not sure how to go about it.
Input values:
Starting Date: 09/14/2007
End Date: 12/06/2007
Monthly Report :
Start Date - End Date - Number of companies
09/14/2007 - 09/30/2007 1
10/01/2007 - 10/31/2007 0
11/01/2007 - 11/30/2007 4
12/01/2007 - 12/06/2007 0
Please its very urgent, Plz do the needful help. Actually this same report format i was posted already and got some methodology, but its not satisfied my requirements :( :(
Used Table: CompanyHistorytrackTable
companyId changed_date
50198 2007-09-05 13:11:17.000
48942 2007-09-14 12:42:30.000
48945 2007-11-06 12:05:31.000
47876 2007-11-14 10:58:21.000
43278 2007-11-16 16:14:25.000
43273 2007-11-16 16:16:11.000
51695 2008-02-04 11:05:09.000
47876 2008-01-21 14:10:02.000
44604 2008-02-04 19:33:02.000
46648 2008-02-04 19:35:30.000
Manimaran.Ramaraj
Software Engineer
Aspire Systems
Chennai - 600 028
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.
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
Greetings;
I recieve a monthly phone bill on cd. I am trying to autoimport these CD's into a SQL server database with a DTS in MSSQL 2000. I have noticed on the bills every so often they change the structure of one of the ACCESS DB tables by one row or something small.
I am trying to figure out how I can test the structure of the database before trying to do the actual imports, I want to do this so someone else can actually do the imports as we get the bills.
Is this a possibility? Or am I going about this all wrong?
Thanks
nhas
I need to run a script on a monthly basis (e.g at midnight on the last day of the month) which selects all the records from that calendar month. I have the below so far however as each month has a different number of days in it this will not work well. Are there any date/time criteria I can use in my tsql statement which will only select records from that month no matter how many days are in the month? I obviously need to move away from manually updating the script each month depending upon how many days are in the month!
SELECT *FROM UserLogwhere logtime >= (getdate() - 30)order by logtime asc
cheers
marco
Here is example table and data
CREATE TABLE [dbo].[sales](
[date_value] [datetime]NOT NULL,
[monthly_total] [int] NOT NULL
)
insert into sales ( date_value, monthly_total)
[code]...
How can I create a query to get below result?
YearMonthMonthlySaleAccumulated Total
2012Jan110 110
2012Feb130 240
2012Mar30 270
2012Apr60 330
2012May10 340
[code]...
Hi All,
I am Maran. Am facing the problem to retrieve the following format of output using the sql query. Is it possible 2 solve this.. I tried this, but i am unable to.
Input values:
Start Date: 2/17/2008
End Date : 5/8/2008
Output Format:
2/17/08 - 2/29/08 (Partial Month) 12
3/1/08 - 3/31/08 (Full month) 0
4/1/08 - 4/30/08 (Full month) 22
5/1/08 - 5/8/08 (Full month) 10
I want the above format of the monthly report. I really could use some help on this. thanks.
~ Maran
Manimaran.Ramaraj
Software Engineer
Aspire Systems
Chennai - 600 028
The Database will hold 2 tables. One of those includes dates. They are joint by a constraint using an ID. What i got to do is, store the tables in a different schema named after the month the data was created. I will have to keep the original tables aktive because this should work while accessing the tables but can flush the data to keep the database small. So i would end up with 2 tables in 1 schema for every month and the productive that keeps track of the current bookings.
I would have access to the enterprise edition if that changes anything at all.
I need to create a report containing the headcounts of the employees for each month. The data I can retrieve from the database looks like this:
Name
StartEmp
EndEmp
tom
1/jan/05
15/mrt/05
dirk
1/jan/05
31/mrt/06
jan
1/feb/05
NULL
In order to get the right information for the report the information should be represented in the following way:
January 2005
February 2005
March 2005
"April" 2005
Tom
1
1
0,5
1
Dirk
1
1
1
1
Jan
0
1
1
1
HeadCount
2
3
2,5
3
(there should be more data in the 2nd table but it's a sliced representation of the data. Sliced by 2 report parameters: startdate (=1 January 2005) and enddate (=30 April 2005) )
I need some help about building up the query to aggregate the info from table1 to the format of table2.
Hi,
I have to set up a project in SQLServer Express that will allow the export of tables from multiple PC's running SQLServer Express each month.
These have to be loaded into a single identical database on SQLServer Express each month for combined reporting.
so its basicaly
insert update on PC
export
import overlaying last months data (handle dup keys from the other pc's etc)
report
I've had a look at the SQLServer replication docs and got confused....
So if anyone can point me at appropriate documentation, or suggest a good method for this it would be appreciated
Thanks
Hi,
I have to set up a project in SQLServer Express that will allow the export of tables from multiple PC's running SQLServer Express each month.
These have to be loaded into a single identical database on SQLServer Express each month for combined reporting.
so its basicaly
insert update on PC
export
import overlaying last months data (handle dup keys from the other pc's etc)
report
I've had a look at the SQLServer replication docs and got confused....
Can I do this with replication ?
So if anyone can point me at appropriate documentation, or suggest a good method for this it would be appreciated
Thanks
hi,
I am using timeseries algorithm.I am training my model like the following
Date StudId Perf
5/1/2005 001 99
5/10/005 001 97.6
6/1/2005 001 94
6/10/2005 001 99
6/30/005 001 96
10/1/2005 001 100
Like that.
I need prediction Output like following
Date StudId Perf
10/1/2005 001 99
11/10/005 001 97.6
12/1/2005 001 94
1/10/2006 001 99
... how to write prediction query for this.
Thanks
Karthik.
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.
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
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
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
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.
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
View 2 Replies View RelatedHere 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!
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
I want to get the employee hierarchy like tree structure and reportes.
View 7 Replies View Related
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
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'
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?
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 ....
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
I have a requirement in a payroll system to show the status of an employee on the first and last day of each pay period. The pay periods are pretty simple, 24 per year starting on the 1st and 16th of each month. The status effective date, however, can occur on any day, not just the first or last day of the pay period. Here's my DDL.
SET NOCOUNT ON;
declare @EmployeeStatus TABLE (
[EmployeeID] [int] NOT NULL,
[EmployeeStatus] [varchar](24) NOT NULL,
[EffectiveDate] [date] NOT NULL
[Code] .....