Calculate shipping amount - 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 2008 Forums

Transact-SQL (2008)

Calculate shipping amount

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-01-25 : 02:03:01

Dear All,

I have table


CREATE TABLE [dbo].[tbshipping](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[fromcountryid] [bigint] NULL,
	[tocountryid] [bigint] NULL,
	[fromStateID] [bigint] NULL,
	[toStateID] [bigint] NULL,
	[fromkg] [float] NULL,
	[tokg] [float] NULL,
	[additional] [float] NULL,
	[addWeight] [float] NULL,
	[addAmount] [money] NULL,
	[Amount] [money] NULL,
	[shortcode] [varchar](50) NULL,
	[type] [varchar](20) NULL,
	[addby] [varchar](50) NULL,
	[adddate] [datetime] NULL,
	[editby] [varchar](50) NULL,
	[editdate] [datetime] NULL,
 CONSTRAINT [PK_tbshipping] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

I want SP to calculate the ahipping amount. Anyone have idea on how to do it?

Create proc spGetShippingCharges(
--spGetShippingCharges 130,130,1397,1400,1500
@fromcountryid int,
@tocountryid int,
@fromstateid int,
@tostateid int,
@totalweight float
) as

set nocount on

begin
SELECT
bal=(tokg-@totalweight)
<< if bal negative mean ShipAmt=abs(balance/[addWeight])*addAMount + @totalweight*amount >>
<< if bal positive mean ShipAmt=@totalweight*amount >>
FROM [dbo].[tbshipping]
where fromcountryid = @fromcountryid and tocountryid=@tocountryid
end

set nocount off

Please advise.

Thank you.

Regards,
Micheale

matty
Posting Yak Master

161 Posts

Posted - 2011-01-25 : 03:59:41

[code]
Create proc spGetShippingCharges(
--spGetShippingCharges 130,130,1397,1400,1500
@fromcountryid int,
@tocountryid int,
@fromstateid int,
@tostateid int,
@totalweight float
) as

set nocount on

begin
SELECT CASE
WHEN (tokg-@totalweight) < 0 THEN abs(balance/[addWeight])*addAMount + @totalweight*amount
ELSE @totalweight*amount
END AS ShipAmt
FROM [dbo].[tbshipping]
WHERE fromcountryid = @fromcountryid and tocountryid=@tocountryid

end

[/code]

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-01-27 : 04:18:11

Thank You Matty.

It's work for me.

Regards,
Micheale

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources