Attendance Report Query!!! Need Help!!!

  • Hi All,

          I have a view which has the below like data as output

          http://www.nopaste.com/p/a1umSHdBx/txt

          I wanted to represent this data like

          Employee 1  01-01-2007 Absent

          Employee 1  02-01-2007 Present

          Employee 1  03-01-2007 Present

          Which query is best for this?

    If my question is not understandable, please reply..i shall try to put more info... my thought is attendance system is commonly known to everyone so may be this info is enough........

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

  • What have you tried, so far?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • That URL link just shows up as HTML (ie, I see the HTML code) to me rather than what I think you expect it to be which is a nicely formatted table.... Hmmm, actually in IE it works - Opera it doesn't... GO figure?

    So what you want is for a range of dates to see, for each date, if an employee had a shift that day or not?

    If this is it, then you'll want what's called a numbers table - Adam Machanic (might not be spelled correctly) has some good info on these. Lots of articles on this site.

    You can treat the numbers table as a range of dates - each number is a date.

    Then you can perform an outer join, or use an exists clause.

    Perhaps

    select EmpNames.EmpName, Dates.Date, case when exists(select * from Cards where [First Name]=EmpNames.EmpName and LogDate = Dates.Date) then 'Present' else 'Absent' end as DayStatus

    from (select distinct [First Name] as EmpName from Cards) EmpNames

    cross join Dates

    would do what you want. The [Dates] table is yours to create from a numbers table. You could do something like

    select EmpNames.EmpName, Dates.Date, case when exists(select * from Cards where [First Name]=EmpNames.EmpName and LogDate = Dates.Date) then 'Present' else 'Absent' end as DayStatus

    from (select distinct [First Name] as EmpName from Cards) EmpNames

    cross join (select dateAdd(d, numbers.number, '2007-01-01') as Date from numbers) dates

    if you had a numbers table called numbers with a single column called number.

    Hope that helps.

  • I created another table with 1,2,3,....31 as values for one column and i tried to put left outer join like the below

    http://www.nopaste.com/p/a7EK0M81gb/txt

    Here cms_date is the table which has a column with values from 1,2,3 ....31

    vw_Attendance_Report  is the view.

    But what is happening is it tries to put outer join on whole dataset instead of each individual which is what i want....

     

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

  • Hi Ian,

           The same logic i had tried already but how can we know that employee was absent for somedays? The query will return only present days...This result i just wanted to publish in asp where i dont want to put any logic and all the logic i wanted to finish in SQL itself..

    Jeff: Hope you got what i had tried...

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

  • Try this:

    ....

    FROM employee E

    LEFT JOIN vw_Attendance_Report hj ON E.Empcode = hj.Empcode

    RIGHT JOIN cms_date kj on kj.day_month =datepart(dd,FirstAccessDateTime)

    order by E.empcode , kj.day_month

    _____________
    Code for TallyGenerator

  • If i have only one employee record then below is the output of the query...

    http://www.nopaste.com/p/aUDjixI6d/txt

    But same employee table if i have 5 records then this is the output

    http://www.nopaste.com/p/aMRaPHc3hb/txt

    So finally if my employee table is having 2000 records and if atleast one employee came on any sunday then it will not show null for that sunday for anyother employee in the records...

    Sergiy: Does this testing is fine??

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

  • First, the lecture...

    If you want good quality help in a timely fashion, you must take the time to post your data in the form of a CREATE TABLE along with the necessary INSERT/SELECT UNION ALL statements 'cause folks just don't have the time or inclination to do what you should have

    Ok... if you don't already have one, let's make a Tally (numbers) table that can handle a little over 30 years worth of dates, etc, etc.

    --===== Create and populate the Tally table on the fly

     SELECT TOP 11000 --equates to more than 30 years of dates

            IDENTITY(INT,1,1) AS N

       INTO dbo.Tally

       FROM Master.dbo.SysColumns sc1,

            Master.dbo.SysColumns sc2

    --===== Add a Primary Key to maximize performance

      ALTER TABLE dbo.Tally

            ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)

    --===== Allow the general public to use it

      GRANT SELECT ON dbo.Tally TO PUBLIC

    And, because I'm lazy, here's what we're going to use to simulate your data...

    --===== Create and populate test table called "yourtable".

     SELECT TOP 500

            RowNum     = IDENTITY(INT,1,1),

            Employee   = CAST(RAND(CAST(NEWID() AS VARBINARY))*10+1 AS INT),

            SomeDate   = CAST(RAND(CAST(NEWID() AS VARBINARY))*90.0+36524.0 AS DATETIME)

       INTO dbo.yourtable

       FROM Master.dbo.SysColumns sc1,

            Master.dbo.SysColumns sc2 --Lack of join criteria makes this a CROSS-JOIN

    --===== A table is not properly formed unless a Primary Key has been assigned

      ALTER TABLE dbo.yourtable

            ADD PRIMARY KEY CLUSTERED (RowNum)

    ... and, finally, the solution to your original request.  The key to this is that in order to figure out if an employee is absent on any particular date, you must have a reference that contains every date for every employee (derived table "de")... and you must do it carefully because it does require a CROSS JOIN and, if not done properly, can create millions of unwanted rows which will drag performance right into the toilet.  Read the comments, study the code...

    DECLARE @StartDate DATETIME

    DECLARE @EndDate   DATETIME

        SET @StartDate = '20000201'

        SET @EndDate   = '20000301'

     SELECT CONVERT(VARCHAR(10),de.TheDate,101) AS TheDate,

            de.Employee,

            CASE WHEN y.Employee IS NULL THEN 'Absent' ELSE 'Present' END AS Attendance

       FROM (--==== Derived table finds unique date/employee combinations

             SELECT DISTINCT

                    DATEADD(dd,DATEDIFF(dd,0,SomeDate),0) AS SomeDate,

                    Employee

               FROM yourtable

            )y

      RIGHT OUTER JOIN

            (--==== Derived table finds every date/employee combination

             SELECT d.TheDate,e.Employee

               FROM

                    (SELECT CAST(N-1 AS DATETIME)+@StartDate AS TheDate

                       FROM dbo.Tally

                      WHERE N<=DATEDIFF(dd,@StartDate,@EndDate)

                    )d

              CROSS JOIN

                    (SELECT DISTINCT Employee

                       FROM yourtable

                    )e

            )de

         ON y.SomeDate = de.TheDate

        AND y.Employee  = de.Employee

      ORDER BY de.TheDate,de.Employee

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Jeff...I will getback to you after reading and understanding and testing this solution...Many Thanks!!

     

    Regards,

    Sakthi

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

  • You bet, Sakthi... thanks for the feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hey Jeff,

    What's so cool about your solutions, one can cut and paste them into QA and run them!  And......they work!  Great job.

    Steve

  • Heh... I try to write "Fine" solutions because I've had "Mo-den-enough" with the ones that aren't   Thanks for the great compliment, Steve!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

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