select employeeName that are present for a particu

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select employeeName that are present for a particu

Author

Topic

ofemino79
Starting Member

2 Posts

Posted - 2011-06-24 : 11:18:29

Hello gurus, am new to sql queries, but need to query some results from a view.
i have this columns in vw_SchoolLogs view;
EmployeeName datatype = varchar, AttendanceDate datatype = DateTime, Status datatype = Varchar(Present or absent).

The objective is to select employeeName that are present for a particular date, and if any employeeName is absent for that date, the query should also include result of the last date the employeeeName was present.

Kindly help, am new to sql programing

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-24 : 11:55:36

something like

with dtemaxmin as
(
select dtemin = MIN(AttendanceDate), dtemax = MAX(AttendanceDate) from vw_SchoolLogs
) ,
dte as
(
select d = dtemin from dtemaxmin
union all
select d+1 from dte, dtemaxmin where d < dtemax
)
select n.EmployeeName ,
dte.d ,
present = case when v.EmployeeName is null then 'No' else 'Yes' end ,
lastpresent = (select MAX(AttendanceDate) from vw_SchoolLogs v2 where v2.EmployeeName = n.EmployeeName and v2.AttendanceDate <= dte.d)
from dte
cross join (select distinct EmployeeName from vw_SchoolLogs) n
left join vw_SchoolLogs v
on v.EmployeeName = n.EmployeeName
and v.AttendanceDate = dte.d

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-25 : 14:26:31

see how you can create a calendar table here.
http://visakhm.blogspot.com/2010/02/generating-calendar-table.html
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ofemino79
Starting Member

2 Posts

Posted - 2011-06-27 : 05:55:11

thanks for the quick response but i don't seem to get it right. trying to twitch the your reply, i seem to be getting this error, "Invalid Object name dtemaxmin". am not making headways yet.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-27 : 06:42:09

dtemaxmin is the first cte.
Try
with dtemaxmin as
(
select dtemin = MIN(AttendanceDate), dtemax = MAX(AttendanceDate) from vw_SchoolLogs
)
sleect * from dtemaxmin

with dtemaxmin as
(
select dtemin = MIN(AttendanceDate), dtemax = MAX(AttendanceDate) from vw_SchoolLogs
) ,
dte as
(
select d = dtemin from dtemaxmin
union all
select d+1 from dte, dtemaxmin where d < dtemax
)
slecet top 10 * from dte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources