3

I have the following function: -

ALTER FUNCTION fncTest ()
RETURNS TABLE
AS
  RETURN
    (SELECT *
     FROM   (SELECT TOP 100 PERCENT sOrderType,
                                    SUM(iQty) AS iOrdQty,
                                    ( YEAR(dReqd) * 100 ) + DATEPART(Week, dReqd) AS iWkNo
             FROM   tblOrderBook
             GROUP  BY sOrderType,
                       dOrdered) AS tblTemp PIVOT(SUM(Qty) FOR iWkNo IN 
                        ([201118], [201119], [201120], [201121], [201122])) AS pvtTemp)

This gives me a pivoted table showing qtys of orders for weeks 18-22 of 2011.

Is it possible to replace the hard-coded weeks with dynamic dates based on GETDATE().

ie:
  • replace [201118] with (YEAR(GETDATE()+7)*100)+DATEPART(week,GETDATE()+7)
  • replace [200119] with (YEAR(GETDATE()+14)*100)+DATEPART(week,GETDATE()+14)
  • replace [200120] with (YEAR(GETDATE()+21)*100)+DATEPART(week,GETDATE()+21)

etc...

Thanks.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Shaun2011
  • 71
  • 2
  • Similar question: http://stackoverflow.com/questions/2363286/is-it-possible-that-we-pass-a-query-to-pivot-as-column-list-in-sql-server – JeffO May 18 '11 at 14:56

1 Answers1

5

Not as you want to do it. The only way of doing this is dynamic SQL and you can't use dynamic SQL in a function.

But you could use fixed column names such as [1],[2],[3] etc to represent the week number relative to the current date.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845