Must be simple solution - so I must be STOOOPID!!!

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

Must be simple solution - so I must be STOOOPID!!!

Author

Topic

nick_dkc
Starting Member

25 Posts

Posted - 2009-11-26 : 06:08:21

Hi there,

In part one of my problem :-) ...

I have two tables - one that records animal relocation exports (dbo.exports) and another that records each animal container that is a part of an export (dbo.containers) - one export event can have one or many containers.

Here's the tables:

CREATE TABLE dbo.containers(containerID int, shipmentID int, animalName nvarchar (25), weight decimal (18,2))

INSERT INTO containers values (1 , 1, 'Howl', 20.00)
INSERT INTO containers values (2 , 1, 'Meow', 22.00)
INSERT INTO containers values (3 , 1, 'Woof', 32.00)
INSERT INTO containers values (4 , 2, 'Bark', 40.00)
INSERT INTO containers values (5 , 2, 'Poop', 32.00)
INSERT INTO containers values (6 , 3, 'Ruff', 19.00)

CREATE TABLE dbo.exports(exportID int, destination nvarchar(25), shipmentWeight decimal (18,2))

INSERT INTO dbo.exports values (1, 'London', 0.00)
INSERT INTO dbo.exports values (2, 'Trinidad', 0.00)
INSERT INTO dbo.exports values (3, 'Beijing', 0.00)

I want to calculate the TOTAL shipment weight for AN export, by adding the 'weight' value for each container in a shipment and here I have a simple query that returns the total weight of all containers for one export:

select sum(weight) as totalWeight, exportID from dbo.containers where exportID = exportID GROUP BY exportID

This correctly returns

totalWeight exportID
----------- ---------
74 1
72 2
19 3

In this part of my problem - :-) - I need help to write insert/update triggers, that are attached to dbo.containers, that will calculate and insert the 'totalWeight' value INTO the dbo.exports.shipmentWeight column.

All advice / suggestions / solutions HUGELY appreciated, as always.

WOOF, WOOF !!
Love from Dubai

Nick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-27 : 02:09:49

how do you link containers to exports? you dont have exportid in containers

aeterna
Starting Member

2 Posts

Posted - 2009-11-27 : 04:44:10

Presumably your query should read:

select sum(weight) as totalWeight, shipmentID from dbo.containers where shipmentID = shipmentID GROUP BY shipmentID

Why do you need to store the shipment weight, rather than calculating it when required?

What happens to your invoicing if you add another containerid/shipmentid combination some time after the original set of data was entered?

Do you need to maintain historical information?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-27 : 07:54:24

quote:
Originally posted by visakh16

how do you link containers to exports? you dont have exportid in containers

Accpording to expected output, exportid equals destinationid.

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-27 : 22:19:59

quote:
Originally posted by Peso

quote:
Originally posted by visakh16

how do you link containers to exports? you dont have exportid in containers
Accpording to expected output, exportid equals destinationid.

N 56°04'39.26"
E 12°55'05.63"

sorry where's destinationid?

nick_dkc
Starting Member

25 Posts

Posted - 2009-11-27 : 22:23:08

1. Sorry Visakh16, you're right and my create table query was wrong, the table should have read as follows to include exportID:

CREATE TABLE dbo.containers(containerID int, exportID int, animalName nvarchar (25), weight decimal (18,2))

INSERT INTO containers values (1 , 1, 'Howl', 20.00)
INSERT INTO containers values (2 , 1, 'Meow', 22.00)
INSERT INTO containers values (3 , 1, 'Woof', 32.00)
INSERT INTO containers values (4 , 2, 'Bark', 40.00)
INSERT INTO containers values (5 , 2, 'Poop', 32.00)
INSERT INTO containers values (6 , 3, 'Ruff', 19.00)

2. Peso - I'm not understanding your feedback my friend - in these tables the destination is irrelevant, hence there is no 'destinationID'.

3. aeterna - good questions.

a. See my correction to the dbo.containers CREATE and you'll see that my query to get the weight is correct now.

b. I need to store the total weight for each shipment because (i) the data is used at different times in the process to generate a number of documents (airwaybill, cargo booking, etc.) both offline and online and (ii) I cannot call the weight query from my primary application (iii) I need the historical data.

c. One customer can have multiple shipments, and each shipment can have multiple containers, and each container can contain multiple animals. Customers are invoiced by shipment - way down the line in the business process. I need this to be a trigger on the containers table so that if a container is ADDED or DELETED from an export event, or the container 'weigh't changes, the shipmentWeight in dbo.exports is automatically updated.

d. Yes, I do need to keep historical information about each export.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-28 : 01:35:34

[code]CREATE TRIGGER ExportUpd
ON containers
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON

UPDATE e
SET e.shipmentWeight = e.shipmentWeight + e1.TotalWeight
FROM export e
INNER JOIN (SELECT exportID,SUM(weight) AS TotalWeight
FROM INSERTED
GROUP BY exportID) e1
ON e1.exportID=e.exportID
END
[/code]

nick_dkc
Starting Member

25 Posts

Posted - 2009-11-28 : 03:21:10

Many, many thanks Visakh16! Just one small change

You had written:
SET e.shipmentWeight = e.shipmentWeight + e1.TotalWeight
- this added the totalWeight to the shipment weight

It needed to be
SET e.shipmentWeight = e1.TotalWeight
- this returned the correct calculation - so it looked like this finally:

---------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[ExportsUpd]
ON [dbo].[containers]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON

UPDATE e
SET e.shipmentWeight = e1.TotalWeight
FROM exports e
INNER JOIN (SELECT exportID, SUM(weight) AS TotalWeight
FROM containers
GROUP BY exportID) e1
ON e1.exportID=e.exportID
END
---------------------

Again, many, many , many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-28 : 05:43:10

quote:
Originally posted by nick_dkc

Many, many thanks Visakh16! Just one small change

You had written:
SET e.shipmentWeight = e.shipmentWeight + e1.TotalWeight
- this added the totalWeight to the shipment weight

It needed to be
SET e.shipmentWeight = e1.TotalWeight
- this returned the correct calculation - so it looked like this finally:

---------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[ExportsUpd]
ON [dbo].[containers]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON

UPDATE e
SET e.shipmentWeight = e1.TotalWeight
FROM exports e
INNER JOIN (SELECT exportID, SUM(weight) AS TotalWeight
FROM containers
GROUP BY exportID) e1
ON e1.exportID=e.exportID
END
---------------------

Again, many, many , many thanks

nope, but dont you need the weight to be added up during each modification? thats why i used e.shipmentWeight = e.shipmentWeight + e1.TotalWeight. also if you see i'm using INSERTED instead of containers in the inner query. if you want to consider updates also make it


UPDATE e
SET e.shipmentWeight = e.shipmentWeight + e1.TotalWeight
FROM exports e
INNER JOIN (SELECT exportID, SUM(CASE WHEN Cat='I' THEN weight ELSE -1*weight END) AS TotalWeight
            FROM (SELECT exportID,weight,'I' AS Cat FROM inserted
                   union all
                  SELECT exportID,weight,'D'  FROM deleted)t
            GROUP BY exportID) e1
ON e1.exportID=e.exportID
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources