July 1, 2018 at 11:49 pm
I’ve been thinking about this over the weekend. Even if it is an insert I’m going to have to see if there is a record already in the aggregate table with the same customer Id, vehicle Id and service id so that the newly inserted job can be added to the totals of the ones that already exist in the aggregate table.
What’s the best way for me to do that. Do I use “IF EXISTS.......” or is there a better way for me to do it?
Thank you
Paul.
July 2, 2018 at 6:48 am
paul 69259 - Sunday, July 1, 2018 11:49 PMI’ve been thinking about this over the weekend. Even if it is an insert I’m going to have to see if there is a record already in the aggregate table with the same customer Id, vehicle Id and service id so that the newly inserted job can be added to the totals of the ones that already exist in the aggregate table.What’s the best way for me to do that. Do I use “IF EXISTS.......†or is there a better way for me to do it?
Thank you
Paul.
The best thing at this point would be to share details of what you are trying to do. We have been guessing blind on this thread so far because we don't know what your tables look like or what the logic should be. I would say that using IF EXISTS is the wrong way to think about this because that is going back to RBAR (row by agonizing row) thinking. This needs to be done set based. Maybe your insert and update trigger do the same thing? I have seen situations where that makes sense. See the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2018 at 8:06 am
Sean Lange - Monday, July 2, 2018 6:48 AMpaul 69259 - Sunday, July 1, 2018 11:49 PMI’ve been thinking about this over the weekend. Even if it is an insert I’m going to have to see if there is a record already in the aggregate table with the same customer Id, vehicle Id and service id so that the newly inserted job can be added to the totals of the ones that already exist in the aggregate table.What’s the best way for me to do that. Do I use “IF EXISTS.......†or is there a better way for me to do it?
Thank you
Paul.The best thing at this point would be to share details of what you are trying to do. We have been guessing blind on this thread so far because we don't know what your tables look like or what the logic should be. I would say that using IF EXISTS is the wrong way to think about this because that is going back to RBAR (row by agonizing row) thinking. This needs to be done set based. Maybe your insert and update trigger do the same thing? I have seen situations where that makes sense. See the first link in my signature for best practices when posting questions.
Ok, here goes.
Here is some test data .
Create the jobs table..............
USE [Testing]
GO
/****** Object: Table [dbo].[jobs] Script Date: 02/07/2018 14:45:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[jobs](
[jobid] [int] IDENTITY(1,1) NOT NULL,
[jobnumber] [varchar](10) NULL,
[coldate] [datetime] NULL,
[customerid] [int] NULL,
[serviceid] [int] NULL,
[vehicleid] [int] NULL,
[packs] [decimal](18, 0) NULL,
[weight] [decimal](18, 0) NULL,
[charge] [float] NULL,
[cost] [float] NULL
) ON [PRIMARY]
GO
Add some data to it....
insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(1235581,'2018-06-27 13:34:39.840',1,5,8,10,100,15.00,5.00)
insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(8856331,'2018-07-01 13:34:39.840',2,10,9,3,100,15.00,5.00)
insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(8896347,'2018-07-02 13:34:39.840',3,9,7,2,10,25.00,7.00)
insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(77558666,'2018-07-01 13:34:39.840',9,6,4,1,10,5.00,4.00)
Now create the jobs_aggregate table.....
USE [Testing]
GO
/****** Object: Table [dbo].[Jobs_Aggregate] Script Date: 02/07/2018 14:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs_Aggregate](
[JobAggregateID] [int] IDENTITY(1,1) NOT NULL,
[Guid] [char](32) NULL,
[TS] [float] NULL,
[STS] [float] NULL,
[DTS] [float] NULL,
[CustomerID] [int] NULL,
[ServiceID] [int] NULL,
[VehicleID] [int] NULL,
[Date] [datetime] NULL,
[Jobs] [int] NULL,
[Packs] [decimal](18, 0) NULL,
[Weight] [decimal](18, 0) NULL,
[Charge] [float] NULL,
[Cost] [float] NULL,
CONSTRAINT [PK__Jobs_Agg__C2E6369DDAB67D38] PRIMARY KEY CLUSTERED
(
[JobAggregateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
What I am trying to do is aggregate the total packs, weight cost and charge for the jobs in the jobs table for each customer,service and vehicle by date. When something is updated in the jobs table I need to find it in the aggregate table and update it by either adding changes to the job to the table or taking them away from it.
EG. someone updates a job to have a weight of 25 kilos instead of 10 kilos I need to add the 15 kilos to the relevant job in the aggregate table but if they change it from 10 kilos to 5 kilos I need to deduct the 5 kilos.
When a job is inserted to the jobs table I need the trigger to look in the aggregates table to see if there is an existing record which has a matching collection date, customerid, serviceid and vehicleid and then update the figures accordingly, if there is not, I need to add a new record to the aggregates table for the newly inserted job.
I'm not too worried about the guid field being populated in the job_aggregates table at the moment. I can use the jobaggregateid but I want the trigger to generate a random 32 character guid when inserting new records into the aggregate table eventually.
Does this make any sense?
Thank you
Paul.
July 2, 2018 at 8:15 am
So your jobs table has no primary key? What column(s) are used to indicate that an aggregate row belongs to a job row? No offense mate but your table design is really brittle here. If we can fix the architecture this will be a LOT simpler for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2018 at 8:21 am
paul 69259 - Monday, July 2, 2018 8:06 AMSean Lange - Monday, July 2, 2018 6:48 AMpaul 69259 - Sunday, July 1, 2018 11:49 PMI’ve been thinking about this over the weekend. Even if it is an insert I’m going to have to see if there is a record already in the aggregate table with the same customer Id, vehicle Id and service id so that the newly inserted job can be added to the totals of the ones that already exist in the aggregate table.What’s the best way for me to do that. Do I use “IF EXISTS.......†or is there a better way for me to do it?
Thank you
Paul.The best thing at this point would be to share details of what you are trying to do. We have been guessing blind on this thread so far because we don't know what your tables look like or what the logic should be. I would say that using IF EXISTS is the wrong way to think about this because that is going back to RBAR (row by agonizing row) thinking. This needs to be done set based. Maybe your insert and update trigger do the same thing? I have seen situations where that makes sense. See the first link in my signature for best practices when posting questions.
Ok, here goes.
Here is some test data .
Create the jobs table..............
USE [Testing]
GO/****** Object: Table [dbo].[jobs] Script Date: 02/07/2018 14:45:06 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[jobs](
[jobid] [int] IDENTITY(1,1) NOT NULL,
[jobnumber] [varchar](10) NULL,
[coldate] [datetime] NULL,
[customerid] [int] NULL,
[serviceid] [int] NULL,
[vehicleid] [int] NULL,
[packs] [decimal](18, 0) NULL,
[weight] [decimal](18, 0) NULL,
[charge] [float] NULL,
[cost] [float] NULL
) ON [PRIMARY]
GOAdd some data to it....
insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(1235581,'2018-06-27 13:34:39.840',1,5,8,10,100,15.00,5.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(8856331,'2018-07-01 13:34:39.840',2,10,9,3,100,15.00,5.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(8896347,'2018-07-02 13:34:39.840',3,9,7,2,10,25.00,7.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(77558666,'2018-07-01 13:34:39.840',9,6,4,1,10,5.00,4.00)Now create the jobs_aggregate table.....
USE [Testing]
GO/****** Object: Table [dbo].[Jobs_Aggregate] Script Date: 02/07/2018 14:45:24 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Jobs_Aggregate](
[JobAggregateID] [int] IDENTITY(1,1) NOT NULL,
[Guid] [char](32) NULL,
[TS] [float] NULL,
[STS] [float] NULL,
[DTS] [float] NULL,
[CustomerID] [int] NULL,
[ServiceID] [int] NULL,
[VehicleID] [int] NULL,
[Date] [datetime] NULL,
[Jobs] [int] NULL,
[Packs] [decimal](18, 0) NULL,
[Weight] [decimal](18, 0) NULL,
[Charge] [float] NULL,
[Cost] [float] NULL,
CONSTRAINT [PK__Jobs_Agg__C2E6369DDAB67D38] PRIMARY KEY CLUSTERED
(
[JobAggregateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
What I am trying to do is aggregate the total packs, weight cost and charge for the jobs in the jobs table for each customer,service and vehicle by date. When something is updated in the jobs table I need to find it in the aggregate table and update it by either adding changes to the job to the table or taking them away from it.EG. someone updates a job to have a weight of 25 kilos instead of 10 kilos I need to add the 15 kilos to the relevant job in the aggregate table but if they change it from 10 kilos to 5 kilos I need to deduct the 5 kilos.
When a job is inserted to the jobs table I need the trigger to look in the aggregates table to see if there is an existing record which has a matching collection date, customerid, serviceid and vehicleid and then update the figures accordingly, if there is not, I need to add a new record to the aggregates table for the newly inserted job.
I'm not too worried about the guid field being populated in the job_aggregates table at the moment. I can use the jobaggregateid but I want the trigger to generate a random 32 character guid when inserting new records into the aggregate table eventually.
Does this make any sense?
Thank you
Paul.
Why can't you just run this as a report off the jobs table? Trying to build a trigger to manage those kind of updates as pre calculated aggregates is going to be a hassle to maintain, for example what happens if a record is added, then deleted, then readded to the jobs table?
July 2, 2018 at 8:23 am
Sean Lange - Monday, July 2, 2018 8:15 AMSo your jobs table has no primary key? What column(s) are used to indicate that an aggregate row belongs to a job row? No offense mate but your table design is really brittle here. If we can fix the architecture this will be a LOT simpler for you.
Sorry, this isn't the actual table it's a smaller version for testing.
The actual jobs table contains nearly 200 columns,most of which are irrelevant to the job_aggregate.
I have created this table as it's got just the releveant columns. I just forgot to mark the PK
The jobid is the primary key of the jobs table.
July 2, 2018 at 8:33 am
ZZartin - Monday, July 2, 2018 8:21 AMpaul 69259 - Monday, July 2, 2018 8:06 AMSean Lange - Monday, July 2, 2018 6:48 AMpaul 69259 - Sunday, July 1, 2018 11:49 PMI’ve been thinking about this over the weekend. Even if it is an insert I’m going to have to see if there is a record already in the aggregate table with the same customer Id, vehicle Id and service id so that the newly inserted job can be added to the totals of the ones that already exist in the aggregate table.What’s the best way for me to do that. Do I use “IF EXISTS.......†or is there a better way for me to do it?
Thank you
Paul.The best thing at this point would be to share details of what you are trying to do. We have been guessing blind on this thread so far because we don't know what your tables look like or what the logic should be. I would say that using IF EXISTS is the wrong way to think about this because that is going back to RBAR (row by agonizing row) thinking. This needs to be done set based. Maybe your insert and update trigger do the same thing? I have seen situations where that makes sense. See the first link in my signature for best practices when posting questions.
Ok, here goes.
Here is some test data .
Create the jobs table..............
USE [Testing]
GO/****** Object: Table [dbo].[jobs] Script Date: 02/07/2018 14:45:06 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[jobs](
[jobid] [int] IDENTITY(1,1) NOT NULL,
[jobnumber] [varchar](10) NULL,
[coldate] [datetime] NULL,
[customerid] [int] NULL,
[serviceid] [int] NULL,
[vehicleid] [int] NULL,
[packs] [decimal](18, 0) NULL,
[weight] [decimal](18, 0) NULL,
[charge] [float] NULL,
[cost] [float] NULL
) ON [PRIMARY]
GOAdd some data to it....
insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(1235581,'2018-06-27 13:34:39.840',1,5,8,10,100,15.00,5.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(8856331,'2018-07-01 13:34:39.840',2,10,9,3,100,15.00,5.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(8896347,'2018-07-02 13:34:39.840',3,9,7,2,10,25.00,7.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(77558666,'2018-07-01 13:34:39.840',9,6,4,1,10,5.00,4.00)Now create the jobs_aggregate table.....
USE [Testing]
GO/****** Object: Table [dbo].[Jobs_Aggregate] Script Date: 02/07/2018 14:45:24 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Jobs_Aggregate](
[JobAggregateID] [int] IDENTITY(1,1) NOT NULL,
[Guid] [char](32) NULL,
[TS] [float] NULL,
[STS] [float] NULL,
[DTS] [float] NULL,
[CustomerID] [int] NULL,
[ServiceID] [int] NULL,
[VehicleID] [int] NULL,
[Date] [datetime] NULL,
[Jobs] [int] NULL,
[Packs] [decimal](18, 0) NULL,
[Weight] [decimal](18, 0) NULL,
[Charge] [float] NULL,
[Cost] [float] NULL,
CONSTRAINT [PK__Jobs_Agg__C2E6369DDAB67D38] PRIMARY KEY CLUSTERED
(
[JobAggregateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
What I am trying to do is aggregate the total packs, weight cost and charge for the jobs in the jobs table for each customer,service and vehicle by date. When something is updated in the jobs table I need to find it in the aggregate table and update it by either adding changes to the job to the table or taking them away from it.EG. someone updates a job to have a weight of 25 kilos instead of 10 kilos I need to add the 15 kilos to the relevant job in the aggregate table but if they change it from 10 kilos to 5 kilos I need to deduct the 5 kilos.
When a job is inserted to the jobs table I need the trigger to look in the aggregates table to see if there is an existing record which has a matching collection date, customerid, serviceid and vehicleid and then update the figures accordingly, if there is not, I need to add a new record to the aggregates table for the newly inserted job.
I'm not too worried about the guid field being populated in the job_aggregates table at the moment. I can use the jobaggregateid but I want the trigger to generate a random 32 character guid when inserting new records into the aggregate table eventually.
Does this make any sense?
Thank you
Paul.Why can't you just run this as a report off the jobs table? Trying to build a trigger to manage those kind of updates as pre calculated aggregates is going to be a hassle to maintain, for example what happens if a record is added, then deleted, then readded to the jobs table?
Eventually it will be a report. What we are trying to accomplish is to create a local aggregate table on all our different customers databases with the details of the jobs, services etc... and then sync these to our server on the cloud. With all this information on our cloud server we will have an app that our customers can download and see at a glance how many jobs, what service and vehicle they are, how much it's cost them and how much profit they have made between a set of dates.
July 2, 2018 at 8:36 am
paul 69259 - Monday, July 2, 2018 8:23 AMSean Lange - Monday, July 2, 2018 8:15 AMSo your jobs table has no primary key? What column(s) are used to indicate that an aggregate row belongs to a job row? No offense mate but your table design is really brittle here. If we can fix the architecture this will be a LOT simpler for you.Sorry, this isn't the actual table it's a smaller version for testing.
The actual jobs table contains nearly 200 columns,most of which are irrelevant to the job_aggregate.
I have created this table as it's got just the releveant columns. I just forgot to mark the PK
The jobid is the primary key of the jobs table.
OK great. What column(s) are used to indicate that an aggregate row belongs to a job row? And why are you not using the primary key as this?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2018 at 8:41 am
Sean Lange - Monday, July 2, 2018 8:35 AMpaul 69259 - Monday, July 2, 2018 8:23 AMSean Lange - Monday, July 2, 2018 8:15 AMSo your jobs table has no primary key? What column(s) are used to indicate that an aggregate row belongs to a job row? No offense mate but your table design is really brittle here. If we can fix the architecture this will be a LOT simpler for you.Sorry, this isn't the actual table it's a smaller version for testing.
The actual jobs table contains nearly 200 columns,most of which are irrelevant to the job_aggregate.
I have created this table as it's got just the releveant columns. I just forgot to mark the PK
The jobid is the primary key of the jobs table.
OK great. What column(s) are used to indicate that an aggregate row belongs to a job row? And why are you not using the primary key as this?
The only columns that will be used to relate the aggregate table and the job table will be Date AND customerid AND vehicleID and ServiceID. When all those match each other in both tables will be how they are linked.
July 2, 2018 at 8:51 am
paul 69259 - Monday, July 2, 2018 8:33 AMZZartin - Monday, July 2, 2018 8:21 AMpaul 69259 - Monday, July 2, 2018 8:06 AMSean Lange - Monday, July 2, 2018 6:48 AMpaul 69259 - Sunday, July 1, 2018 11:49 PMI’ve been thinking about this over the weekend. Even if it is an insert I’m going to have to see if there is a record already in the aggregate table with the same customer Id, vehicle Id and service id so that the newly inserted job can be added to the totals of the ones that already exist in the aggregate table.What’s the best way for me to do that. Do I use “IF EXISTS.......†or is there a better way for me to do it?
Thank you
Paul.The best thing at this point would be to share details of what you are trying to do. We have been guessing blind on this thread so far because we don't know what your tables look like or what the logic should be. I would say that using IF EXISTS is the wrong way to think about this because that is going back to RBAR (row by agonizing row) thinking. This needs to be done set based. Maybe your insert and update trigger do the same thing? I have seen situations where that makes sense. See the first link in my signature for best practices when posting questions.
Ok, here goes.
Here is some test data .
Create the jobs table..............
USE [Testing]
GO/****** Object: Table [dbo].[jobs] Script Date: 02/07/2018 14:45:06 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[jobs](
[jobid] [int] IDENTITY(1,1) NOT NULL,
[jobnumber] [varchar](10) NULL,
[coldate] [datetime] NULL,
[customerid] [int] NULL,
[serviceid] [int] NULL,
[vehicleid] [int] NULL,
[packs] [decimal](18, 0) NULL,
[weight] [decimal](18, 0) NULL,
[charge] [float] NULL,
[cost] [float] NULL
) ON [PRIMARY]
GOAdd some data to it....
insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(1235581,'2018-06-27 13:34:39.840',1,5,8,10,100,15.00,5.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(8856331,'2018-07-01 13:34:39.840',2,10,9,3,100,15.00,5.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(8896347,'2018-07-02 13:34:39.840',3,9,7,2,10,25.00,7.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(77558666,'2018-07-01 13:34:39.840',9,6,4,1,10,5.00,4.00)Now create the jobs_aggregate table.....
USE [Testing]
GO/****** Object: Table [dbo].[Jobs_Aggregate] Script Date: 02/07/2018 14:45:24 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Jobs_Aggregate](
[JobAggregateID] [int] IDENTITY(1,1) NOT NULL,
[Guid] [char](32) NULL,
[TS] [float] NULL,
[STS] [float] NULL,
[DTS] [float] NULL,
[CustomerID] [int] NULL,
[ServiceID] [int] NULL,
[VehicleID] [int] NULL,
[Date] [datetime] NULL,
[Jobs] [int] NULL,
[Packs] [decimal](18, 0) NULL,
[Weight] [decimal](18, 0) NULL,
[Charge] [float] NULL,
[Cost] [float] NULL,
CONSTRAINT [PK__Jobs_Agg__C2E6369DDAB67D38] PRIMARY KEY CLUSTERED
(
[JobAggregateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
What I am trying to do is aggregate the total packs, weight cost and charge for the jobs in the jobs table for each customer,service and vehicle by date. When something is updated in the jobs table I need to find it in the aggregate table and update it by either adding changes to the job to the table or taking them away from it.EG. someone updates a job to have a weight of 25 kilos instead of 10 kilos I need to add the 15 kilos to the relevant job in the aggregate table but if they change it from 10 kilos to 5 kilos I need to deduct the 5 kilos.
When a job is inserted to the jobs table I need the trigger to look in the aggregates table to see if there is an existing record which has a matching collection date, customerid, serviceid and vehicleid and then update the figures accordingly, if there is not, I need to add a new record to the aggregates table for the newly inserted job.
I'm not too worried about the guid field being populated in the job_aggregates table at the moment. I can use the jobaggregateid but I want the trigger to generate a random 32 character guid when inserting new records into the aggregate table eventually.
Does this make any sense?
Thank you
Paul.Why can't you just run this as a report off the jobs table? Trying to build a trigger to manage those kind of updates as pre calculated aggregates is going to be a hassle to maintain, for example what happens if a record is added, then deleted, then readded to the jobs table?
Eventually it will be a report. What we are trying to accomplish is to create a local aggregate table on all our different customers databases with the details of the jobs, services etc... and then sync these to our server on the cloud. With all this information on our cloud server we will have an app that our customers can download and see at a glance how many jobs, what service and vehicle they are, how much it's cost them and how much profit they have made between a set of dates.
Trying to store all this like you are attempting seems like not a great approach. I would suggest creating a view that can aggregate all the data you need at the time you need it. Storing aggregate data from multiple external locations is very likely to return incorrect data. I think you need a different approach entirely, not help with a trigger.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2018 at 8:58 am
paul 69259 - Monday, July 2, 2018 8:33 AMZZartin - Monday, July 2, 2018 8:21 AMpaul 69259 - Monday, July 2, 2018 8:06 AMSean Lange - Monday, July 2, 2018 6:48 AMpaul 69259 - Sunday, July 1, 2018 11:49 PMI’ve been thinking about this over the weekend. Even if it is an insert I’m going to have to see if there is a record already in the aggregate table with the same customer Id, vehicle Id and service id so that the newly inserted job can be added to the totals of the ones that already exist in the aggregate table.What’s the best way for me to do that. Do I use “IF EXISTS.......†or is there a better way for me to do it?
Thank you
Paul.The best thing at this point would be to share details of what you are trying to do. We have been guessing blind on this thread so far because we don't know what your tables look like or what the logic should be. I would say that using IF EXISTS is the wrong way to think about this because that is going back to RBAR (row by agonizing row) thinking. This needs to be done set based. Maybe your insert and update trigger do the same thing? I have seen situations where that makes sense. See the first link in my signature for best practices when posting questions.
Ok, here goes.
Here is some test data .
Create the jobs table..............
USE [Testing]
GO/****** Object: Table [dbo].[jobs] Script Date: 02/07/2018 14:45:06 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[jobs](
[jobid] [int] IDENTITY(1,1) NOT NULL,
[jobnumber] [varchar](10) NULL,
[coldate] [datetime] NULL,
[customerid] [int] NULL,
[serviceid] [int] NULL,
[vehicleid] [int] NULL,
[packs] [decimal](18, 0) NULL,
[weight] [decimal](18, 0) NULL,
[charge] [float] NULL,
[cost] [float] NULL
) ON [PRIMARY]
GOAdd some data to it....
insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(1235581,'2018-06-27 13:34:39.840',1,5,8,10,100,15.00,5.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(8856331,'2018-07-01 13:34:39.840',2,10,9,3,100,15.00,5.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(8896347,'2018-07-02 13:34:39.840',3,9,7,2,10,25.00,7.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(77558666,'2018-07-01 13:34:39.840',9,6,4,1,10,5.00,4.00)Now create the jobs_aggregate table.....
USE [Testing]
GO/****** Object: Table [dbo].[Jobs_Aggregate] Script Date: 02/07/2018 14:45:24 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Jobs_Aggregate](
[JobAggregateID] [int] IDENTITY(1,1) NOT NULL,
[Guid] [char](32) NULL,
[TS] [float] NULL,
[STS] [float] NULL,
[DTS] [float] NULL,
[CustomerID] [int] NULL,
[ServiceID] [int] NULL,
[VehicleID] [int] NULL,
[Date] [datetime] NULL,
[Jobs] [int] NULL,
[Packs] [decimal](18, 0) NULL,
[Weight] [decimal](18, 0) NULL,
[Charge] [float] NULL,
[Cost] [float] NULL,
CONSTRAINT [PK__Jobs_Agg__C2E6369DDAB67D38] PRIMARY KEY CLUSTERED
(
[JobAggregateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
What I am trying to do is aggregate the total packs, weight cost and charge for the jobs in the jobs table for each customer,service and vehicle by date. When something is updated in the jobs table I need to find it in the aggregate table and update it by either adding changes to the job to the table or taking them away from it.EG. someone updates a job to have a weight of 25 kilos instead of 10 kilos I need to add the 15 kilos to the relevant job in the aggregate table but if they change it from 10 kilos to 5 kilos I need to deduct the 5 kilos.
When a job is inserted to the jobs table I need the trigger to look in the aggregates table to see if there is an existing record which has a matching collection date, customerid, serviceid and vehicleid and then update the figures accordingly, if there is not, I need to add a new record to the aggregates table for the newly inserted job.
I'm not too worried about the guid field being populated in the job_aggregates table at the moment. I can use the jobaggregateid but I want the trigger to generate a random 32 character guid when inserting new records into the aggregate table eventually.
Does this make any sense?
Thank you
Paul.Why can't you just run this as a report off the jobs table? Trying to build a trigger to manage those kind of updates as pre calculated aggregates is going to be a hassle to maintain, for example what happens if a record is added, then deleted, then readded to the jobs table?
Eventually it will be a report. What we are trying to accomplish is to create a local aggregate table on all our different customers databases with the details of the jobs, services etc... and then sync these to our server on the cloud. With all this information on our cloud server we will have an app that our customers can download and see at a glance how many jobs, what service and vehicle they are, how much it's cost them and how much profit they have made between a set of dates.
Right but why do you want to do that as a trigger? Can't you have whatever the sync job is pull directly from the jobs table, or just create a view for the jobs aggregate? If i'm understanding this right what you're trying to do is sync up the jobs aggregate table with the jobs table but only for a few columns. As I said that's going to be a hassle trying to do in triggers depending on what kind of activity you have on the jobs table.
July 2, 2018 at 8:59 am
Sean Lange - Monday, July 2, 2018 8:51 AMpaul 69259 - Monday, July 2, 2018 8:33 AMZZartin - Monday, July 2, 2018 8:21 AMpaul 69259 - Monday, July 2, 2018 8:06 AMSean Lange - Monday, July 2, 2018 6:48 AMpaul 69259 - Sunday, July 1, 2018 11:49 PMI’ve been thinking about this over the weekend. Even if it is an insert I’m going to have to see if there is a record already in the aggregate table with the same customer Id, vehicle Id and service id so that the newly inserted job can be added to the totals of the ones that already exist in the aggregate table.What’s the best way for me to do that. Do I use “IF EXISTS.......†or is there a better way for me to do it?
Thank you
Paul.The best thing at this point would be to share details of what you are trying to do. We have been guessing blind on this thread so far because we don't know what your tables look like or what the logic should be. I would say that using IF EXISTS is the wrong way to think about this because that is going back to RBAR (row by agonizing row) thinking. This needs to be done set based. Maybe your insert and update trigger do the same thing? I have seen situations where that makes sense. See the first link in my signature for best practices when posting questions.
Ok, here goes.
Here is some test data .
Create the jobs table..............
USE [Testing]
GO/****** Object: Table [dbo].[jobs] Script Date: 02/07/2018 14:45:06 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[jobs](
[jobid] [int] IDENTITY(1,1) NOT NULL,
[jobnumber] [varchar](10) NULL,
[coldate] [datetime] NULL,
[customerid] [int] NULL,
[serviceid] [int] NULL,
[vehicleid] [int] NULL,
[packs] [decimal](18, 0) NULL,
[weight] [decimal](18, 0) NULL,
[charge] [float] NULL,
[cost] [float] NULL
) ON [PRIMARY]
GOAdd some data to it....
insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(1235581,'2018-06-27 13:34:39.840',1,5,8,10,100,15.00,5.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(8856331,'2018-07-01 13:34:39.840',2,10,9,3,100,15.00,5.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(8896347,'2018-07-02 13:34:39.840',3,9,7,2,10,25.00,7.00)insert into jobs
(jobnumber,coldate,customerid,serviceid,vehicleid,packs,weight,charge,cost)
values
(77558666,'2018-07-01 13:34:39.840',9,6,4,1,10,5.00,4.00)Now create the jobs_aggregate table.....
USE [Testing]
GO/****** Object: Table [dbo].[Jobs_Aggregate] Script Date: 02/07/2018 14:45:24 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Jobs_Aggregate](
[JobAggregateID] [int] IDENTITY(1,1) NOT NULL,
[Guid] [char](32) NULL,
[TS] [float] NULL,
[STS] [float] NULL,
[DTS] [float] NULL,
[CustomerID] [int] NULL,
[ServiceID] [int] NULL,
[VehicleID] [int] NULL,
[Date] [datetime] NULL,
[Jobs] [int] NULL,
[Packs] [decimal](18, 0) NULL,
[Weight] [decimal](18, 0) NULL,
[Charge] [float] NULL,
[Cost] [float] NULL,
CONSTRAINT [PK__Jobs_Agg__C2E6369DDAB67D38] PRIMARY KEY CLUSTERED
(
[JobAggregateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
What I am trying to do is aggregate the total packs, weight cost and charge for the jobs in the jobs table for each customer,service and vehicle by date. When something is updated in the jobs table I need to find it in the aggregate table and update it by either adding changes to the job to the table or taking them away from it.EG. someone updates a job to have a weight of 25 kilos instead of 10 kilos I need to add the 15 kilos to the relevant job in the aggregate table but if they change it from 10 kilos to 5 kilos I need to deduct the 5 kilos.
When a job is inserted to the jobs table I need the trigger to look in the aggregates table to see if there is an existing record which has a matching collection date, customerid, serviceid and vehicleid and then update the figures accordingly, if there is not, I need to add a new record to the aggregates table for the newly inserted job.
I'm not too worried about the guid field being populated in the job_aggregates table at the moment. I can use the jobaggregateid but I want the trigger to generate a random 32 character guid when inserting new records into the aggregate table eventually.
Does this make any sense?
Thank you
Paul.Why can't you just run this as a report off the jobs table? Trying to build a trigger to manage those kind of updates as pre calculated aggregates is going to be a hassle to maintain, for example what happens if a record is added, then deleted, then readded to the jobs table?
Eventually it will be a report. What we are trying to accomplish is to create a local aggregate table on all our different customers databases with the details of the jobs, services etc... and then sync these to our server on the cloud. With all this information on our cloud server we will have an app that our customers can download and see at a glance how many jobs, what service and vehicle they are, how much it's cost them and how much profit they have made between a set of dates.
Trying to store all this like you are attempting seems like not a great approach. I would suggest creating a view that can aggregate all the data you need at the time you need it. Storing aggregate data from multiple external locations is very likely to return incorrect data. I think you need a different approach entirely, not help with a trigger.
My boss doesn't think he will be able to sync a view, thats why he wants me to go down this road. He wants live data, synced throughout the day to the cloud server.
July 2, 2018 at 9:01 am
paul 69259 - Monday, July 2, 2018 8:59 AMMy boss doesn't think he will be able to sync a view, thats why he wants me to go down this road. He wants live data, synced throughout the day to the cloud server.
A view is live data and it's likely going to be more accurate than trying to maintain a synced table through triggers....
July 2, 2018 at 9:03 am
ZZartin - Monday, July 2, 2018 9:01 AMpaul 69259 - Monday, July 2, 2018 8:59 AMMy boss doesn't think he will be able to sync a view, thats why he wants me to go down this road. He wants live data, synced throughout the day to the cloud server.A view is live data and it's likely going to be more accurate than trying to maintain a synced table through triggers....
Could a view on our cloud server be synced from all our different customer servers?
July 2, 2018 at 10:56 am
paul 69259 - Monday, July 2, 2018 9:03 AMZZartin - Monday, July 2, 2018 9:01 AMpaul 69259 - Monday, July 2, 2018 8:59 AMMy boss doesn't think he will be able to sync a view, thats why he wants me to go down this road. He wants live data, synced throughout the day to the cloud server.A view is live data and it's likely going to be more accurate than trying to maintain a synced table through triggers....
Could a view on our cloud server be synced from all our different customer servers?
Yes. There is no synch required. It would pull live data from all the different servers when it runs. That would not only be a much simpler solution to code but also more consistent.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply