Using variable in select statement?

[scoobyroo]

I need to create a report that tallies up the headcount of attorneys. Attorneys can have three different types of headcount(pay, work and count). They want
to be able to run the report for whichever headcount type they choose.

The simplest example of the select statement is as follows

select title
, sum(work)
from employeemonthfte
where period between @BeginPeriod and @EndPeriod


I need to be able to replace sum(work) with whatever headcount they choose (pay, work or count). I'm not sure how to go about doing this. Any help would be appreciated.

 

[bborissov]

If they have only 3 different types of headcount you could create a Stored Procedure and pass the type you want to sum, then it that SP you could have:

IF @ParForCount = 1 
    select title
         , sum(work) AS SomeName
    from employeemonthfte
    where period between @BeginPeriod and @EndPeriod
ELSE IF @ParForCount = 2
    select title
         , sum(pay) AS SomeName -- the same name as in the first case
    from employeemonthfte
    where period between @BeginPeriod and @EndPeriod
ELSE IF @ParForCount = 3
    select title
         , sum([count]) AS SomeName -- the same name as in the first case
    from employeemonthfte
    where period between @BeginPeriod and @EndPeriod

If these type can grow you can use Dynamic SQL.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[scoobyroo]

Thank you very much. I think this is exactly what I need.