Results 1 to 6 of 6

Thread: Off day

  1. #1
    Join Date
    Jun 2013
    Posts
    3

    Unanswered: Off day

    Dear all,

    i am making attendance project for a factory.employee come in factory time in and time out. on sunday employee have off day and some time employee get absent in my data i dont have absent and off day data i want to create this. my data is like this



    date---------------------------eid--------------timein----------------------timeout-------------------------------spendtime------------excesshsort
    2013-05-02 00:00:00.000--17031----2013-06-13 09:34:00.000------2013-06-13 18:30:00.000-------08:56:00----------- 00:04:00
    2013-05-03 00:00:00.000--17031------2013-06-13 09:55:00.000------2013-06-13 13:30:00.000-------04:41:00------------- 04:19:00


    i want this type of data

    date---------------------------eid------------timein----------------------------timeout---------------------spendtime------------excesshsort
    2013-05-02 00:00:00.000--17031-----2013-06-13 09:34:00.000------2013-06-13 18:30:00.000-------08:56:00----------- 00:04:00
    2013-05-03 00:00:00.000--17031------2013-06-13 09:55:00.000------2013-06-13 13:30:00.000-------04:41:00------------- 04:19:00
    2013-05-04 00:00:00.000---17031---------2013-06-13 09:15:00.000------2013-06-13 15:23:00.000-----06:08:00--------- 02:52:00
    2013-05-05 00:00:00.000--17031--------------NULL------------------------NULL-------------------------------NULL------------------NULL


    when employee absent or off day data show in table off day mean sunday


    i hope u under stand
    thanks
    Last edited by immad; 06-17-13 at 09:40.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    There are two ways of doing this that come to mind:
    • Create a record in your main table for every employee, going as far into the future as you require. Amend the processes so that when the employees clock in and out, it searches for their record for that day and updates it accordingly.
    • Create a calendar table that holds each date for as far into the future as you require. Give this table flag columns, such as weekend or public holiday. Cross-join the calendar table to your employee table to yield a view that gives you a record for each employee and each date. Left-join this view to the main table, so that you can see gaps
    My preference would be for the latter option, but it's really up to you.
    I'm sure that other people can advise you of more elegant solutions.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Calendar table trick ..

    Do you know about the Calendar table idiom in SQL. It is a table keyed on a calendar date used to find other temporal data. Business days are part of the data ..

    CREATE TABLE Calendar
    (cal_date DATE NOT NULL PRIMARY KEY,
    julian_business_nbr INTEGER NOT NULL,
    ...);

    INSERT INTO Calendar
    VALUES ('2007-04-05', 42),
    ('2007-04-06', 43), -- good Friday
    ('2007-04-07', 43),
    ('2007-04-08', 43), -- Easter Sunday
    ('2007-04-09', 44),
    ('2007-04-10', 45); --Tuesday

    To compute the business days from Thursday of this week to next
    Tuesdays:

    SELECT (C2.julian_business_nbr - C1.julian_business_nbr)
    FROM Calendar AS C1, Calendar AS C2
    WHERE C1.cal_date = '2007-04-05',
    AND C2.cal_date = '2007-04-10';

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If your database does not already have a sequential numbers table, then you should create one:
    Code:
    CREATE TABLE [dbo].[SequentialNumbers] ([Number] [int] NOT NULL PRIMARY KEY CLUSTERED)
    
    ;
    with NumList (NumVal) as
    	(select 0 as NumVal
    	UNION ALL
    	select NumVal + 1
    	from NumList
    	where NumVal < 9)
    insert into SequentialNumbers (Number)
    select	Ones.NumVal + Tens.NumVal * 10 + Hundreds.NumVal * 100 + Thousands.NumVal * 1000
    from	NumList Ones,
    	NumList Tens,
    	NumList Hundreds,
    	NumList Thousands
    where not exists
    	(select	*
    	from	SequentialNumbers
    	where	Number = Ones.NumVal + Tens.NumVal * 10 + Hundreds.NumVal * 100 + Thousands.NumVal * 1000)
    Then create this function in order to return a list of dates within a range.

    Code:
    CREATE function [dbo].[DateRange](@StartDate date, @EndDate date)
    returns table
    --Test parameters
    --declare	@StartDate date
    --declare	@EndDate date
    --set		@StartDate = GETDATE()
    --set		@EndDate = '2013-01-01'
    return
    with	Holidays as
    		(
    		select '2011-01-03' as HolidayDate, 'New Years' as Holiday
    		union select '2011-05-30' as HolidayDate, 'Memorial Day' as Holiday
    		union select '2011-07-04' as HolidayDate, 'Independence Day' as Holiday
    		union select '2011-09-05' as HolidayDate, 'Labor Day' as Holiday
    		union select '2011-11-24' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2011-11-25' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2011-12-26' as HolidayDate, 'Christmas Day' as Holiday
    
    		union select '2012-01-02' as HolidayDate, 'New Years' as Holiday
    		union select '2012-05-28' as HolidayDate, 'Memorial Day' as Holiday
    		union select '2012-07-04' as HolidayDate, 'Independence Day' as Holiday
    		union select '2012-09-03' as HolidayDate, 'Labor Day' as Holiday
    		union select '2012-11-22' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2012-11-23' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2012-12-25' as HolidayDate, 'Christmas Day' as Holiday
    
    		union select '2013-01-01' as HolidayDate, 'New Years' as Holiday
    		union select '2013-05-27' as HolidayDate, 'Memorial Day' as Holiday
    		union select '2013-07-04' as HolidayDate, 'Independence Day' as Holiday
    		union select '2013-09-02' as HolidayDate, 'Labor Day' as Holiday
    		union select '2013-11-28' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2013-11-29' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2013-12-25' as HolidayDate, 'Christmas Day' as Holiday
    		),
    	DateList as
    		(
    		select	Number as DateNumber,
    			DATEADD(day, number, @StartDate) as DateValue
    		from	dbadb.dbo.SequentialNumbers
    		where	DATEADD(day, number, @StartDate) <= @EndDate
    		)
    select	DateList.DateNumber,
    	DateList.DateValue,
    	datepart(weekday, DateList.DateValue) DayOfWeek,
    	datepart(day, DateList.DateValue) DayOfMonth,
    	datepart(DAYOFYEAR, DateList.DateValue) DayOfYear,
    	case when datepart(weekday, DateList.DateValue) between 2 and 6 then 1 else 0 end as IsWeekDay,
    	case when (datepart(weekday, DateList.DateValue) between 2 and 6 and Holidays.HolidayDate is null) then 1 else 0 end as IsWorkDay,
    	case when Holidays.HolidayDate is null then 0 else 1 end as IsHoliday,
    	coalesce(Holidays.Holiday, '') as HolidayName
    from	DateList
    	left outer join Holidays on DateList.DateValue = Holidays.HolidayDate
    You can replace the Holidays CTE with an actual table, if you wish.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2013
    Posts
    3
    Thanks for your help i solved this problem by my self

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Just an aside, why would you put essentially a non-row into your table? The better method would be to only have them in the tables for the dates that there are entries to be put into the table, such as time-in time-out, maybe vacation/sick day. Then you outer join this table to your calendar. This way every date shows and then the emp id with their times on the days that they were working.

    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •