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.
Who is Participating?

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

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.

Once you have that it is a fairly trivial exercise.

FROM dimDate AS D
   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.
Here is a link to an EE article on creating a calendar table

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

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.
   -- 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. 

-- 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
   INSERT INTO days (PKDate) 
   SELECT CAST(DATEADD(d, @i, @dt_start) as DATE) 

   SET @i = @i + 1

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.
I think you chose the right script to run.

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

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...

	DateKey				INT 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,

Open in new window

CREATE PROCEDURE dbo.PopulateDateTable
	@StartDate	DATE,
	@EndDate	DATE


WHILE (@StartDate <= @EndDate)
		[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
		[MonthNumber] = DATEPART(MONTH, @StartDate),
		[MonthName] = DATENAME(MONTH, @StartDate),
		[QuarterNumber] = DATEPART(QUARTER, @StartDate),
		[Year] = DATEPART(YEAR, @StartDate);

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

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:

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:

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


-- Set Holidays
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...

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
	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:

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.