sql server - How to get week formatted string in sql query -


i have following sql query (sql server 2008):

select sum(data.freq) freq,data.week week ( select  count(daterequested) freq, datepart(wk,daterequested) week, daterequested  request ma join contracts mc on (mc.uid= ma.uid) join groups og on og.groupuid = mc.groupuid join member m on (m.memberuid = mc.memberuid) daterequested between  dateadd(month,-1,getdate()) , getdate() , isdeleted = 0 group datepart(wk,daterequested),daterequested --order daterequested asc ) data group data.week 

the result table following data:

enter image description here

instead of showing week number show week formatted following:

mm/dd mm = month , dd day week starts.

it great if can format starting first day of week, middle slash, last day of week , month: example: 11-17/04 (april 11 17), etc.

here final table get:

enter image description here

any clue?

in case needs found solution, maybe not best works.

select sum(data.freq) freq, data.week week, cast(data.weekstart varchar) + '-' + cast(data.weekend varchar) + '/' + cast(data.monthend varchar) formatweek ( select  count(daterequested) freq, datepart(wk,daterequested) week, datepart(dd,dateadd(dd, -(datepart(dw, daterequested)-1), daterequested)) weekstart, datepart(dd,dateadd(dd, 7-(datepart(dw, daterequested)), daterequested)) weekend, datepart(mm,dateadd(dd, 7-(datepart(dw, daterequested)), daterequested)) monthend, daterequested  requestma join contracts mc on (mc.uid= ma.uid) join groups og on og.groupuid = mc.groupuid join member m on (m.memberuid = mc.memberuid) daterequested between  dateadd(month,-1,getdate()) , getdate() , isdeleted = 0 group datepart(wk,daterequested),daterequested --order daterequested asc ) data group data.week,data.weekstart,data.weekend,data.monthend 

Comments

Popular posts from this blog

android - MPAndroidChart - How to add Annotations or images to the chart -

javascript - Add class to another page attribute using URL id - Jquery -

firefox - Where is 'webgl.osmesalib' parameter? -