October 19, 2015 at 4:00 am
I have a HR system with Fingerprint, I need to make Stored Procedure to give me average of employees who is come before specific time like 8:20 in period of time ,like from date to date.
October 19, 2015 at 4:11 am
Please can you post create table, sample data and expected outcomes?
Please see the posting code and data link in my signature for help.
**Very Crude Example**
create table #fingerprint
(
id int identity(1,1),
empid int,
scandatetime datetime
)
insert into #fingerprint
values
(1,'2015-10-01 05:36:15'),
(2,'2015-10-01 06:36:15'),
(3,'2015-10-01 07:36:15'),
(4,'2015-10-01 08:36:15'),
(5,'2015-10-01 09:36:15'),
(6,'2015-10-01 10:36:15'),
(7,'2015-10-01 11:36:15'),
(8,'2015-10-01 12:36:15'),
(9,'2015-10-01 13:36:15'),
(10,'2015-10-01 14:36:15'),
(1,'2015-10-02 14:36:15'),
(2,'2015-10-02 14:36:15'),
(3,'2015-10-02 14:36:15'),
(4,'2015-10-02 08:36:15'),
(5,'2015-10-02 07:36:15'),
(6,'2015-10-02 07:36:15'),
(7,'2015-10-02 10:36:15'),
(8,'2015-10-02 12:36:15'),
(9,'2015-10-02 19:36:15'),
(10,'2015-10-02 04:36:15'),
(1,'2015-10-03 10:36:15'),
(2,'2015-10-03 06:10:15'),
(3,'2015-10-03 06:36:15'),
(4,'2015-10-03 06:36:15'),
(5,'2015-10-03 06:36:15'),
(6,'2015-10-03 07:36:15'),
(7,'2015-10-03 07:36:15'),
(8,'2015-10-03 07:36:15'),
(9,'2015-10-03 08:36:15'),
(10,'2015-10-03 03:36:15')
select
count(*) as EmployeeCount,
CONVERT(DATE, scandatetime)
from
#fingerprint
where
convert(time,scandatetime) < '08:20:00'
group by CONVERT(DATE, scandatetime)
select
avg(EmployeeCount)
from
(select
count(*) as EmployeeCount,
CONVERT(DATE, scandatetime) as dt
from
#fingerprint
where
convert(time,scandatetime) < '08:20:00'
group by CONVERT(DATE, scandatetime)) as d
From the above, the two result sets are
EmployeeCount | Date
3 | 2015-10-01
3 | 2015-10-02
8 | 2015-10-03
or
Average
4
Which is right for your problem?
October 19, 2015 at 4:31 am
That is what i do ,but i need to create fix SP ,give it Time ,from date and after date the give me the Average,
By the way,The table have a check in finger print and check out finger print per EMP per day.
Thanks
October 19, 2015 at 4:35 am
OK, please give definitions of your tables, some sample data to work with and what you want the outcomes to be.
Please also give what you have got so far to complete the task so we can see where your going with solving the problem.
October 19, 2015 at 4:46 am
Table name is "Transaction"
SELECT [TransactionID]
,[EmployeeID]
,[DateTime]
,[Type]
,[OrderType]
,[OrderNo]
,[OrderDate]
,[LocationID]
,[IsProcessed]
,[Manager]
,[CreatedOn]
,[ModifiedBy]
,[ModifiedOn]
,127.0.0.1
,[PCName]
,[Notes]
,[TypeUsed]
,[GroupKey]
,[IsEnquiryTransaction]
FROM [dbo].[Transaction]
i need specific time as variable to could calculate the average of Employees check in before it and average of employee check in after this time
with in specific DATE
Thanks
October 19, 2015 at 4:48 am
Please read this link http://www.sqlservercentral.com/articles/Best+Practices/61537/
Could you then re-post the table definition, sample data and expected outcome?
October 19, 2015 at 5:10 am
I uploaded as attachment file ,image from used table and output of it as text file.
October 19, 2015 at 5:34 am
There are no attachments on any of your posts.
Images are no good, we need the code which is in a easily consumable format, like the code I posted earlier
October 19, 2015 at 5:42 am
I attach sample file output file but i don't know where ,
You couldn't create SP from description of table which writes before ???
October 19, 2015 at 5:57 am
Yes I could write a stored proc, but it wont answer your problem.
CREATE PROCEDURE MyProc
AS
BEGIN
SELECT * FROM [dbo].[Transaction]
END
GO
I don't know what your column types are, I don't know what your data looks like, I don't know how your results should look like.
Please re-read the article I posted and post back in a consumable format (like my first post with the code snippets) as your more likely to get an answer to your problem if we can re-create your problem.
October 21, 2015 at 3:46 am
CREATE TABLE [dbo].[Transaction](
[TransactionID] [uniqueidentifier] NOT NULL,
[EmployeeID] [bigint] NOT NULL,
[DateTime] [datetime] NOT NULL,
[Type] [nvarchar](max) NOT NULL,
[OrderType] [nvarchar](2) NULL,
[OrderNo] [nvarchar](50) NULL,
[OrderDate] [datetime] NULL,
[LocationID] [int] NOT NULL,
[IsProcessed] [bit] NOT NULL,
[Manager] [nvarchar](max) NULL,
[CreatedOn] [datetime] NULL,
[ModifiedBy] [nvarchar](max) NULL,
[ModifiedOn] [datetime] NULL,
127.0.0.1 [nvarchar](max) NULL,
[PCName] [nvarchar](max) NULL,
[Notes] [nvarchar](max) NULL,
[TypeUsed] [nvarchar](2) NULL,
[GroupKey] [uniqueidentifier] NULL,
[IsEnquiryTransaction] [bit] NULL,
CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED
(
[TransactionID]
That is what you need ???
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply