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
 General SQL Server Forums
 New to SQL Server Programming
 Set Item price to end with .05 or .09

Author  Topic 

aharvestofhealth
Yak Posting Veteran

52 Posts
Posted - 2010-01-28 : 09:48:37
I'm trying to create a script to set all of the items in my IM_PRC table to round to the nearest .05 or .09. I don't want it to round just up, but to the nearest.

Primary key is IM_ITEM
Price field is IM_PRC

At this point, I don't want to create a trigger, just a script to run.

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-01-28 : 10:03:01
Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

aharvestofhealth
Yak Posting Veteran

52 Posts
Posted - 2010-01-28 : 10:34:40
[code]
ITEM_NO PRC_1 DESIRED RESULTS
===============================================
5098545 10.42 10.45
42000013 3.16 3.15
56017007 1.24 1.25
56453805 24.95 24.95
56453836 21.96 21.95
56453867 16.91 16.89
56453881 11.91 11.89
56453898 12.96 12.95
56454000 10.98 10.99
56454024 10.97 10.99
56454048 10.98 10.99
56454109 9.98 9.99
56454154 12.97 12.99
56454178 16.94 16.95
56454192 16.83 16.85
[/code]
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2010-01-28 : 10:46:39
Why not just do a text replace on the output?

replace the last "3,4,6" with a 5
replace the last "7,8,0,1,2" with a 9
Go to Top of Page

aharvestofhealth
Yak Posting Veteran

52 Posts
Posted - 2010-01-28 : 10:53:34
This is not a select statement I wish to run. It is an update statement. I want the results to be permanent in my database.

The following formula rounds to the nearest .05, but I want it to also round to the nearest .09 as well:

update IM_PRC set PRC_1 = Round(2.*PRC_1 ,1)/2.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2010-01-28 : 12:23:30
[code]
select
X,
Rounded_X =
round(x,1,2)+
case
when x-round(x,1,2) between .03 and .06 then .05
when x-round(x,1,2) between .07 and .09 then .09
else -0.01
end
from
( -- Test Data
select x = 21.90 union all
select x = 21.91 union all
select x = 21.92 union all
select x = 21.93 union all
select x = 21.94 union all
select x = 21.95 union all
select x = 21.96 union all
select x = 21.97 union all
select x = 21.98 union all
select x = 21.99
) a
order by
x
[/code]
Results:
[code]
X Rounded_X
------ ---------
21.90 21.89
21.91 21.89
21.92 21.89
21.93 21.95
21.94 21.95
21.95 21.95
21.96 21.95
21.97 21.99
21.98 21.99
21.99 21.99

(10 row(s) affected)
[/code]

CODO ERGO SUM
Go to Top of Page

aharvestofhealth
Yak Posting Veteran

52 Posts
Posted - 2010-01-28 : 12:37:04
Thanks Michael, but again, I don't need to grab results. I want permanent changes to my IM_PRC table using the update statement.
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2010-01-28 : 12:40:33
Obviously, you just have to convert this to an UPDATE statement...
update <urtable> 
set x = round(x,1,2)+
	case
	when x-round(x,1,2) between .03 and .06 then .05
	when x-round(x,1,2) between .07 and .09 then .09
	else -0.01
	end

Replace 'x' with your actual field name.
Go to Top of Page

aharvestofhealth
Yak Posting Veteran

52 Posts
Posted - 2010-01-28 : 13:11:04
Thanks!

I used the following statement:

update IM_PRC 
set PRC_1 = round(PRC_1,1,2)+
	case
	when PRC_1-round(PRC_1,1,2) between .03 and .06 then .05
	when PRC_1-round(PRC_1,1,2) between .07 and .09 then .09
	else -0.01
	end

However, I get this error when I execute if from SQL Server Management Express Studio:

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_IM_PRC_PRC_1". The conflict occurred in database "TESTAHOH", table "dbo.IM_PRC", column 'PRC_1'

What am I doing wrong?
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2010-01-28 : 14:47:38
It means there is a CHECK constraint for the field PRC_1 that you are trying to update. You need to show us the code for the CHECK constraint to figure out why its throwing the error.

If you generate the DDL , you shud be able to see it.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2010-01-28 : 15:24:01
I am not surprised if the check constraint

1) Disallows prices end with a 5 or 9
2) Disallows duplicate prices


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

aharvestofhealth
Yak Posting Veteran

52 Posts
Posted - 2010-01-29 : 11:09:20
quote:
Originally posted by vijayisonly

It means there is a CHECK constraint for the field PRC_1 that you are trying to update. You need to show us the code for the CHECK constraint to figure out why its throwing the error.

If you generate the DDL , you shud be able to see it.


How do I generate the DDL?
Go to Top of Page

aharvestofhealth
Yak Posting Veteran

52 Posts
Posted - 2010-01-29 : 11:19:46
Ok, so far the following script is working just fine without any constraint errors:


UPDATE IM_PRC 
SET PRC_1 =
CASE 
  WHEN (PRC_1 * 100) % 10 < 2 --0,1 become 9
  THEN ((FLOOR(PRC_1 * 10) * 10) + 9) / 100.0
  WHEN (PRC_1 * 100) % 10 > 6 --7,8,9 become 9
  THEN ((FLOOR(PRC_1 * 10) * 10) + 9) / 100.0
  ELSE ((FLOOR(PRC_1 * 10) * 10) + 5) / 100.0 --2,3,4,5,6 becomes 5
  END


However it is rounding the numbers that end in 0 or 1 UP to 9. I need it to round it DOWN to 9.
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2010-01-29 : 11:41:39
quote:
Originally posted by aharvestofhealth

quote:
Originally posted by vijayisonly

It means there is a CHECK constraint for the field PRC_1 that you are trying to update. You need to show us the code for the CHECK constraint to figure out why its throwing the error.

If you generate the DDL , you shud be able to see it.


How do I generate the DDL?


Right click on the table name in your Management Studio object explorer and give CREATE TO...that will generate your DLL
Go to Top of Page

aharvestofhealth
Yak Posting Veteran

52 Posts
Posted - 2010-01-29 : 12:12:01
Then here is the DDL:

