SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select query with multiple conditions using left j
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

540 Posts

Posted - 08/15/2013 :  15:58:06  Show Profile  Reply with Quote
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.

Edited by - cplusplus on 08/15/2013 16:47:18

Ifor
Aged Yak Warrior

590 Posts

Posted - 08/16/2013 :  07:06:00  Show Profile  Reply with Quote
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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000