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

[JB1976]

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.

[skhanal]

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.

[JB1976]

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