June 27, 2013 at 2:26 am
SIR I AM REALLY SORRY I DONT HAVE AN OPTION OF SCRIPT DATA I JUST SCRIPT MY TABLE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ATTEND_LOG]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ATTEND_LOG](
[EID] [int] NULL,
[date] [datetime] NULL,
[timein] [datetime] NULL,
[timeout] [datetime] NULL,
[BID] [int] NULL,
[EBID] [int] NULL,
[spendtime] [datetime] NULL,
[excessshort] [datetime] NULL,
[excess] [nvarchar](50) NULL,
[remarks] [varchar](50) NULL,
[SHIFT] [varchar](50) NULL
) ON [PRIMARY]
END
immad
June 27, 2013 at 2:46 am
usE Narayana Vyas Kondreddi's stored procedure sp_generate_inserts http://vyaskn.tripod.com/code/generate_inserts.txt in a SQL Server database.
Use this proc to generate the insert statement
June 27, 2013 at 3:13 am
sir here it is just copy and paste it into your sql server
create TABLE atend_log
(
eid int,
date datetime,
timein datetime,
timeout datetime,
spendtime datetime,
excessshort datetime,
remarks varchar(50)
)
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-01 00:00:00.000','2013-06-19 08:39:00.000','2013-06-19 18:04:00.000','1900-01-01 09:25:00.000','1900-01-01 00:25:00.000','Late')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-02 00:00:00.000','2013-06-19 08:42:00.000','2013-06-19 09:56:00.000','1900-01-01 08:50:00.000','1900-01-01 00:10:00.000','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-02 00:00:00.000','2013-06-19 10:09:00.000','2013-06-19 12:23:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-02 00:00:00.000','2013-06-19 12:32:00.000','2013-06-19 13:14:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-02 00:00:00.000','2013-06-19 13:30:00.000','2013-06-19 18:10:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-03 00:00:00.000','2013-06-19 08:40:00.000','2013-06-19 17:55:00.000','1900-01-01 09:15:00.000','1900-01-01 00:15:00.000','Halfday')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-04 00:00:00.000','2013-06-19 08:32:00.000','2013-06-19 10:53:00.000','1900-01-01 08:45:00.000','1900-01-01 00:15:00.000','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-04 00:00:00.000','2013-06-19 11:09:00.000','2013-06-19 12:57:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-04 00:00:00.000','2013-06-19 13:06:00.000','2013-06-19 13:18:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-04 00:00:00.000','2013-06-19 13:45:00.000','2013-06-19 18:09:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-05 00:00:00.000','2013-06-19 08:47:00.000','2013-06-19 18:01:00.000','1900-01-01 09:14:00.000','1900-01-01 00:14:00.000','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-08 00:00:00.000','2013-06-19 08:35:00.000','2013-06-19 18:29:00.000','1900-01-01 09:54:00.000','1900-01-01 00:54:00.000','')
immad
June 27, 2013 at 3:23 am
Things not clarified ,,
More than one rows per date
how spend date was calculated
how late and half date was updTED
etc
GIVE PROPER EXPLANATION ON HOW THE DATA RESIDES , AND WHAT WAS THE BUSINESS RULES TO FRAME THE OUTPUT ..THAT SAVES A TONS OF TIME
Your just giving data and output
June 27, 2013 at 3:50 am
Bala' (6/27/2013)
Things not clarified ,,More than one rows per date
how spend date was calculated
how late and half date was updTED
etc
GIVE PROPER EXPLANATION ON HOW THE DATA RESIDES , AND WHAT WAS THE BUSINESS RULES TO FRAME THE OUTPUT ..THAT SAVES A TONS OF TIME
Your just giving data and output
I am not sure if typing in bold capital letters is going to make the OP understand the importance of properly describing and presenting the question.
We have articles in SSC which we normally refer to such OP's to read and understand first.
If the OP still does not understand, I normally walk off and never waste my time.
@Immaduddin
I am not sure if you are wondering Why there is only one person trying to solve my problem and that nobody else seems to assist?
If you are not wondering so you should. SSC is a place where you get really quick help from many experts if you follow some basic rules.
If you don't follow those rules, you reduce your own chances of being helped
Hence, I would once again refer you to the same article on Forum Etiquette[/url]
If you read the article and follow some basic rules, I am sure you will get a lot more help and that too much quicker. Good luck:-)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 27, 2013 at 4:16 am
ok sir i give u full information
i am making a attendance project in this project when employee enter in factory he time in and when he go out from a factory he time out.i want to make a report that calculate spendtime and excessshort time(mean how much time did he spend in this factory and how much excess time and short time he spend in this factory).i did that.that task is completed.now i have another task that if employee want to see a summary report of whole month
or a specific date.now the other three questions
More than one rows per date?
some time employee timein and timeout several times that why its shows every time in and time out detail with date means if 24 june employee time in and time out 4 times then data is like this
eid----------date---------------------------timein------------------------------timeout----------------------spendtime--------excesshsort
17074---2013-01-12 00:00:00.000---2013-06-19 08:25:00.000---2013-06-19 12:22:00.000---- 09:14:00.000------00:14:00.000
17074--2013-01-12 00:00:00.000---2013-06-19 13:28:00.000-----2013-06-19 18:45:00.000
how spend date was calculated ?
well i make a monthly report i write syntax to caluclate spendtime and merge spend time in one column
how late and half day was updated ?
well i also write an update and select statement in one procedure when procedure execute first select statement run ,calculate spendtime and excess short column , show remarks that its halfday or late then update column remarks in attend_log table.
i just want to caluclate absnet and off day other works is done.
i hope u understand
immad
June 27, 2013 at 8:41 am
It seems to partially make sense now, but I feel that there are still some pieces missing from the logic.
For example; do all employees have Sunday off or do different employees have different days off?
If employees have different days off a week, how do you determine the difference between public holidays, absence, day off etc. I would suspect that you would then have at least a calendar table that shows these exceptable absences.
From what you have described, you have no public holidays, annual leave and employees only have Sunday off - this is the only set of conditions that would satisfy the data that you have sent.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
June 27, 2013 at 10:56 pm
sir your first question that
Q. do all employees have Sunday off or do different employees
have different days off ?
A.yes sir different days off. for employess.
Q.If employees have different days off a week, how do you determine the difference between public holidays, absence, day off etc ?
A.sir i make a roster table that define shifts and employee off day , public holidays are not define and absent if employee absent if there is no off day then its absent i do that in my monthly report procedure
Q.I would suspect that you would then have at least a calendar table that shows these exceptable absences?
A.Yes sir i have a calendar table that define whole year dates from jan 1 2013 to dec 31 2013
but sir this work is done i want to summarize employee data means in jan month how many days he absent?how many days he do half day.how many days is off day.problem is that i get a problem to calculate off day and absent other wise all work is done.
immad
June 28, 2013 at 3:29 am
A.sir i make a roster table that define shifts and employee off day , public holidays are not define and absent if employee absent if there is no off day then its absent i do that in my monthly report procedure
It sounds like you need to use this table in your query as this would give the missing Sunday in your earlier example
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
June 28, 2013 at 4:03 am
this is my query
SELECT
EID,
LATE,
[HALF DAY],
ABSENT,
[OFF DAY],
[EXCESS / SHORT],
ExcessShort,
CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS [SPEND TIME]
FROM
(
SELECT
t.EID,
sum(case when t.Remarks = 'LATE' then 1 else 0 end) as LATE,
sum(case when t.Remarks = 'HALF DAY' then 1 else 0 end) as [HALF DAY],
sum(case when t4.shift ='O' and t.eid = t3.eid then 1 else 0 end) as [OFF DAY],
case when datediff(dd,'20130101','20130131')%7 < 6 then 1 else 0 end as Absent,
DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedate,
convert(Varchar(10),DATEADD(hh,SUM(DATEDIFF(mi,0,excessshort)),0),108)AS [EXCESS / SHORT],
case when (540 - 560) > 0 then 'Short'
when (540 - 560) < 0 then 'Excess'
else NULL end as ExcessShort
FROM ATTEND_LOG t
left join employee e on e.eid = t.eid
LEFT OUTER JOIN FRoaster (17074) T3 ON e.EID = T3.EID AND DATEPART(YYYY,T.DATE) = T3.Year AND DATEPART(MM,T.DATE) = T3.Month AND DATEPART(DD,T.DATE) = T3.Day
LEFT OUTER JOIN ShiftView T4 ON T3.SName = T4.Shift
where t.eid=17074 and T.date >'20130101' and T.date <'20130131'
group by t.eid
)t
how i implement calendar table and what syntax i write on this query
immad
June 28, 2013 at 8:26 am
And does this new query give you the correct result?
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
June 28, 2013 at 10:37 pm
this query give me the correct result except off days and absent.i am trying but its a complex thing for me
immad
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply