April 12, 2006 at 6:31 am
Hi, I have a view that contains rows with the id of a person and a date that they worked. I would like to create a stored procedure that accepts a start date and end date and returns a result set that contains one row per person and columns for each of the dates between the 2 dates entered. The value in each of the columns would be the number of times that person and date occured in the original view.
I have done cross tabs like this before, casting the columns to varchar, but this is becoming problematic in this situation because the ordering is not correct of the dates. Also, I need a way to ensure that all dates are included between the 2 requested dates even if there are no records in the view that contain a particular date.
View columns: techid, startdate
Thanks,
Andrew
April 12, 2006 at 7:59 am
DECLARE @start datetime, @end datetime, @sql nvarchar(200)
SET @start = '20060401'
SET @end = '20060420'
SET NOCOUNT ON
CREATE TABLE #temp (techid int)
INSERT INTO #temp (techid) SELECT DISTINCT techid FROM [view]
WHILE @start <= @end
BEGIN
SET @sql = 'ALTER TABLE #temp ADD ['+CONVERT(varchar,@start,103)+'] int'
--select @sql
EXEC(@sql)
SET @sql = 'UPDATE t SET t.['+CONVERT(varchar,@start,103)+'] = ISDATE(v.startdate) FROM #temp t LEFT OUTER JOIN [view] v ON v.techid = t.techid AND v.startdate = '''+CONVERT(varchar,@start,120)+''''
--select @sql
EXEC(@sql)
SET @start = DATEADD(day,1,@start)
END
SET NOCOUNT OFF
SELECT * FROM #temp
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon.
April 13, 2006 at 8:43 am
Thanks David, that will do the trick.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply