Using parameters to name columns

[scoobyroo]

The stored procedure below now needs to reference the actual period instead of M1, M2, etc..
So, for example if @Period1 = 200802 then the column would be named 200802 instead of M1.

CURRENT - SUM(CASE WHEN p.period = @Period1 THEN t.tworkhrs ELSE 0 END) M1

NEED TO BE - SUM(CASE WHEN p.period = @Period1 THEN t.tworkhrs ELSE 0 END) 200802


Is there a way to do this in the stored procedure?



CREATE PROCEDURE dbo.TIME_BillHrsRolling13monthsTitleGraph

@EndPeriod INT

AS

DECLARE @Period1 INT
, @Period2 INT
, @Period3 INT
, @Period4 INT
, @Period5 INT
, @Period6 INT
, @Period7 INT
, @Period8 INT
, @Period9 INT
, @Period10 INT
, @Period11 INT
, @Period12 INT

IF RIGHT(@EndPeriod, 2) - 1 = 0
SELECT @Period12 = @EndPeriod - 89
ELSE
SELECT @Period12 = @EndPeriod - 1

IF RIGHT(@Period12, 2) - 1 = 0
SELECT @Period11 = @Period12 - 89
ELSE
SELECT @Period11 = @Period12 - 1

IF RIGHT(@Period11, 2) - 1 = 0
SELECT @Period10 = @Period11 - 89
ELSE
SELECT @Period10 = @Period11 - 1

IF RIGHT(@Period10, 2) - 1 = 0
SELECT @Period9 = @Period10 - 89
ELSE
SELECT @Period9 = @Period10 - 1

IF RIGHT(@Period9, 2) - 1 = 0
SELECT @Period8 = @Period9 - 89
ELSE
SELECT @Period8 = @Period9 - 1

IF RIGHT(@Period8, 2) - 1 = 0
SELECT @Period7 = @Period8 - 89
ELSE
SELECT @Period7 = @Period8 - 1

IF RIGHT(@Period7, 2) - 1 = 0
SELECT @Period6 = @Period7 - 89
ELSE
SELECT @Period6 = @Period7 - 1

IF RIGHT(@Period6, 2) - 1 = 0
SELECT @Period5 = @Period6 - 89
ELSE
SELECT @Period5 = @Period6 - 1

IF RIGHT(@Period5, 2) - 1 = 0
SELECT @Period4 = @Period5 - 89
ELSE
SELECT @Period4 = @Period5 - 1

IF RIGHT(@Period4, 2) - 1 = 0
SELECT @Period3 = @Period4 - 89
ELSE
SELECT @Period3 = @Period4 - 1

IF RIGHT(@Period3, 2) - 1 = 0
SELECT @Period2 = @Period3 - 89
ELSE
SELECT @Period2 = @Period3 - 1

IF RIGHT(@Period2, 2) - 1 = 0
SELECT @Period1 = @Period2 - 89
ELSE
SELECT @Period1 = @Period2 - 1


SELECT CASE WHEN h.httitle = 'PARTNER' THEN 'Partner'
WHEN h.httitle in ('PARALEGAL','CASE ASSISTANT') THEN 'Paralegal'
WHEN h.httitle in ('ASSOCIATE','CONTRACT ATTY','OF COUNSEL','SENIOR COUNSEL','LAW CLERK','PATENT AGENT') THEN 'Associate'
END Title
, SUM(CASE WHEN p.period = @Period1 THEN t.tworkhrs ELSE 0 END) M1
, SUM(CASE WHEN p.period = @Period2 THEN t.tworkhrs ELSE 0 END) M2
, SUM(CASE WHEN p.period = @Period3 THEN t.tworkhrs ELSE 0 END) M3
, SUM(CASE WHEN p.period = @Period4 THEN t.tworkhrs ELSE 0 END) M4
, SUM(CASE WHEN p.period = @Period5 THEN t.tworkhrs ELSE 0 END) M5
, SUM(CASE WHEN p.period = @Period6 THEN t.tworkhrs ELSE 0 END) M6
, SUM(CASE WHEN p.period = @Period7 THEN t.tworkhrs ELSE 0 END) M7
, SUM(CASE WHEN p.period = @Period8 THEN t.tworkhrs ELSE 0 END) M8
, SUM(CASE WHEN p.period = @Period9 THEN t.tworkhrs ELSE 0 END) M9
, SUM(CASE WHEN p.period = @Period10 THEN t.tworkhrs ELSE 0 END) M10
, SUM(CASE WHEN p.period = @Period11 THEN t.tworkhrs ELSE 0 END) M11
, SUM(CASE WHEN p.period = @Period12 THEN t.tworkhrs ELSE 0 END) M12
, SUM(CASE WHEN p.period = @EndPeriod THEN t.tworkhrs ELSE 0 END) M13
, SUM(CASE WHEN p.period between @Period2 and @EndPeriod THEN t.tworkhrs ELSE 0 END) Total12Months
, SUM(CASE WHEN p.period between @Period2 and @EndPeriod THEN t.tworkhrs ELSE 0 END)/12 Avg12Months
FROM timecard t
INNER JOIN matter m ON t.tmatter = m.mmatter
INNER JOIN htkeep h ON t.ttk = h.htkinit
INNER JOIN eis_period p ON t.twoper = p.per
WHERE t.tstatus in ('B','PB','BNC','BNP','H','WA')
AND p.end_date between h.htdate1 and h.htdate2
AND h.httitle in ('PARTNER','PARALEGAL','CASE ASSISTANT','ASSOCIATE','CONTRACT ATTY','OF COUNSEL','SENIOR COUNSEL','LAW CLERK','PATENT AGENT')
GROUP BY CASE WHEN h.httitle = 'PARTNER' THEN 'Partner'
WHEN h.httitle in ('PARALEGAL','CASE ASSISTANT') THEN 'Paralegal'
WHEN h.httitle in ('ASSOCIATE','CONTRACT ATTY','OF COUNSEL','SENIOR COUNSEL','LAW CLERK','PATENT AGENT') THEN 'Associate'
END

 

[ESquared]

I don't have a full answer for you but there are many things I see.

Period should not be an integer column if it is, and it shouldn't be a varchar column if it is. It should be a datetime column with the day value set to 1. Then all this stuff becomes so much easier.

There are other things:

IF RIGHT(@EndPeriod, 2) - 1 = 0
-->
IF RIGHT(@EndPeriod, 2) = 1 --??
-->
SET @Period12 = DateAdd(mm, @EndPeriod / 100 - 2, DateAdd(yy, @EndPeriod % 100 - 1900, 0))
-->
SET @Period12 = @EndPeriod - CASE WHEN @EndPeriod % 100 = 1 THEN 89 ELSE 1 END

There is also no "WHERE p.period between @Period2 AND @EndPeriod" so it's scanning your entire table every time.

Really... storing dates as varchar '200807' or as an integer 200,807 is not good, for reasons you have discovered in trying to query the dang stuff.

To answer your actual question, no you can't have dynamic alias names without dynamic SQL. Best practice is to have the client program change the aliases. Or to have the client do the pivoting.

And is this SQL 2000 or 2005?

 

[scoobyroo]

Unfortunately, the database I am pulling from stores period as an integer. I can't change the way it is stored. I'm just trying to work with what I've got. This is SQL 2000. Normally, I do change the aliases in the client program. However, they want this report in Excel and I've never done this in Excel before. Guess, I will need to figure it out. Thanks for the input.

 

[ESquared]

The / and % in my expression above should be reversed.

Can you create a view?

CREATE VIEW eis_period_withdates
AS
SELECT
   Column1,
   Column2,
   Period = DateAdd(mm, Period % 100, DateAdd(yy, Period / 100 - 1900, 0)),
   RestOfColumns
FROM eis_period

DECLARE
   @SQL1 varchar(8000),
   @SQL2 varchar(8000),
   @SQL3 varchar(8000),
   @EndPeriodDate datetime

SET @EndPeriodDate = DateAdd(mm, @EndPeriod % 100, DateAdd(yy, @EndPeriod / 100 - 1900, 0))

SET @SQL1 = 'SELECT
   X.Title,'

SET @SQL2 = ''
SELECT @SQL2 = @SQL2 + '
   Sum(CASE p.Period WHEN ''' + Convert(varchar(30), DateAdd(mm, -Diff, @EndPeriod)) + ''' THEN  END [' + Left(Convert(varchar(30), DateAdd(mm, -Diff, @EndPeriod), 112), 6) + '],'
FROM
   (
      SELECT Diff = 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
   ) X
ORDER BY
   Diff DESC

SET @SQL3 = '
    SUM(CASE WHEN p.period between ''' + Convert(varchar(30), DateAdd(mm, -11, @EndPeriod)) + ''' and ''' + Convert(varchar(30), @EndPeriod) + ''' THEN t.tworkhrs ELSE 0 END) Total12Months,
    SUM(CASE WHEN p.period between ''' + Convert(varchar(30), DateAdd(mm, -11, @EndPeriod)) + ''' and ''' + Convert(varchar(30), @EndPeriod) + ''' THEN t.tworkhrs ELSE 0 END)/12 Avg12Months
FROM timecard t
   INNER JOIN matter m ON t.tmatter = m.mmatter
   INNER JOIN htkeep h ON t.ttk = h.htkinit 
   INNER JOIN eis_period_withdates p ON t.twoper = p.per
   INNER JOIN ( -- or better, put this in a table!
      SELECT ''PARTNER'' httitle, ''Partner'' Title
      UNION ALL SELECT ''PARALEGAL'', ''Paralegal''
      UNION ALL SELECT ''CASE ASSISTANT'', ''Paralegal''
      UNION ALL SELECT ''ASSOCIATE'', ''Associate''
      UNION ALL SELECT ''CONTRACT ATTY'', ''Associate''
      UNION ALL SELECT ''OF COUNSEL'', ''Associate''
      UNION ALL SELECT ''SENIOR COUNSEL'', ''Associate''
      UNION ALL SELECT ''LAW CLERK'', ''Associate''
      UNION ALL SELECT ''PATENT AGENT'', ''Associate''
   ) X ON h.httitle = x.httitle
WHERE
   t.tstatus in (''B'', ''PB'', ''BNC'', ''BNP'', ''H'', ''WA'')
   AND p.end_date between h.htdate1 and h.htdate2 
   AND p.period between ''' + Convert(varchar(30), DateAdd(mm, -12, @EndPeriod)) + ''' and ''' + Convert(varchar(30), @EndPeriod) + '''
GROUP BY
   X.Title'

IF @Debug = 1
   PRINT @SQL1 + @SQL2 + @SQL3
   ELSE EXEC (@SQL1 + @SQL2 + @SQL3)

This is TOTALLY untested, but should get you started. I can't believe I did this for you.

Please keep in mind that dynamic SQL is evil and you should only give yourself over to its darkness when there is absolutely no other way to avoid it.