Here is a quick demonstration of a dynamic SQL solution, in case there is no option of changing/adding any tables in the database.
😎
USE tempdb;
GO
/* TEST DATA */
CREATE TABLE dbo.Planner(
[Name] [nvarchar](255) NULL
,[ProjectName] [nvarchar](255) NULL
,[7-Jul-14][nvarchar](255) NULL
,[14-Jul-14][nvarchar](255) NULL
,[21-Jul-14][nvarchar](255) NULL
,[28-Jul-14][nvarchar](255) NULL
,[4-Aug-14][nvarchar](255) NULL
,[11-Aug-14][nvarchar](255) NULL
,[18-Aug-14][nvarchar](255) NULL
,[25-Aug-14][nvarchar](255) NULL
,[1-Sep-14][nvarchar](255) NULL
,[8-Sep-14][nvarchar](255) NULL
,[15-Sep-14][nvarchar](255) NULL)
insert into Planner (Name,ProjectName,[7-Jul-14]
,[14-Jul-14]
,[21-Jul-14]
,[28-Jul-14]
,[4-Aug-14]
,[11-Aug-14]
,[18-Aug-14]
,[25-Aug-14]
,[1-Sep-14]
,[8-Sep-14]
,[15-Sep-14]) Values ('Mike','Audit','10','10','10','0','5','5','5','4','15','14','10')
DECLARE @TABLE_NAME NVARCHAR(128) = N'Planner'
DECLARE @TABLE_SCHEMA NVARCHAR(128) = N'dbo'
DECLARE @PARAM NVARCHAR(128) = N'@TABLE_NAME NVARCHAR(128),@TABLE_SCHEMA NVARCHAR(128)';
DECLARE @NORMALIZED_DATA TABLE
(
ND_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,P_NAME NVARCHAR(50) NOT NULL
,P_PROJECT_NAME NVARCHAR(100) NOT NULL
,DATE_VAL DATE NOT NULL
,P_VALUE INT NOT NULL
);
DECLARE @SQL_STR NVARCHAR(MAX) = N'';
SELECT @SQL_STR = N'SELECT
P.Name
,P.ProjectName
,CONVERT(DATE,C.COLUMN_NAME,105) AS DATE_VAL
,CASE
'+ (
SELECT
N'WHEN C.COLUMN_NAME = N' + NCHAR(39) + C.COLUMN_NAME + NCHAR(39) + N' THEN P.[' + C.COLUMN_NAME + N']
'
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = N'Planner'
AND C.TABLE_SCHEMA = N'dbo'
AND C.ORDINAL_POSITION > 2
FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(MAX)') + N' END AS COL_VAL
FROM INFORMATION_SCHEMA.COLUMNS C
OUTER APPLY dbo.Planner P
WHERE C.TABLE_NAME = @TABLE_NAME
AND C.TABLE_SCHEMA = @TABLE_SCHEMA
AND C.ORDINAL_POSITION > 2';
/*
PRINT @SQL_STR
*/
INSERT INTO @NORMALIZED_DATA (P_NAME,P_PROJECT_NAME,DATE_VAL,P_VALUE)
EXEC SP_EXECUTESQL @SQL_STR, @PARAM, @TABLE_NAME, @TABLE_SCHEMA;
DECLARE @TODAY DATE = '2014-07-23';
DECLARE @PER_LEN INT = 14;
DECLARE @FORTNIGHT DATE = DATEADD(DAY,14,@TODAY);
SELECT
ND.P_NAME
,ND.P_PROJECT_NAME
,@TODAY AS TODAY
,SUM(ND.P_VALUE) AS SUM_PERIOD
FROM @NORMALIZED_DATA ND
WHERE ND.DATE_VAL BETWEEN @TODAY AND @FORTNIGHT
GROUP BY ND.P_NAME,ND.P_PROJECT_NAME
/* CLEAN UP */
DROP TABLE dbo.Planner;
Results
P_NAME P_PROJECT_NAME TODAY SUM_PERIOD
------- --------------- ---------- -----------
Mike Audit 2014-07-23 5