How to create monthly attendance report?

15 pts.
Tags:
AS/400
SQL
TABLE I HAVE:
Code Name AttendanceDate Status
---- ---- -------------- ------
A001 Abc Jan-01-2013 Present
B001 Xyz Jan-01-2013 Present
A001 Abc Jan-02-2013 Absent
B001 Xyz Jan-02-2013 Late
A001 Abc Jan-03-2013 Late
Expecting below output with AVG attendance of month.
Jan, 2013
Code Name 1 2 3 .. 29 30 31   AVG_ATTENDANCE_OF_MONTH 
---- ---- - - - .. -  -  -
A001 Abc  P A L .. L  P  A 
B001 Xyz  P L L .. L  P  A
Hi, please help me guys.


Software/Hardware used:
system analyst
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Splat
    What have you tried?
    12,915 pointsBadges:
    report
  • Sri1008
    Hello Splat,

    I have tried below Sql stmt.

    SELECT [EmpCode], [ProjectName],[1] , [2],[3],[4],[5]
    FROM 
    (SELECT Day([AttendanceDate]) as d1, [EmpCode],[ProjectName],[AttendanceDate] , [Status]
    FROM [tebs].[dbo].[View_Attendance]) p
    PIVOT(
    MAX([Status])
    FOR d1 IN (  [1] , [2],[3],[4],[5]) ) AS pvt
    ORDER BY 
    pvt.[EmpCode],  pvt.[ProjectName];

    BUT it result was... 
    A100	p1	NULL	NULL	P	NULL	NULL
    A100 p1 NULL NULL NULL P NULL
    A100 p1 NULL NULL NULL NULL P
    A100 p1 NULL NULL NULL NULL NULL
    A101 p3 NULL NULL P NULL NULL
    A101 p3 NULL NULL NULL P NULL
    A101 p3 NULL NULL NULL NULL L
    A102 p3 NULL NULL P NULL NULL
    A102 p3 NULL NULL NULL P NULL
    A102 p3 NULL NULL NULL NULL 
    Thanks & Regards,
    Sriharsha
    15 pointsBadges:
    report
  • Splat
    I'm no great hand at SQL, but don't you need to group that data? I'm seeing repeated identifiers.
    12,915 pointsBadges:
    report
  • carlosdl
    Do you have a row for each day of the month in your table?

    Also, I don't know how PIVOT is implemented on DB2 (which is what I assume you are using), but in other RDMSs the number of columns in the Pivot result must be known beforehand. If that is the case on your system, you will not be able to have a generic query that shows 28 columns for February but 31 for August, for example. Will that work for you?
    85,480 pointsBadges:
    report
  • philpl1jb

    This will be rough

    Step 1 Create the table

    CREATE TABLE LEVINSPJ/ATTRPT (CODE CHAR ( 5) NOT NULL, NAME CHAR (
    50) NOT NULL, DAY01 CHAR ( 1) NOT NULL WITH DEFAULT ' ',
    DAY02 CHAR ( 1) NOT NULL WITH DEFAULT ' ',
    DAY03 CHAR ( 1) NOT NULL WITH DEFAULT ' ',
    DAY04 CHAR ( 1) NOT NULL WITH DEFAULT ' ' )

    Step 2 insert rows into table

    Insert into ATTRPT (Code, Name)

    (Select distinct code, Name from attendance order by code);

    54,090 pointsBadges:
    report
  • philpl1jb

    So we now have a table with rows for every employee with the code and name fields loaded and day01-day31 blank.

    One way to go would be to have 31 update statements .. another would have a boat load of case statements. This SQL will populate day 1.

    Update ATTRPT A Set A.Day01 = (Select COALESCE(Substring(B.Status,1,1), A.Day01) from Attendance b where DayOfMonth( A.AttendanceDate) = 01 and A.Code = B.Code);

    Now for every row in ATTRPT it goes out to get a day 01 record, if it's found it puts the first character of the status in Day01 else it puts the current value in Day01 back into day 01.

    54,090 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: