how to sort on calculated field?

[Jesse]

I'm querying from a sql 2k database...from a single take. I'm ok selecting
the data I want. I need to group the value of a calculated field....and I
don't know how to do this? The calculated field is to identify the status of
the entry for a project task (late to start, late to finish, etc). Here's my
query:

select proj_id, task_uid, task_id, task_name
task_start_date, task_dur, task_finish_date,
task_pct_comp, task_pct_work_comp
task_is_summary, task_is_milestone
from dbo.MSP_TASKS
where proj_id = 819
group by task_status
order by task_id

here's the pseudocode for the task_status calculated field:

([% Work Complete]=100 And [Finish]>[Current Date]-7) = "finished last week"
[% Work Complete]=100 = "finished"
([% Complete]=0 And [Start]>[Current Date]-1 And [Start]<[Current Date]+7) =
"start within 1 week"
([% Complete]=0 And [Current Date]>[Start]) = "late to start"
[% Complete]=0 = "not started"
([% Complete]>0 And [% Work Complete]<100 And [Current Date]>[Finish]) =
"late to finish"
([% Complete]>0 And [% Work Complete]<100) = "started")

Can someone help me to create and group by this calculated "status" field?

 

[John Spencer]

You can use the switch function to do this. Switch handles matched pairs of
arguments. First argument (in the pair) is a boolean statement of some kind
and the second argument is the value to return. Switch returns the argument
of the first pair where the boolean statement is true.

It will probably be slow with large sets of data.
SWITCH (
task_pct_Comp = 100 and task_Finish_Date > Date()-7,"Finished Last Week",
task_pct_Comp = 100, "Finished",
task_pct_Comp = 0 and Task_Start_Date>Date()-1 and task_start_date <
Date()+7, "Start within 1 week",
....) as task_status



SELECT proj_id
, task_uid
, task_id
, task_name
, task_start_date
, task_dur
, task_finish_date,
, task_pct_comp
, task_pct_work_comp
, task_is_summary
, task_is_milestone
, SWITCH (task_pct_Comp = 100 and task_Finish_Date > Date()-7,"Finished Last
Week",
task_pct_Comp = 100, "Finished",
task_pct_Comp = 0 and Task_Start_Date>Date()-1 and task_start_date <
Date()+7, "Start within 1 week,
....) as task_status
FROM dbo.MSP_TASKS
WHERE proj_id = 819

 

[TedMi]

The Switch function will provide the various values for the calc field, but
it alone will not allow grouping, unless you repeat the whole Switch in the
GROUP BY. Create a view where the column holding the output of the Switch
statement is named AS Task_Status.
Then run another query against the view, and you can use the Task_Status
column by name in GROUP BY

 

[Jesse]

Thanks Ted, I'll give this a try!

 

[Jesse]

Thanks John! I'll give this a try
--
Jesse

You can use the switch function to do this. Switch handles matched pairs of
arguments. First argument (in the pair) is a boolean statement of some kind
and the second argument is the value to return. Switch returns the argument
of the first pair where the boolean statement is true.

It will probably be slow with large sets of data.
SWITCH (
task_pct_Comp = 100 and task_Finish_Date > Date()-7,"Finished Last Week",
task_pct_Comp = 100, "Finished",
task_pct_Comp = 0 and Task_Start_Date>Date()-1 and task_start_date <
Date()+7, "Start within 1 week",
....) as task_status



SELECT proj_id
, task_uid
, task_id
, task_name
, task_start_date
, task_dur
, task_finish_date,
, task_pct_comp
, task_pct_work_comp
, task_is_summary
, task_is_milestone
, SWITCH (task_pct_Comp = 100 and task_Finish_Date > Date()-7,"Finished Last
Week",
task_pct_Comp = 100, "Finished",
task_pct_Comp = 0 and Task_Start_Date>Date()-1 and task_start_date <
Date()+7, "Start within 1 week,
....) as task_status
FROM dbo.MSP_TASKS
WHERE proj_id = 819

I'm querying from a sql 2k database...from a single take. I'm ok selecting
the data I want. I need to group the value of a calculated field....and I
don't know how to do this? The calculated field is to identify the status
of
the entry for a project task (late to start, late to finish, etc). Here's
my
query:

select proj_id, task_uid, task_id, task_name
task_start_date, task_dur, task_finish_date,
task_pct_comp, task_pct_work_comp
task_is_summary, task_is_milestone
from dbo.MSP_TASKS
where proj_id = 819
group by task_status
order by task_id

here's the pseudocode for the task_status calculated field:

([% Work Complete]=100 And [Finish]>[Current Date]-7) = "finished last
week"
[% Work Complete]=100 = "finished"
([% Complete]=0 And [Start]>[Current Date]-1 And [Start]<[Current Date]+7)
=
"start within 1 week"
([% Complete]=0 And [Current Date]>[Start]) = "late to start"
[% Complete]=0 = "not started"
([% Complete]>0 And [% Work Complete]<100 And [Current Date]>[Finish]) =
"late to finish"
([% Complete]>0 And [% Work Complete]<100) = "started")

Can someone help me to create and group by this calculated "status" field?