Post reply

Data Split and insert into another table

  • January 17, 2012 at 1:46 pm

    #249830

    Hello,

    I have a table which has leave data

    EmpCode, Leave_from, Leave_to, Status , addnl status.

    eg:

    1001,'12/1/2011','12/5/2011','auth','appr'

    i need to move it to another table

    EmpCode, LeaveDate, Status , addnl status.

    eg:

    1001,'12/1/2011',,'auth','appr'

    1001,'12/2/2011',,'auth','appr'

    1001,'12/3/2011',,'auth','appr'

    1001,'12/4/2011',,'auth','appr'

    1001,'12/5/2011',,'auth','appr'

    how to accomplish this?

    Regards
    Durai Nagarajan

  • January 17, 2012 at 1:56 pm

    #1434856

    Build a calendar table, join to that using Between on your start and end dates.

    The calendar table should have all dates in a reasonable range (I like 50 years for that usually), and should have dates marked as normal workdays or not. That way, you don't count a weekend or holiday as "on-leave".

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 17, 2012 at 2:04 pm

    #1434868

    thanks will try and update.

    i need it only on a month basis so i'll update calendar table on parameter basis.

    Regards
    Durai Nagarajan

Viewing 3 posts - 1 through 2 (of 2 total)

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