MAX of different Columns - 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 of different Columns

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-20 : 01:38:58

[code]SELECT
emp_number,
fee_type_code,
CASE
WHEN fee_type_code='A' THEN processing_date
ELSE
NULL
END as join_dt,
CASE
WHEN fee_type_code='P' THEN processing_date
ELSE
NULL
END as paid_dt,
CASE
WHEN fee_type_code='W' THEN processing_date
ELSE
NULL
END as end_dt,
fee_amount

FROM
tbl_emp_fees_info
WHERE
emp_number=@emp_number AND
accessed='N' AND
indicator IN ('C') AND
processing_date >
(
SELECT --Max(join_dt,paid_dt and end_dt)

FROM tbl_corporate_fee
WHERE emp_number=@emp_number
) [/code]

In the above query I need to get the max of join_dt,paid and end_dt which are 3 different columns in the tbl_corporate_fee table.So that all the records after the max date from that table can be inserted to tbl_corporate_fee table

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-20 : 01:53:10

Somthing like this


SELECT 
		emp_number,
		fee_type_code,
		CASE
			WHEN fee_type_code='A' THEN processing_date
			ELSE
				NULL
		END as join_dt,
		CASE
			WHEN fee_type_code='P' THEN processing_date
			ELSE
				NULL
		END as paid_dt,
		CASE
			WHEN fee_type_code='W' THEN processing_date
			ELSE
				NULL
		END as end_dt,
		fee_amount
		
	FROM 
		tbl_emp_fees_info 
	WHERE
		emp_number=@emp_number AND
		accessed='N' AND
		indicator IN ('C')	AND
		processing_date > 
				(
				SELECT Case When (Max(join_dt)>MAx(paid_dt)) and (Max(join_dt)>MAx(end_dt)) Then Max(join_dt)
					    When (Max(paid_dt)>MAx(join_dt)) and (Max(paid_dt)>MAx(end_dt)) Then Max(paid_dt)
					     Else Max(end_dt) End	 
					
				FROM tbl_corporate_fee
				WHERE emp_number=@emp_number
				)

Chirag

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-20 : 02:18:12

Thanks a lot..But there is one issue here...
IF the emp_number is a first time insert to tbl_corporate_fee the MAX date will be NULL .Is there any way to avoid the first insert for each emp_number

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-20 : 02:31:42

quote:
Originally posted by sqllearner

Thanks a lot..But there is one issue here...
IF the emp_number is a first time insert to tbl_corporate_fee the MAX date will be NULL .Is there any way to avoid the first insert for each emp_number

is it one of the column join_dt, paid_id or end_dt value is NULL ?

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-20 : 02:33:36

3 columns are NULLable.So If 3 of them are NULL then

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-20 : 02:46:34