dcsimg
Results 1 to 3 of 3

Thread: to change a to&from table into a monthly version?

  1. #1
    Join Date
    Feb 2011
    Posts
    2

    Question to change a to&from table into a monthly version?

    Hello,

    I've got a table that looks like this (in DB2&MSSQL)...

    ID,NAME,SDATE,EDATE,TOTALDAYS,WEEKDAYS
    1,James,01/01/2010,10/01/2010,10,6
    2,John,03/02/2010,19/02/2010,16,13
    1,James,15/04/2010,23/07/2010,100,72
    3,Chris,05/08/2010,05/08/2010,1,1
    2,John,30/08/2010,03/09/2010,5,5
    3,Chris,28/09/2010,06/10/2010,9,7

    I want it in a format thats more like this...

    YEAR,MONTH,ID,NAME,TOTALDAYS,WEEKDAYS
    2010,JAN,1,James,10,6
    2010,FEB,2,John,16,13
    2010,APR,1,James,16,12
    2010,MAY,1,James,31,21
    2010,JUN,1,James,30,22
    2010,JUL,1,James,23,17
    2010,AUG,3,Chris,1,1
    2010,AUG,2,John,2,2
    2010,SEP,2,John,3,3
    2010,SEP,3,Chris,3,3
    2010,OCT,3,Chris,6,4

    Any ideas on how to achieve this???

    Thanks for any help as this ones really hurting my head
    jez.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,918
    In SQL Server there is a DATEPART function that you can use to get different parts of date, then you can use string concatenation to achieve the format you want.

  3. #3
    Join Date
    Feb 2011
    Posts
    2
    understand that i can chop up the date -

    the issue is more around how to create a row per month when
    i have a start and end date than can span many months

    with the secondary problem of how to give the number of days within that month

    and thirdly the number of week days

    thats whats getting by head in a muddle

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
  •