SQL Server Performance

Absent

Discussion in 'ALL SQL SERVER QUESTIONS' started by immad, Aug 15, 2013.

  1. immad Member

    Hello

    i have a query problem

    this is my query


    select
    distinct a.eid,
    e.ename,
    CONVERT (DATETIME,CONVERT (VARCHAR,CheckTIme,101)) Date,
    case when a.status='I' and a.status= 'O' then '' else 'P' end Attendance
    from attendlog a
    left outer join employee e on a.eid = e.eid
    where CONVERT (DATETIME,CONVERT (VARCHAR,CheckTIme,101))>'20130801' and CONVERT (DATETIME,CONVERT (VARCHAR,CheckTIme,101))<'20130815'
    order by date

    its gives me that result

    eid--------name---------------date-------------------attendance
    1-------------A--------2013-08-01 00:00:00.000----------P
    1-------------A--------2013-08-02 00:00:00.000----------P
    1-------------A--------2013-08-04 00:00:00.000----------P

    but if A employee absent on 3 august then that data its not in attendlog
    becouse attendlog table didnt have absent employee data
    please help me out

    CREATE DATABASE dbname;


    CREATE TABLE Attendlog
    (
    EID int,
    Name varchar(10),
    date datetime,
    Attendnace varchar(10)
    );

    INSERT INTO attendlog (eid,name,date,attendance)
    VALUES (1,A,2013-08-01 00:00:00.000,P);


    INSERT INTO attendlog (eid,name,date,attendance)
    VALUES (1,A,2013-08-02 00:00:00.000,P);


    INSERT INTO attendlog (eid,name,date,attendance)
    VALUES (1,A,2013-08-04 00:00:00.000,P);


  2. davidfarr Member

    There is no possible query syntax that can fabricate missing dates and data rows on its own.
    To include the missing dates; you would need to first create a reference calendar table of dates and 'left join' from that against the actual attendance log.
    Something like this;
    Code:
    CREATE TABLE #Calendar  ( [CalDate] smalldatetime )
    GO
    insert into #Calendar values ('2013-08-01')
    insert into #Calendar values ('2013-08-02')
    insert into #Calendar values ('2013-08-03')
    insert into #Calendar values ('2013-08-04')
    GO
    
    select
    distinct a.eid,
    e.ename,
    CONVERT (VARCHAR,t1.CalDate,101) as [Date],
    case when a.status='I' and a.status= 'O' then '' else 'P' end Attendance
    from #Calendar t1
    left join attendlog a on convert(varchar,t1.CalDate,101)=convert(varchar,a.CheckTime,101)
    left outer join employee e on a.eid = e.eid
    where t1.caldate between '2013-08-01' and '2013-08-04'
    order by [date]
    GO
    
    One other thing you need to re-check; Your CASE statement is not logically valid;
    Code:
    case when a.status='I' and a.status= 'O' then '' else 'P' end
    A row value can never equal two different values in the same condition.
    That case statement will return a 'P' value on every row regardless of the actual data values in the row. I don't know what you were aiming for there, but it needs more work.

Share This Page