create an attendance report that shows missed days.

I am trying to create a report that shows the days a person did not show up for work. The difficulty comes from the fact that currently there is no record in the table that would show they where not here. Only the dates they are here show up in the table.
Here is an example of the current table.
Name                  Badge#       Date          Time (in minutes)
John Belushi       1005           09/01/15     600
John Belushi       1005           09/02/15     600
John Belushi       1005           09/03/15     600
John Belushi       1005           09/08/15     600
John Belushi       1005           09/10/15     600
John Belushi       1005           09/15/15     600

What I am trying to find from this is that this person was not here on 09/09/15 & 09/14/15.  The reason for it just being those two days is because we work a 4 day week, so we are not here on Friday, Saturday and Sunday and 09/07 was a holiday.

I do not see a way to create a report just using this table. It would seem to me the easiest way would be to create a calendar table in SQL that shows all the dates and we could check the holidays and the weekends so we could ignore those form the report. But I am not sure how to create the sql table.
If someone can tell me either how to create the sql table or a way to create the report in Crystal without the calendar table. I would appreciate the help.
LVL 1
CJSilverAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
First you will need to create a date table that includes your holidays.  Day of the week is easy.  Below is a link to an example dimDate table that you can trim down a bit.  You will need to manually add and set an IsHoliday flag or something similar.

http://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho

Once you have that it is a fairly trivial exercise.

SELECT D.Date
FROM dimDate AS D
LEFT OUTER JOIN MyTable AS T
   ON D.Date = T.Date
WHERE D.IsHoliday = 0
   AND D.DayOfWeekUSA NOT IN (1, 6, 7) -- Sunday, Friday, Saturday
   AND T.Badge# = ???
CJSilverAuthor Commented:
Once I have the SQL table, making the report is easy. It's making the table that I am not clear on. I know very little about SQL scripts. I can write select statements and updates but I have never created a table. I have no idea what values to enter for the inserts. Which of the lines I Need from the create table example. Please tell me exactly what the script is that I need to run to create the table and insert the data. It's full date usa. I just want a table of dates that goes from 01/01/2010 through 12/31/2025. I need to know how to check off the dates that are days off. Every Friday, Saturday and Sunday and holidays that I select. I don't care about naming the holidays. It can just be a yes or no, it doesn't matter.
mlmccCommented:
Here is a link to an EE article on creating a calendar table

http://www.experts-exchange.com/articles/12267/SQL-Server-Calendar-Table.html

This can also be done without a calendar table using a list of holidays and formulas

mlmcc
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

CJSilverAuthor Commented:
As I mentioned, I do not know much about SQL scripts. When I tried to run the link that mlmcc gave I got many errors.
I don't want to create a database so I assume I just run the create table portion. This is what i ran.
CREATE TABLE days (
   PKDate date NOT NULL PRIMARY KEY CLUSTERED, 
   -- Years
   calendar_year smallint,
   -- Quarters 
   calendar_quarter tinyint, 
   calendar_quarter_desc varchar(10), 
   -- Months
   calendar_month tinyint, 
   calendar_month_name_long varchar(30), 
   calendar_month_name_short varchar(10), 
   -- Weeks
   calendar_week_in_year tinyint, 
   calendar_week_in_month tinyint,  
   -- Days
   calendar_day_in_year smallint, 
   calendar_day_in_week tinyint,  -- The first of the month 
   calendar_day_in_month tinyint, 
   mdy_name_long varchar(30), 
   mdy_name_long_with_suffix varchar(30), 
   day_name_long varchar(10), 
   day_name_short varchar(10), 
   -- Continuous Y/M/D, starts with the first day = 1 and keeps going.  Used for various dateadd functions.
   continuous_year tinyint,
   continuous_month smallint, 
   continuous_week smallint, 
   continuous_day int, 
   -- Custom
   description varchar(100), 
   is_weekend tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_holiday tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_workday tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_event tinyint)        -- Used to indicate any special event days. 
GO

-- Create the table, with dates ranging from 2010 to 2020.  Change to suit your needs.
Declare @dt_start date = '2000-01-01', @dt_end date = '2020-12-31', @total_days int, @i int = 0
SELECT @total_days = DATEDIFF(d, @dt_start, @dt_end) 

WHILE @i <= @total_days
   begin
   INSERT INTO days (PKDate) 
   SELECT CAST(DATEADD(d, @i, @dt_start) as DATE) 

   SET @i = @i + 1
   end

Open in new window


Here are the errors:
Msg 2715, Level 16, State 7, Line 1
Column or parameter #1: Cannot find data type date.
Msg 139, Level 15, State 1, Line 3
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 4
Must declare the variable '@dt_start'.
Msg 137, Level 15, State 2, Line 6
Must declare the variable '@i'.
Msg 137, Level 15, State 2, Line 9
Must declare the variable '@i'.

Also, how do I add a column to check off the holidays and weekends? By the way, my database is SQL2000. I have a SQL2005 & SQL2008 server but the reports I am running are on the SQL2000 server.
mlmccCommented:
I think you chose the right script to run.

Apparently SQL 2000 doesn't have a DATE type.  Try using DateTime

https://technet.microsoft.com/en-us/library/aa258271(v=sql.80).aspx

mlmcc
Brian CroweDatabase AdministratorCommented:
You're really handcuffing yourself by working on an almost 20-year-old version and I certainly don't have one installed so I can't test any of this but...

CREATE TABLE [Date]
(
	DateKey				INT NOT NULL,
	[Date]				DATETIME NOT NULL,
	[Day]				INT NOT NULL,
	[DayOfMonth]		INT NOT NULL,
	[DayName]			NVARCHAR(30) NOT NULL,
	[WeekNumber]		INT NOT NULL,
	[MonthNumber]		INT NOT NULL,
	[MonthName]			NVARCHAR(30) NOT NULL,
	[QuarterNumber]		INT NOT NULL,
	[Year]				INT NOT NULL,
	CONSTRAINT PK_Date PRIMARY KEY (DateKey)
)

Open in new window


CREATE PROCEDURE dbo.PopulateDateTable
(
	@StartDate	DATE,
	@EndDate	DATE
)  
AS

SET NOCOUNT ON;

TRUNCATE TABLE [Date];
 
WHILE (@StartDate <= @EndDate)
BEGIN
	INSERT INTO [Date]
	(
		DateKey,
		[Date],
		[Day],
		[DayOfMonth],
		[DayName],
		[WeekNumber],
		[MonthNumber],
		[MonthName],
		[QuarterNumber],
		[Year]
	)
	SELECT
		[DateKey] = CONVERT(INT, CONVERT(VARCHAR(20), @StartDate, 112)),
		[Date] = @StartDate,
		[Day] = DATEPART(WEEKDAY, @StartDate),
		[DayOfMonth] = DATEPART(DAY, @StartDate),
		[DayName] = DATENAME(WEEKDAY, @StartDate),
		[WeekNumber] = CASE
			WHEN DATEPART(DAY, @StartDate) between 1 and 7 THEN 1
			WHEN DATEPART(DAY, @StartDate) between 8 and 14 THEN 2
			WHEN DATEPART(DAY, @StartDate) between 15 and 21 THEN 3
			WHEN DATEPART(DAY, @StartDate) between 22 and 28 THEN 4
			WHEN DATEPART(DAY, @StartDate) >28 THEN 5
		END,
		[MonthNumber] = DATEPART(MONTH, @StartDate),
		[MonthName] = DATENAME(MONTH, @StartDate),
		[QuarterNumber] = DATEPART(QUARTER, @StartDate),
		[Year] = DATEPART(YEAR, @StartDate);

	SET @StartDate = DATEADD(DAY, 1, @StartDate);
END

Open in new window


EXECUTE PopulateDateTable @StartDate = '1/1/1900', @EndDate = '12/31/2199'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CJSilverAuthor Commented:
Brian,

Your script ran, but i it has far more columns then I need. I think I only need a date column and and closed column to mark which days we are not open. I can use the table yours created, but I need to add the closed column. How do I add that?
CJSilverAuthor Commented:
When I run the stored procedure script I get the following errors.

Msg 2715, Level 16, State 3, Procedure PopulateDateTable, Line 0
Column or parameter #1: Cannot find data type DATE.
Parameter '@StartDate' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure PopulateDateTable, Line 0
Column or parameter #2: Cannot find data type DATE.
Parameter '@EndDate' has an invalid data type.
Brian CroweDatabase AdministratorCommented:
Copy/Paste error from my procedure...Change both of the procedure parameter (@StartDate & @EndDate) datatypes to DATETIME
CJSilverAuthor Commented:
Brian,

How do I add the column to check off the weekends and holidays?
Brian CroweDatabase AdministratorCommented:
You don't need to do anything for weekends we can use the [Day] column to filter those.  After you have added the column (code below) you will need to manually set which days are holidays.  Unfortunately there just isn't an easy shortcut for this especially for holidays that don't fall on a consistent date.

-- Add the column

ALTER TABLE [Date]
ADD IsHoliday BIT NOT NULL
   CONSTRAINT DF_Date_IsHoliday DEFAULT (0)

-- Set Holidays
UPDATE [Date]
SET IsHoliday = 1
WHERE [Date] IN ('1/1/2015', '12/25/2015', etc)

For holidays that always fall on the same date like New Years' & Christmas you can hit them all at once...

UPDATE [Date]
SET IsHoliday = 1
WHERE DayOfMonth = 1
   AND MonthNumber = 1
Brian CroweDatabase AdministratorCommented:
Once that is done can you tell me how you would like to run the report?  Is it always by Employee or might you need to run it for a set of employees?  Would you run it against a date range?  Is there an employee table of some kind that we can use?  One possible solution is...

SELECT E.BadgeID, E.Name, D.[Date]
FROM Employee AS E
CROSS JOIN [Date] AS D			--CROSS JOIN Employees and Dates for all possible combinations
LEFT OUTER JOIN TimeClock AS T
	ON E.BadgeID = T.BadgeID
	AND D.[Date] = T.[Date]
WHERE E.StartDate <= D.[Date]	--No need to evaluate before the employee started
        AND D.[Date] < GETDATE() - 1
	AND D.IsHoliday = 0			--Holidays excluded
	AND D.[Day] NOT IN (1,7)	--Sunday & Saturday excluded
	AND T.[Date] IS NULL		--Exclude days worked

Open in new window

CJSilverAuthor Commented:
Brian,

I know how to create the report in Crystal. Thank you. This will work for me.
CJSilverAuthor Commented:
Thanks for the help Brian. Great work!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.