How to add dates not existing on a table

Question

How to add dates not existing on a table

gjuarez

Old Hand

Points: 395
More actions
December 15, 2016 at 10:07 am

#324054

I have a table with staff information, in that table i have a the date staff was logged in our system. I have a table with dates for the entire year that staff are supposed to be logged in.
I want to have a count of days a staff was not logged in (sort of attendance tracking). So the only way i can see what date this staff was missing is by comparing both tables and "add" the missing dates to either a new table or the staff table. I tried to link both tables by the date only but that doesn't work. The date format is smalldatetime. Any suggestions? Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Could you post your create table scripts and some sample data? It sounds like you have to unpack the intervals and then compare, but I'm not sure without table structures etc.

Lowell SSC Guru Points: 323518 More actions · Answer 2

the key is to generate all possible combinations first..

Basically, you have to select from Your Calendar/Dates table,and cross join all possible employees, and then finally left join it to attendance.

WHERE Attendendance.ID IS NULL gives you the "missing" items to add:

something like

SELECT

Calendar.TheDate

AllEmployees.EmployeeID,

Attendance.EmployeeID

FROM Calendar

CROSS JOIN AllEmployees

LEFT JOIN Attendance

ON Calendar.TheDate = Attendance.TheDate

AND AllEmployees.EmployeeID = Attendance.EmployeeID

WHERE AllEmployees.IsActive = 1

AND Calendar.TheDate >='2016-12-01'

AND Calendar.TheDate <='2016-12-31'

--AND Attendance.EmployeeID IS NULL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gjuarez Old Hand Points: 395 More actions · Answer 3

Table 1 Staff

SELECT [SECTION_GU]

,[ORGANIZATION_NAME]

,[SECTION_ID]

,[COURSE_ID]

,[COURSE_TITLE]

,[PERIOD]

,[TEACHER_NAME]

,[ATTEND_TAKEN_DATE]

,[BELL_PERIOD]

FROM [rev].[VWTCHTAKEATTENDANCE_2]

table 2 Calendear

SELECT [CAL_DATE]

FROM [rev].[VWTCHTAKEATTCALENDAR]

I need to create a table or view where attend_taken_dates that do not exist in table 1 be inserted for each staff record from table 2

gjuarez Old Hand Points: 395 More actions · Answer 4

Further clarification; not all staff records are missing dates, the ones are missing dates are the ones that need a date from table 2.

So if i query staff attendance for this week staff 1 shows all the days, staff 2 is missing 1 day, that will need to be inserted from table 2

STAFFNAMEATTDATE

NAME112/12/2016 0:00

NAME112/13/2016 0:00

NAME112/14/2016 0:00

NAME112/15/2016 0:00

NAME212/12/2016 0:00

NAME212/14/2016 0:00

NAME212/15/2016 0:00