Conditional SQL statement..Please help

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)

Conditional SQL statement..Please help

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-01 : 14:47:09

Divya writes "Hello Gurus,
I have the following question forMS SQL Server
I have a Status field in a table based on whose value, I need to calculate "Number of Days".
If the Status = "Open", then the formula for the Number of days is avg(DATEDIFF(DAY, S_SRV_REQ.CREATED, GETDATE()))
and if the Status = "Closed", then the formula changes a little bit.
Is there any way I could use a conditional query in sql server like CASE or anything like to to fulfil the above condition.

I wrote the following not very easy to understand sql

SELECT S_SRV_REQ.SR_TYPE_CD, S_ORG_EXT.NAME , S_SRV_REQ.SR_STAT_ID, S_SRV_REQ.SR_SEV_CD, (select avg(DATEDIFF(DAY, S_SRV_REQ.CREATED, GETDATE())) WHERE S_SRV_REQ.SR_STAT_ID='Open') AS Average_Days_Open, avg(DATEDIFF(DAY, S_SRV_REQ.CREATED, S_SRV_REQ.ACT_CLOSE_DT)) AS Average_Days_SR_To_Close
FROM S_SRV_REQ, S_ORG_EXT
WHERE S_SRV_REQ.SR_STAT_ID='Closed'
GROUP BY S_SRV_REQ.SR_TYPE_CD, S_ORG_EXT.NAME, S_SRV_REQ.SR_SEV_CD, S_SRV_REQ.SR_STAT_ID, S_SRV_REQ.ACT_CLOSE_DT
ORDER BY S_SRV_REQ.SR_TYPE_CD, S_ORG_EXT.NAME, S_SRV_REQ.SR_SEV_CD, S_SRV_REQ.SR_STAT_ID, S_SRV_REQ.ACT_CLOSE_DT

But it does not seem to be outputting any results for Status = Open when there are records with Status as Open in other words, my query is not working fine.

Please help me out
Thanks,
Divya"

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-01 : 15:04:46

It would help if you posted some sample data / desired results. Im not sure if this is what you are asking for:

declare @test table (test_id int identity(1,1), status varchar(10), created datetime, act_close_dt datetime)
insert into @test (status, created, act_close_dt)
	select 'open', getdate()-1, getdate()+10 union
	select 'closed', getdate()-10, getdate()-5

select 	test_id,
	status, 
	case status
		when 'open' then avg(DATEDIFF(DAY, created, GETDATE()))
	end as 'avg_days_open',
	case status
		when 'closed' then avg(DATEDIFF(DAY, created, act_close_dt))
	end as 'avg_days_closed'
from @test
group by test_id, status

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources