Data Split and insert into another table

• durai nagarajan

  SSCoach

  Points: 15778

  More actions

  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

• GSquared

  SSC Guru

  Points: 260824

  More actions

  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

• durai nagarajan

  SSCoach

  Points: 15778

  More actions

  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