IF Exists problem - 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

SQL Server Development (2000)

IF Exists problem

Author

Topic

solent
Starting Member

33 Posts

Posted - 2005-11-09 : 07:56:48

Basically i have the following code which first of all i dont even know if this is the best/correct way to do this but anyway if you can point me something better it will be great. But my problem here is that on the IF exists statement it seems that that my second part of the where clauses is not been calculated thus resulting on moving on the ELSE statment. Any ideas how i can fix this?

IF exists (SELECT h_BOND_PD_RA, h_LI_PERIOD5 FROM dbo.tbl_investment_H WHERE ((h_BOND_PD_RA >= N'3') OR (h_LI_PERIOD5 >= N'3')))
BEGIN

UPDATE    dbo.tbl_investment_H
SET              h_band = '4 to 5 years'
WHERE     (DATEDIFF(day, ReportDate, h_MATURITY) / 30.4167 > '48') AND  (DATEDIFF(day, ReportDate, h_MATURITY) / 30.4167 <= '60') 

END
ELSE

UPDATE    dbo.tbl_investment_H
SET              h_band = '4,3 to 5,7 years'
WHERE     (DATEDIFF(day, ReportDate, h_MATURITY) / 30.4167 > '51.599') AND  (DATEDIFF(day, ReportDate, h_MATURITY) / 30.4167 <= '68.399')

Sample Data.
h_BOND_PD_RA --> 6
h_LI_PERIOD5 --> 0
h_band --> 4,3 to 5,7 years
DATEDIFF is --> 55

The above is wrong as h_band should have been 4 to 5 years

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-09 : 07:59:38

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

solent
Starting Member

33 Posts

Posted - 2005-11-09 : 08:05:32

i did

Sample Data.
h_BOND_PD_RA --> 6
h_LI_PERIOD5 --> 0
DATEDIFF is --> 55

and h_band should come out as '4 to 5 years' but it comes out as 4,3 to 5,7 years

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-09 : 08:07:57

Why do you divide it by 30.4167?

Madhivanan

Failing to plan is Planning to fail

solent
Starting Member

33 Posts

Posted - 2005-11-09 : 08:15:45

365 days divided by 12 months =30.4167

but thats not where my problem is. That value is coming out as i want it to come.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-09 : 08:34:49

You didnt give the sample data

Refer this



Declare @t table(h_BOND_PD_RA varchar(10),h_LI_PERIOD5 varchar(10),ReportDate datetime, h_MATURITY datetime, 
h_band varchar(100))

insert into @t Select '5','1',getdate()-1655,getdate(),''
  
IF exists (SELECT h_BOND_PD_RA, h_LI_PERIOD5 FROM @t WHERE ((h_BOND_PD_RA >= N'3') OR (h_LI_PERIOD5 >= N'3')))
BEGIN
	UPDATE    @t
	SET              h_band = '4 to 5 years'
	WHERE     (DATEDIFF(day, ReportDate, h_MATURITY) / 30.4167 > '48') AND  (DATEDIFF(day, ReportDate, h_MATURITY) / 30.4167 <= '60') 
END
ELSE
	UPDATE    @t
	SET              h_band = '4,3 to 5,7 years'
	WHERE     (DATEDIFF(day, ReportDate, h_MATURITY) / 30.4167 > '51.599') AND  (DATEDIFF(day, ReportDate, h_MATURITY) / 30.4167 <= '68.399')

select * from @t