select query with multiple conditions using left j

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 2008 Forums

Transact-SQL (2008)

select query with multiple conditions using left j

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-08-15 : 15:58:06

I need a select query, I am very confused how to set multiple conditions to get result output 4 columns.

Output columns.
select Act_unit, Active_status, Active_date, Inactive_date

left join between @GLNAME to @GLNAMESAU based on act_unit and max(R_Date) and Max(R_Time)

If act_units based on max(r_date) & max(r_time) is found in table two, i want to get the dates and populate as active_date & inactive_date based on status in the output

If act_unit is not found in table two, then need to use first table active_status I or A and populate hardcoded values in active_date and inactive date

if Active_status is 'I' in table 1
then inactivedate = to_date('12/31/9999','MM/DD/YYYY')
----------

If Active_status is 'A' in table 1 then hardcode
Active_date = to_date('01/01/1900','MM/DD/YYYY')


 
declare @GLNAME table (ACT_UNIT char(10), ACTIVE_STATUS char(1)
insert @GLNAME
select '34562', 'I' union all
select '32674', 'A' union all
select '33456', 'A' union all
select '99123', 'A' union all
select '92016', 'A' 



declare @GLNAMESAU table (ACT_UNIT char(10), R_DATE date, R_TIME int, ACTIVE_STATUS char(1)
insert @GLNAME
select '92016', '13-Aug-13',150434,'I' union all
select '92016', '13-Aug-13',151754,'A'

**************************
Thank you very much for helpful info.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-08-16 : 07:06:00

This is a MS SQL Server site and to_date() is not a MS SQL Server function.

In MS SQL Server, maybe you want something like:


;WITH SAU
AS
(
	SELECT ACT_UNIT, R_DATE, R_TIME, ACTIVE_STATUS
		,ROW_NUMBER() OVER (PARTITION BY ACT_UNIT ORDER BY R_DATE DESC, R_TIME DESC) AS rn
	FROM @GLNAMESAU
)
SELECT N.ACT_UNIT, N.ACTIVE_STATUS
	,CASE
		WHEN S.R_DATE IS NOT NULL
		THEN S.R_DATE
		WHEN N.ACTIVE_STATUS = 'I'
		THEN CAST('99991231' as date)
		ELSE CAST('19000101' as date)
	END AS R_DATE
	,COALESCE(S.R_TIME, 0) AS R_TIME
FROM @GLNAME N
	LEFT JOIN SAU S
		ON N.ACT_UNIT = S.ACT_UNIT
			AND S.rn = 1;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources