Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010
    Posts
    1

    Unanswered: Need Leave count Query [urgent]

    Hi ,

    I need a query to calculate appliedleaves, remaining leaves of a staff whose leave status is approved by excluding weekends [ saturday + sunday] and holidays [ which are in my holidays table ] by using leavefrom and leaveto dates a leave.



    I have 3 tables like...


    LeaveTypes table
    ---------------------------------------------
    LeaveType Numberofleaves
    ---------------------------------------
    85 24
    86 24
    87 90
    88 30

    [ 85-casual, 86- Payment , 87- maternity , 88- faternity etc ]

    Leaves table [* all dates are in mm/dd/yyyy format]
    ------------------------------------------------------------
    staffid leaveappliedon leavetype leavefrom leaveto status
    ------------------------------------------------------------
    101 1/1/2010 85 1/1/2010 1/5/2010 approved
    102 1/2/2010 86 1/15/2010 1/18/2010 open
    101 1/3/2010 87 2/20/2010 2/25/2010 approved
    103 1/4/2010 86 1/15/2010 1/18/2010 open


    Holidays table
    --------------------------------------------------------
    Holidaydate Holidayname Holidaytype
    -------------------------------------------------------
    1/1/2010 New Years day General
    3/17/2010 Saint Patrick's Day General


    I want result like this

    for ex staff 101

    ----------------------------------------------------------------
    Leavetype Numberofleaves Appliedleaves remainingleaves
    ----------------------------------------------------------------
    85 24 2 23
    86 24 0 24
    87 90 4 86
    88 30 0 30

    -----------------

    Thanks in advance.

    Regards,
    Anil Kumar.T

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Funny how everyone needs "leaves" queries all of a sudden, and urgently too.
    http://www.dbforums.com/microsoft-sq...ent-query.html
    http://www.dbforums.com/microsoft-sq...eavestats.html

    So Anil - usual question: what have you come up with so far?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •