Is it possible to create a temp table with dynamic columns?
-
Why not simply
select * from JobLines where StartDate >= @StartDate
and then let the client display it on the above form?
-
Well, that is one way to do it.
But, I was hoping that this can be done in stored procedure.
-
Try this
DECLARE @Mode varchar(10), @StartDate char(8), @sql varchar(4000)
SET @Mode = 'Weekly'
SET @StartDate = '20050804'
SET @sql = '
DECLARE @StartDate datetime SET @StartDate='''+@startdate+''' SELECT DISTINCT 1,l.ResourceDesc,l.ResourceDesc AS [Desc]'
SELECT @sql = @sql + ','''' AS [' + REPLACE(CONVERT(char(6),@startdate,113),' ','-') + ']'
FROM master.dbo.spt_values n WHERE n.type='P' AND n.number between 0 AND 6
SET @sql = @sql + '
FROM [JobLines] l
INNER JOIN master.dbo.spt_values n ON n.type=''P'' AND n.number between 0 AND 6
AND @StartDate+n.number BETWEEN l.StartDate AND l.EndDate
UNION
SELECT 2,l.ResourceDesc,l.JobNo,
MAX(CASE WHEN n.number=0 THEN h.Status ELSE NULL END),
MAX(CASE WHEN n.number=1 THEN h.Status ELSE NULL END),
MAX(CASE WHEN n.number=2 THEN h.Status ELSE NULL END),
MAX(CASE WHEN n.number=3 THEN h.Status ELSE NULL END),
MAX(CASE WHEN n.number=4 THEN h.Status ELSE NULL END),
MAX(CASE WHEN n.number=5 THEN h.Status ELSE NULL END),
MAX(CASE WHEN n.number=6 THEN h.Status ELSE NULL END)
FROM [JobLines] l
CROSS JOIN (SELECT n2.number FROM master.dbo.spt_values n2 WHERE n2.type=''P'' AND n2.number between 0 AND 6) n
LEFT OUTER JOIN [JobHeader] h ON h.JobNo = l.JobNo AND @StartDate+n.number BETWEEN l.StartDate AND l.EndDate
GROUP BY l.ResourceDesc,l.JobNo
HAVING COUNT(h.Status)>0
ORDER BY 2,1,3'
EXECUTE(@sql)
Far away is close at hand in the images of elsewhere.
Anon. -
Of course you can create the temp table and then run ALTER TABLE statements against it to add columns dynamically. This won't work with table variables though.
-
David,
Thanks so much!
I hope I am not asking for too much by asking you to point me in the right direction if I wanna do a Monthly and Quarterly view.If I were to modify that script you wrote, how can I determine if that Month has 28/29/30/31 days? or when it comes to Quarterly view, how best can I re-write this script?
Thank you once again!
-
Monthly below and does 1 month from the start date.
Depending on what Quarterly means you could revise this accordingly
DECLARE @NoofDays int, @StartDate char(8), @sql varchar(4000)
SET @StartDate = '20050801'
SET @NoofDays = DATEDIFF(day,@StartDate,DATEADD(month,1,@StartDate)) - 1
SET @sql = '
DECLARE @StartDate datetime SET @StartDate='''+@startdate+'''
SELECT DISTINCT 1,l.ResourceDesc,l.ResourceDesc AS [Desc]'
SELECT @sql = @sql + ','''' AS [' + REPLACE(CONVERT(char(6),CAST(@startdate as datetime)+n.number,113),' ','-') + ']'
FROM master.dbo.spt_values n WHERE n.type='P' AND n.number between 0 AND @NoofDays
SET @sql = @sql + ' FROM [JobLines] l
INNER JOIN master.dbo.spt_values n ON n.type=''P'' AND n.number between 0 AND '+CAST(@NoofDays as varchar)+'
AND @StartDate+n.number BETWEEN l.StartDate AND l.EndDate
UNION
SELECT 2,l.ResourceDesc,l.JobNo'
SELECT @sql = @sql + ',MAX(CASE WHEN n.number='+CAST(n.number as varchar)+' THEN h.Status ELSE NULL END)'
FROM master.dbo.spt_values n WHERE n.type='P' AND n.number BETWEEN 0 AND @NoofDays
SET @sql = @sql + ' FROM [JobLines] l
CROSS JOIN (SELECT n2.number FROM master.dbo.spt_values n2 WHERE n2.type=''P'' AND n2.number BETWEEN 0 AND '+CAST(@NoofDays as varchar)+') n
LEFT OUTER JOIN [JobHeader] h ON h.JobNo = l.JobNo AND @StartDate+n.number BETWEEN l.StartDate AND l.EndDate
GROUP BY l.ResourceDesc,l.JobNo
HAVING COUNT(h.Status)>0
ORDER BY 2,1,3'
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks, David! You are a life-saver!
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply