MAX queries - 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

SQL Server 2000 Forums

Transact-SQL (2000)

MAX queries

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-09 : 21:22:37

[code]MasterID emp_task_date emp_end_date
4657 NULL NULL
4657 01/05/2006 10/06/2006
4657 02/05/2006 11/06/2006
4657 NULL NULL
4657 04/05/2006 15/06/2006 [/code]

Here I want to get the next_task_attempt_date

Condition : IF Max of emp_task_date IS null then
get the max of emp_end_date else max of emp_task_date is my next_task_attempt_date

I tried this query..Is this a correct one : I am currently getting the correct results but doesn't cover every scenario :
SELECT
CASE
WHEN MAX(tbl_task_dates.emp_task_date)IS NULL THEN MAX(tbl_task_dates.emp_end_date)
ELSE MAX(tbl_task_dates.emp_task_date)
END AS next_task_attempt_date
FROM
tbl_task_dates
INNER JOIN tbl_control_dates ON tbl_task_dates .ID= tbl_control_dates.MasterID AND
tbl_task_dates.masterID='4657' AND
tbl_control_dates.emp_number='95677'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-09 : 21:48:41

can you post the relevant table structure and more sample data ? especially the sets that your query does not cover

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-09 : 23:32:34

The above mentioned is the tbl_task_dates table :

tbl_control_dates :

emp_number MasterID
95677 4657
10576 6787

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-09 : 23:42:49

quote:
Condition : IF Max of emp_task_date IS null then
get the max of emp_end_date else max of emp_task_date is my next_task_attempt_date

base on above condition, you can do it in this way


next_task_attemp_date = coalesce(max(emp_task_date), max(emp_end_date))

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-10 : 01:57:18

next_task_attemp_date = ISNULL(max(emp_task_date), max(emp_end_date)) also is giving me the same result as above :

One doubt I have is : When we use isnull or coalesce it compares row by row.In this case will it take first the max of emp_task_date and if that is null then only take the max of emp_end_date

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 02:36:56

[code]SELECT CASE
WHEN d.opt1 IS NOT NULL THEN d.opt1
ELSE d.opt2
END
FROM (
SELECT MAX(td.emp_task_date) opt1,
MAX(td.emp_end_date) opt2
FROM tbl_task_dates td
INNER JOIN tbl_control_dates cd ON td.ID= cd.MasterID AND cd.emp_number = '95677'
WHERE td.masterID = '4657'
) d[/code]

Peter Larsson
Helsingborg, Sweden

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-10 : 03:00:12

I think this will solve my issue.Let me try putting it together

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources