Help with Variables or functions

[spiff2002]

Hi all
I'm trying to create a sales goal table

My current query looks like this

select 
brand,
goal,
state,
goaljan,
goalfeb-((salesjan-goaljan)/11) as goalfeb,
case 
   when month(getdate())>=2 then goalmar-(((salesfeb-goalfeb)/10)+((salesjan-goaljan)/11))
   when month(getdate())=1  then goalmar-((salesjan-goaljan)/11)
end as goalmar,
case 
   when month(getdate())>=3 then goalapr-(((salesmar-goalmar)/9)+((salesfeb-goalfeb)/10)+((salesjan-goaljan)/11))
   when month(getdate())=2  then goalapr-(((salesfeb-goalfeb)/10)+((salesjan-goaljan)/11))
   when month(getdate())=1  then goalapr-((salesjan-goaljan)/11)
end as goalapr

from sales

There some problems:
1. Calculating goalmar (goals for the month of March)
should be

case 
   when month(getdate())>=2 then goalmar-(((salesfeb-goalfeb-((salesjan-goaljan)/11))/10)+((salesjan-goaljan)/11))
   when month(getdate())=1  then goalmar-((salesjan-goaljan)/11)
end as goalmar

In this case I replaced "goalfeb" with the new definition of goalfeb "goalfeb-((salesjan-goaljan)/11)". Can I create a variable called newgoalfeb=goalfeb-((salesjan-goaljan)/11)???
2. This has to be done for all months so if I'm able to create functions or variables, that would save me a lot of coding.

Like you can see I'm very confused
I'm new to MS SQL and I'm not familir with variables or user defined functions.

Thanks a lot
Spiff

 

[NoCoolHandle]

Maybe it would be nice to see some sample data and also sample results..

It seems like a crosstab query might solve your problem but without seeing data that would generate teh result it is a hard call.


Rob

 

[ESquared]

spiff2002, I think you would find everything simplified by a huge amount if you switched from a "wide" format to a "tall" one.

Instead of fields like so

empID goaljan goalfeb goalmar goalapr ...
1 500 600 750 900
do it like so:

empID mo goal
1 1 500
1 2 600
1 3 750
1 4 900

Then do all your calculations and such, and when you are finally ready to display results, *then* use the CASE syntax to move them back to "wide." (In the next version of SQL Server, pivot tables will be part of standard syntax.)

SELECT
   empID,
   goaljan = CASE Mo WHEN 1 THEN SUM(goal) END,
   goalfeb = CASE Mo WHEN 2 THEN SUM(goal) END,
   goalmar = CASE Mo WHEN 3 THEN SUM(goal) END,
   goalapr = CASE Mo WHEN 4 THEN SUM(goal) END
...

-------------------------------------
A sacrifice is harder when no one knows you've made it.