a better way to write this code

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 2005 Forums

Transact-SQL (2005)

a better way to write this code

Author

Topic

ikekairey
Starting Member

2 Posts

Posted - 2009-11-28 : 23:08:34

Hello all. I Know that there has to be a better way to write the below code. I tried a few different ways but I couldnt get the desired results. So, sadly, I had to resort to using a cursor. Below is the code. Basicaly, it checks which records meets the criteria, calculates the rate using a function, then updates or inserts the value.

declare @bol int, @client varchar(35), @sumCtns int, @sumPallets int, @state varchar(5) , @rst cursor, @rc int, @rate numeric(8,2)

set @rst = cursor for
SELECT [BOL Header].BOL_Number, [BOL Header].client, SUM([BOL line items].CTNS) AS sumCtns, SUM([BOL line items].palletsBL) AS SumPallets,
[BOL Header].state
FROM [BOL Header] INNER JOIN
[BOL line items] ON [BOL Header].BOL_Number = [BOL line items].BOL_Number LEFT OUTER JOIN
tblTrucking ON [BOL Header].BOL_Number = tblTrucking.BolNumber
WHERE ([BOL Header].[BOL Pickedup] = 1) AND (tblTrucking.Rate = 0 OR
tblTrucking.Rate IS NULL) AND ([BOL Header].Carrier = N'ups')
GROUP BY [BOL Header].BOL_Number, [BOL Header].client, [BOL Header].state
HAVING ([BOL Header].client <> N'clientX')

Open @RST

fetch next from @rst into @bol, @client, @sumCtns, @sumPallets, @state
while @@fetch_status = 0
begin
select @rate = jdistdev.dbo.spiritTruckRate(@state,@sumCtns,@SumPallets, @client)
update tblTrucking set rate = @rate
where bolNumber = @bol

set @rc = @@rowcount
if @rc = 0
INSERT INTO tblTrucking
(BolNumber, Rate)
VALUES (@bol,@rate)
fetch next from @rst into @bol, @client, @sumCtns, @sumPallets, @state

end
close @rst
deallocate @rst

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-29 : 12:55:58

you can simply use


INSERT INTO tblTrucking
(BolNumber, Rate)
SELECT BOL_Number,jdistdev.dbo.spiritTruckRate(state,sumCtns,SumPallets,client) 
FROM
(
SELECT [BOL Header].BOL_Number, [BOL Header].client, SUM([BOL line items].CTNS) AS sumCtns, SUM([BOL line items].palletsBL) AS SumPallets, 
[BOL Header].state
FROM [BOL Header] INNER JOIN
[BOL line items] ON [BOL Header].BOL_Number = [BOL line items].BOL_Number LEFT OUTER JOIN
tblTrucking ON [BOL Header].BOL_Number = tblTrucking.BolNumber
WHERE ([BOL Header].[BOL Pickedup] = 1) AND (tblTrucking.Rate = 0 OR
tblTrucking.Rate IS NULL) AND ([BOL Header].Carrier = N'ups')
GROUP BY [BOL Header].BOL_Number, [BOL Header].client, [BOL Header].state
HAVING ([BOL Header].client <> N'clientX')
)t

ikekairey
Starting Member

2 Posts

Posted - 2009-11-29 : 22:17:25

thanks it works nicely. i added a delete from tblTrucking where rate = 0 so i dont have to deal with an update.

thanks again.

quote:

Originally posted by visakh16

you can simply use


INSERT INTO tblTrucking
(BolNumber, Rate)
SELECT BOL_Number,jdistdev.dbo.spiritTruckRate(state,sumCtns,SumPallets,client) 
FROM
(
SELECT [BOL Header].BOL_Number, [BOL Header].client, SUM([BOL line items].CTNS) AS sumCtns, SUM([BOL line items].palletsBL) AS SumPallets, 
[BOL Header].state
FROM [BOL Header] INNER JOIN
[BOL line items] ON [BOL Header].BOL_Number = [BOL line items].BOL_Number LEFT OUTER JOIN
tblTrucking ON [BOL Header].BOL_Number = tblTrucking.BolNumber
WHERE ([BOL Header].[BOL Pickedup] = 1) AND (tblTrucking.Rate = 0 OR
tblTrucking.Rate IS NULL) AND ([BOL Header].Carrier = N'ups')
GROUP BY [BOL Header].BOL_Number, [BOL Header].client, [BOL Header].state
HAVING ([BOL Header].client <> N'clientX')
)t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 10:36:04

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources