Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

567 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

700 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  
 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.05 seconds. Powered By: Snitz Forums 2000