View Single Post
  #4 (permalink)  
Old March 9th, 2009, 01:12 PM
TComfort TComfort is offline
Registered User
 
Join Date: Mar 2009
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
Default Thanks for your help

Thanks, I got INTO into the right place. Here's the final code:

Code:
ALTER PROCEDURE
Code:
[dbo].[uspBuildShipsByDate]
@datToday date,
@intDaysBack int
AS
SET NOCOUNT ON
DECLARE @datBegin date;
DECLARE @datTheDate date;
DECLARE @intTheDay int;
DECLARE @vchProc varchar(4096);
DECLARE @Error int;
SET @datBegin = DATEADD(day, (@intDaysBack * -1), @datToday)
IF EXISTS (SELECT * FROM sys.objects
WHERE OBJECT_NAME(object_id) = 'tblShips'
AND SCHEMA_NAME(schema_id) = 'dbo' 
AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
DROP TABLE dbo.tblShips

 
SET @vchProc = 'SELECT USER_PART_NO '
SET @datTheDate = @datBegin
SET @intTheDay = @intDaysBack
 
WHILE @datTheDate < @datToday
BEGIN
SET @vchProc = @vchProc + ', [' + CONVERT(varchar,@datTheDate,101) + ']'
SET @vchProc = @vchProc + ' AS [Date-' + CONVERT(varchar, @intTheDay) + ']'
SET @datTheDate = DATEADD(day,1,@datTheDate)
SET @intTheDay = @intTheDay - 1
END
 
SET @vchProc = @vchProc + ', [' + CONVERT(varchar,@datTheDate,101) + ']'
SET @vchProc = @vchProc + ' AS [Date]'
SET @vchProc = @vchProc + ' INTO tblShips' 
SET @vchProc = @vchProc + ' FROM (Select User_part_no, Reqstd_dt, QTY ' 
SET @vchProc = @vchProc + 'FROM vwShips) as SourceTable '
SET @vchProc = @vchProc + 'PIVOT (SUM (QTY) FOR REQSTD_DT IN (' 
SET @datTheDate = @datBegin
 
WHILE @datTheDate < @datToday
BEGIN
SET @vchProc = @vchProc + ' [' + CONVERT(varchar,@datTheDate,101) + '], '
SET @datTheDate = DATEADD(day,1,@datTheDate)
END
 
SET @vchProc = @vchProc + '[' + + CONVERT(varchar,@datTheDate,101) + '] '
SET @vchProc = @vchProc + ')) as pvt '
SET @vchProc = @vchProc + 'ORDER by USER_PART_NO'
EXEC (@vchProc)