USE [TESTAHOH]
GO
/****** Object:  Table [dbo].[IM_PRC]    Script Date: 01/29/2010 12:09:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IM_PRC](
	[ITEM_NO] [dbo].[T_ITEM_NO] NOT NULL,
	[LOC_ID] [dbo].[T_LOC_ID] NOT NULL,
	[DIM_1_UPR] [dbo].[T_GRID_DIM_1] NOT NULL,
	[DIM_2_UPR] [dbo].[T_GRID_DIM_2] NOT NULL,
	[DIM_3_UPR] [dbo].[T_GRID_DIM_3] NOT NULL,
	[REG_PRC] [dbo].[T_PRC] NULL,
	[PRC_1] [dbo].[T_PRC] NULL,
	[PRC_2] [dbo].[T_PRC] NULL,
	[PRC_3] [dbo].[T_PRC] NULL,
	[PRC_4] [dbo].[T_PRC] NULL,
	[PRC_5] [dbo].[T_PRC] NULL,
	[PRC_6] [dbo].[T_PRC] NULL,
	[ALT_1_REG_PRC] [dbo].[T_PRC] NULL,
	[ALT_1_PRC_1] [dbo].[T_PRC] NULL,
	[ALT_1_PRC_2] [dbo].[T_PRC] NULL,
	[ALT_1_PRC_3] [dbo].[T_PRC] NULL,
	[ALT_1_PRC_4] [dbo].[T_PRC] NULL,
	[ALT_1_PRC_5] [dbo].[T_PRC] NULL,
	[ALT_1_PRC_6] [dbo].[T_PRC] NULL,
	[ALT_2_REG_PRC] [dbo].[T_PRC] NULL,
	[ALT_2_PRC_1] [dbo].[T_PRC] NULL,
	[ALT_2_PRC_2] [dbo].[T_PRC] NULL,
	[ALT_2_PRC_3] [dbo].[T_PRC] NULL,
	[ALT_2_PRC_4] [dbo].[T_PRC] NULL,
	[ALT_2_PRC_5] [dbo].[T_PRC] NULL,
	[ALT_2_PRC_6] [dbo].[T_PRC] NULL,
	[ALT_3_REG_PRC] [dbo].[T_PRC] NULL,
	[ALT_3_PRC_1] [dbo].[T_PRC] NULL,
	[ALT_3_PRC_2] [dbo].[T_PRC] NULL,
	[ALT_3_PRC_3] [dbo].[T_PRC] NULL,
	[ALT_3_PRC_4] [dbo].[T_PRC] NULL,
	[ALT_3_PRC_5] [dbo].[T_PRC] NULL,
	[ALT_3_PRC_6] [dbo].[T_PRC] NULL,
	[ALT_4_REG_PRC] [dbo].[T_PRC] NULL,
	[ALT_4_PRC_1] [dbo].[T_PRC] NULL,
	[ALT_4_PRC_2] [dbo].[T_PRC] NULL,
	[ALT_4_PRC_3] [dbo].[T_PRC] NULL,
	[ALT_4_PRC_4] [dbo].[T_PRC] NULL,
	[ALT_4_PRC_5] [dbo].[T_PRC] NULL,
	[ALT_4_PRC_6] [dbo].[T_PRC] NULL,
	[ALT_5_REG_PRC] [dbo].[T_PRC] NULL,
	[ALT_5_PRC_1] [dbo].[T_PRC] NULL,
	[ALT_5_PRC_2] [dbo].[T_PRC] NULL,
	[ALT_5_PRC_3] [dbo].[T_PRC] NULL,
	[ALT_5_PRC_4] [dbo].[T_PRC] NULL,
	[ALT_5_PRC_5] [dbo].[T_PRC] NULL,
	[ALT_5_PRC_6] [dbo].[T_PRC] NULL,
	[LST_MAINT_DT] [dbo].[T_DT] NULL,
	[LST_MAINT_USR_ID] [dbo].[T_USR_ID] NULL,
	[LST_LCK_DT] [dbo].[T_DT] NULL,
	[ROW_TS] [timestamp] NULL,
 CONSTRAINT [PK_IM_PRC] PRIMARY KEY CLUSTERED 
(
	[ITEM_NO] ASC,
	[LOC_ID] ASC,
	[DIM_1_UPR] ASC,
	[DIM_2_UPR] ASC,
	[DIM_3_UPR] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [FK_IM_PRC_IM_ITEM] FOREIGN KEY([ITEM_NO])
REFERENCES [dbo].[IM_ITEM] ([ITEM_NO])
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [FK_IM_PRC_IM_ITEM]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_1_PRC_1] CHECK  (([ALT_1_PRC_1] IS NULL OR [ALT_1_PRC_1]>=(0) AND [ALT_1_PRC_1]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_1_PRC_1]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_1_PRC_2] CHECK  (([ALT_1_PRC_2] IS NULL OR [ALT_1_PRC_2]>=(0) AND [ALT_1_PRC_2]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_1_PRC_2]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_1_PRC_3] CHECK  (([ALT_1_PRC_3] IS NULL OR [ALT_1_PRC_3]>=(0) AND [ALT_1_PRC_3]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_1_PRC_3]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_1_PRC_4] CHECK  (([ALT_1_PRC_4] IS NULL OR [ALT_1_PRC_4]>=(0) AND [ALT_1_PRC_4]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_1_PRC_4]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_1_PRC_5] CHECK  (([ALT_1_PRC_5] IS NULL OR [ALT_1_PRC_5]>=(0) AND [ALT_1_PRC_5]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_1_PRC_5]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_1_PRC_6] CHECK  (([ALT_1_PRC_6] IS NULL OR [ALT_1_PRC_6]>=(0) AND [ALT_1_PRC_6]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_1_PRC_6]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_1_REG_PRC] CHECK  (([ALT_1_REG_PRC] IS NULL OR [ALT_1_REG_PRC]>=(0) AND [ALT_1_REG_PRC]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_1_REG_PRC]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_2_PRC_1] CHECK  (([ALT_2_PRC_1] IS NULL OR [ALT_2_PRC_1]>=(0) AND [ALT_2_PRC_1]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_2_PRC_1]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_2_PRC_2] CHECK  (([ALT_2_PRC_2] IS NULL OR [ALT_2_PRC_2]>=(0) AND [ALT_2_PRC_2]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_2_PRC_2]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_2_PRC_3] CHECK  (([ALT_2_PRC_3] IS NULL OR [ALT_2_PRC_3]>=(0) AND [ALT_2_PRC_3]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_2_PRC_3]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_2_PRC_4] CHECK  (([ALT_2_PRC_4] IS NULL OR [ALT_2_PRC_4]>=(0) AND [ALT_2_PRC_4]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_2_PRC_4]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_2_PRC_5] CHECK  (([ALT_2_PRC_5] IS NULL OR [ALT_2_PRC_5]>=(0) AND [ALT_2_PRC_5]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_2_PRC_5]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_2_PRC_6] CHECK  (([ALT_2_PRC_6] IS NULL OR [ALT_2_PRC_6]>=(0) AND [ALT_2_PRC_6]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_2_PRC_6]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_2_REG_PRC] CHECK  (([ALT_2_REG_PRC] IS NULL OR [ALT_2_REG_PRC]>=(0) AND [ALT_2_REG_PRC]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_2_REG_PRC]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_3_PRC_1] CHECK  (([ALT_3_PRC_1] IS NULL OR [ALT_3_PRC_1]>=(0) AND [ALT_3_PRC_1]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_3_PRC_1]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_3_PRC_2] CHECK  (([ALT_3_PRC_2] IS NULL OR [ALT_3_PRC_2]>=(0) AND [ALT_3_PRC_2]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_3_PRC_2]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_3_PRC_3] CHECK  (([ALT_3_PRC_3] IS NULL OR [ALT_3_PRC_3]>=(0) AND [ALT_3_PRC_3]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_3_PRC_3]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_3_PRC_4] CHECK  (([ALT_3_PRC_4] IS NULL OR [ALT_3_PRC_4]>=(0) AND [ALT_3_PRC_4]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_3_PRC_4]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_3_PRC_5] CHECK  (([ALT_3_PRC_5] IS NULL OR [ALT_3_PRC_5]>=(0) AND [ALT_3_PRC_5]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_3_PRC_5]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_3_PRC_6] CHECK  (([ALT_3_PRC_6] IS NULL OR [ALT_3_PRC_6]>=(0) AND [ALT_3_PRC_6]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_3_PRC_6]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_3_REG_PRC] CHECK  (([ALT_3_REG_PRC] IS NULL OR [ALT_3_REG_PRC]>=(0) AND [ALT_3_REG_PRC]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_3_REG_PRC]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_4_PRC_1] CHECK  (([ALT_4_PRC_1] IS NULL OR [ALT_4_PRC_1]>=(0) AND [ALT_4_PRC_1]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_4_PRC_1]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_4_PRC_2] CHECK  (([ALT_4_PRC_2] IS NULL OR [ALT_4_PRC_2]>=(0) AND [ALT_4_PRC_2]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_4_PRC_2]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_4_PRC_3] CHECK  (([ALT_4_PRC_3] IS NULL OR [ALT_4_PRC_3]>=(0) AND [ALT_4_PRC_3]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_4_PRC_3]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_4_PRC_4] CHECK  (([ALT_4_PRC_4] IS NULL OR [ALT_4_PRC_4]>=(0) AND [ALT_4_PRC_4]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_4_PRC_4]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_4_PRC_5] CHECK  (([ALT_4_PRC_5] IS NULL OR [ALT_4_PRC_5]>=(0) AND [ALT_4_PRC_5]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_4_PRC_5]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_4_PRC_6] CHECK  (([ALT_4_PRC_6] IS NULL OR [ALT_4_PRC_6]>=(0) AND [ALT_4_PRC_6]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_4_PRC_6]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_4_REG_PRC] CHECK  (([ALT_4_REG_PRC] IS NULL OR [ALT_4_REG_PRC]>=(0) AND [ALT_4_REG_PRC]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_4_REG_PRC]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_5_PRC_1] CHECK  (([ALT_5_PRC_1] IS NULL OR [ALT_5_PRC_1]>=(0) AND [ALT_5_PRC_1]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_5_PRC_1]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_5_PRC_2] CHECK  (([ALT_5_PRC_2] IS NULL OR [ALT_5_PRC_2]>=(0) AND [ALT_5_PRC_2]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_5_PRC_2]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_5_PRC_3] CHECK  (([ALT_5_PRC_3] IS NULL OR [ALT_5_PRC_3]>=(0) AND [ALT_5_PRC_3]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_5_PRC_3]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_5_PRC_4] CHECK  (([ALT_5_PRC_4] IS NULL OR [ALT_5_PRC_4]>=(0) AND [ALT_5_PRC_4]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_5_PRC_4]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_5_PRC_5] CHECK  (([ALT_5_PRC_5] IS NULL OR [ALT_5_PRC_5]>=(0) AND [ALT_5_PRC_5]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_5_PRC_5]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_5_PRC_6] CHECK  (([ALT_5_PRC_6] IS NULL OR [ALT_5_PRC_6]>=(0) AND [ALT_5_PRC_6]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_5_PRC_6]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_ALT_5_REG_PRC] CHECK  (([ALT_5_REG_PRC] IS NULL OR [ALT_5_REG_PRC]>=(0) AND [ALT_5_REG_PRC]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ALT_5_REG_PRC]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH NOCHECK ADD  CONSTRAINT [CK_IM_PRC_ITEM_PRC_1_REQD] CHECK  ((((not([LOC_ID] = '*' and [DIM_1_UPR] = '*' and [DIM_2_UPR] = '*' and [DIM_3_UPR] = '*' and [PRC_1] is null)))))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_ITEM_PRC_1_REQD]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_PRC_1] CHECK  (([PRC_1] IS NULL OR [PRC_1]>=(0) AND [PRC_1]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_PRC_1]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_PRC_2] CHECK  (([PRC_2] IS NULL OR [PRC_2]>=(0) AND [PRC_2]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_PRC_2]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_PRC_3] CHECK  (([PRC_3] IS NULL OR [PRC_3]>=(0) AND [PRC_3]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_PRC_3]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_PRC_4] CHECK  (([PRC_4] IS NULL OR [PRC_4]>=(0) AND [PRC_4]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_PRC_4]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_PRC_5] CHECK  (([PRC_5] IS NULL OR [PRC_5]>=(0) AND [PRC_5]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_PRC_5]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_PRC_6] CHECK  (([PRC_6] IS NULL OR [PRC_6]>=(0) AND [PRC_6]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_PRC_6]
GO
ALTER TABLE [dbo].[IM_PRC]  WITH CHECK ADD  CONSTRAINT [CK_IM_PRC_REG_PRC] CHECK  (([REG_PRC] IS NULL OR [REG_PRC]>=(0) AND [REG_PRC]<=(99999999)))
GO
ALTER TABLE [dbo].[IM_PRC] CHECK CONSTRAINT [CK_IM_PRC_REG_PRC]
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2010-01-29 : 12:23:55
Ok..It appears you have a value of 0 for the field..
try this update..
update IM_PRC 
set PRC_1 = round(PRC_1,1,2)+
	case
	when PRC_1-round(PRC_1,1,2) between .03 and .06 then .05
	when PRC_1-round(PRC_1,1,2) between .07 and .09 then .09
        when PRC_1-round(PRC_1,1,2) between .01 and .02 then -0.01
	else 0
	end
Go to Top of Page

aharvestofhealth
Yak Posting Veteran

52 Posts
Posted - 2010-01-29 : 12:59:07
quote:
Originally posted by vijayisonly

Ok..It appears you have a value of 0 for the field..
try this update..
update IM_PRC 
set PRC_1 = round(PRC_1,1,2)+
	case
	when PRC_1-round(PRC_1,1,2) between .03 and .06 then .05
	when PRC_1-round(PRC_1,1,2) between .07 and .09 then .09
        when PRC_1-round(PRC_1,1,2) between .01 and .02 then -0.01
	else 0
	end




Ok, but it seems that numbers that end in 0 (10.00 for example) don't change. Can we change it so .00 through .01 rounds down to .09, and .02 through .06 rounds up to .05?
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2010-01-29 : 14:20:36
Man....ok...try this
update IM_PRC 
set PRC_1 = round(PRC_1,1,2)+
	case
	when PRC_1-round(PRC_1,1,2) between .02 and .06 then .05
	when PRC_1-round(PRC_1,1,2) between .07 and .09 then .09
        when PRC_1-round(PRC_1,1,2) = .01 then -0.01
        when PRC_1 > 0 and PRC_1-round(PRC_1,1,2) = 0 then -0.01
	else 0
	end
Go to Top of Page

aharvestofhealth
Yak Posting Veteran

52 Posts
Posted - 2010-01-29 : 15:00:05
quote:
Originally posted by vijayisonly

Man....ok...try this
update IM_PRC 
set PRC_1 = round(PRC_1,1,2)+
	case
	when PRC_1-round(PRC_1,1,2) between .02 and .06 then .05
	when PRC_1-round(PRC_1,1,2) between .07 and .09 then .09
        when PRC_1-round(PRC_1,1,2) = .01 then -0.01
        when PRC_1 > 0 and PRC_1-round(PRC_1,1,2) = 0 then -0.01
	else 0
	end



Thanks! You're the man!!!
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2010-01-29 : 17:28:30
Np. You're welcome. But you really should be thanking MVJ for the solution.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site