Case Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case Statement

Author

Topic

cekirge
Starting Member

4 Posts

Posted - 2006-01-03 : 17:07:06

Hello,
I have a sql statement that uses case statement to calculate a variable, I would like to use that variable in the same sql statement to calculate a different variable.
In my code Wed_Var is calculated, but since I dont know a way to use the Wed_Var in Wed_Adj, I copy the whole formula again.
Any help is appreciated.

Thanks

SELECT     TOP 100 PERCENT dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal, 
                      Wed_Var = CASE WHEN dbo.VIEW_ManPlan_Dock_Prev_Current.Tue = 0 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Tue_Var WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon
                       + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) 
                      / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date < .75 THEN .75 WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue)
                       / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date > 1.25 THEN 1.25 ELSE (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue)
                       / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date END, 
                      Wed_Adj = CASE WHEN dbo.VIEW_ManPlan_Dock_Prev_Current.Tue = 0 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Tue_Var * (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed
                       / 4) WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) 
                      / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date < .75 THEN .75 * (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) 
                      WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) 
                      / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date > 1.25 THEN 1.25 * (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) 
                      ELSE (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) * ((dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) 
                      / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date) END, 
                      Week_To_Date = CASE WHEN dbo.VIEW_ManPlan_Dock_Prev_Current.Tue = 0 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed
                       / 4) 
                      * dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Tue_Var WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue)
                       / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date < .75 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed
                       / 4) * .75 WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) 
                      / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date > 1.25 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed
                       / 4) * 1.25 ELSE dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) 
                      * ((dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) 
                      / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date) END
FROM         dbo.VIEW_ManPlan_Dock_Prev_4WK INNER JOIN
                      dbo.VIEW_ManPlan_Dock_Prev_Current ON 
                      dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal = dbo.VIEW_ManPlan_Dock_Prev_Current.Terminal INNER JOIN
                      dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday ON 
                      dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal = dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Terminal
ORDER BY dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-03 : 19:47:55

you can do something like this

select r.Terminal, r.Wed_Var, (r.Wed_Var * ...) as Wed_Adj, r.Week_To_Date
from
(
SELECT    TOP 100 PERCENT dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal, 
                      Wed_Var = CASE WHEN ... END, 
                      Week_To_Date = CASE WHEN ... END
FROM    dbo.VIEW_ManPlan_Dock_Prev_4WK INNER JOIN
                      dbo.VIEW_ManPlan_Dock_Prev_Current ON 
                      dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal = dbo.VIEW_ManPlan_Dock_Prev_Current.Terminal INNER JOIN
                      dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday ON 
                      dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal = dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Terminal
) as r
ORDER BY r.Terminal

-----------------
[KH]
2006 a new beginning

cekirge
Starting Member

4 Posts

Posted - 2006-01-04 : 10:42:44

Thank you that helped me a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources