I need to count and group records held on SQL Server 2003 by a combined Year Week String

I have been asked to graph data held in SQL Server. This I can do with the tools I have available.

However I have been asked to count the number of records in the database, grouping them based on a combination of last two digits of the Year, and the week number

For example the first week of 2011 would be 1101

example the fif开发者_StackOverflow中文版teenth week of 2010 would be 1015

(note need week as 2 char) so index who alphabetically

I have tried various combinations such as

select CASE WHEN DATENAME(ww,j.requestedat) <= 9
            THEN (CAST('0' AS VARCHAR(1)) + CAST(DATENAME(ww,j.requestedat) AS VARCHAR(1)))
            ELSE CAST(DATENAME(ww,j.requestedat) AS VARCHAR(2))
         END AS WeekNumber
         , right(DateName(yy, j.requestedat),2) + WeekNumber as YYWW
from
    facts_reactive.dbo.jobs j 
order by j.requestedat

but all have failed.

Any help/guidance gratefully accepted.

Rob

try this one :

select
    right(DateName(yy, wn.requestedat),2) + wn.WeekNumber as YYWW
from
    (
        select CASE WHEN DATENAME(ww,j.requestedat) <= 9
                THEN (CAST('0' AS VARCHAR(1)) + CAST(DATENAME(ww,j.requestedat) AS VARCHAR(1)))
                ELSE CAST(DATENAME(ww,j.requestedat) AS VARCHAR(2))
             END as WeekNumber, j.requestedat
        from
            facts_reactive.dbo.jobs j      
    ) AS wn
order by wn.requestedat

Try this:

SELECT RIGHT(YEAR(j.requestedat),2) + RIGHT('00'+CAST(DATEPART(ww,j.requestedat) AS VARCHAR(2)),2) YYWW
FROM facts_reactive.dbo.jobs j  
ORDER BY j.requestedat 

SELECT
  YYWW = RIGHT(YEAR(j.requestedat) * 100 + DATEPART(ww, j.requestedat), 4)
FROM facts_reactive.dbo.jobs j
ORDER BY j.requestedat