i'm looking efficient way convert columns week name in sql server.
this example:
id value ondate 1 10 06/05/2013 2 9 07/05/2013 3 5 08/05/2013 4 89 09/05/2013 5 8 10/05/2013 scenario #1:
- start date : 06/05/2013 (monday)
- end date : 14/05/2013 (thursday)
this result:
mo tu th fr sa su row1 10 9 5 89 8 23 3 row2 5 8 scenario #2:
- start date : 06/05/2013 (monday)
- end date : 07/05/2013 (thursday)
this desired result:
mo tu row1 10 9 how can build result?
use sum(case... below pivot data across days columns (or pivot operator). also, consider how multiple years might affect results, , adjust grouping accordingly.
set dateformat dmy; set datefirst 1; --monday declare @yourtable table (id int, value int, ondate datetime) insert @yourtable select 1, 10, '06/05/2013' union select 2, 9, '07/05/2013' union select 3, 5, '08/05/2013' union select 4, 89, '09/05/2013' union select 5, 8, '10/05/2013' union select 6, 23, '11/05/2013' union select 7, 3, '12/05/2013' union select 8, 5, '13/05/2013' union select 9, 8, '14/05/2013' select [mo]=sum(case when datename(weekday, ondate) = 'monday' value else 0 end), [tu]=sum(case when datename(weekday, ondate) = 'tuesday' value else 0 end), [we]=sum(case when datename(weekday, ondate) = 'wednesday' value else 0 end), [th]=sum(case when datename(weekday, ondate) = 'thursday' value else 0 end), [fr]=sum(case when datename(weekday, ondate) = 'friday' value else 0 end), [sa]=sum(case when datename(weekday, ondate) = 'saturday' value else 0 end), [su]=sum(case when datename(weekday, ondate) = 'sunday' value else 0 end) @yourtable ondate between '06/05/2013' , '14/05/2013' group datepart(week, ondate); -- result: /* mo tu th fr sa su 10 9 5 89 8 23 3 5 8 0 0 0 0 0 */ 
Comments
Post a Comment