Update using value from another table 2

[kylua]

I have inherited a time sheet database which stores the hours that a person has worked against a project with a link to an employee table for his name which is linked to a positions table which gives the rate charged.
And there is a totals value in the database which is supposed to be the hours multiplied by the rate.

At the present the total is calculated by some poor girl sitting there with 900 records in front of her clicking on each one which triggers a script that calculates the two. Then she clicks on a tickbox to show she has processed it.
Access being Access, a calculation can take 30 seconds!!
Then she exports it all into Excel and forgets about it!

I think I can make that a little easier to do!

But I am stuck. My initial thought was just to do a simple update query such as: (After writing all the unprocessed to a csv she can open in excel)
[navy]"Update TimeSheetData set Total = Hours * rate, processed = 1 where processed = 0"[/navy]
But how do I do this when the rate is stored in another table that is referenced thru a third table?
[navy]"update sheets set TimeSheetData.total = TimeSheetData.hours * positions.rate inner join staff ON TimeSheetData.staff = staff.ID inner join positions ON staff.rate = positions.ID where Processed = 0"[/navy]
Didn't Parse.

Anybody know the syntax of the top of their heads? A young lady by the name of Angela will be eternally grateful if she doesn't have to spend hours clicking an Access database for no reason.

 

[RiverGuy]

Your query is missing a FROM clause

update sheets set TimeSheetData.total = TimeSheetData.hours * positions.rate [b]from sheets[/b] inner join staff ON TimeSheetData.staff = staff.ID inner join positions ON staff.rate = positions.ID  where Processed = 0

Although it seems odd that you would be joining the Staff.rate to Positions.ID

 

[kylua]

Taking the lead that an update statement needs a FROM clause, something I would never have guessed, I managed, eventually, to validate the following against the data source:

UPDATE    TimeSheetData
SET              Total = TimeSheetData_1.[Time Sheet Hours] * StaffPositions.Rate
FROM         TimeSheetData AS TimeSheetData_1 INNER JOIN
                      Staff ON TimeSheetData_1.[Staff Ref] = Staff.[Staff Ref] INNER JOIN
                      StaffPositions ON Staff.[Staff Position Ref] = StaffPositions.[Staff Position Ref] CROSS JOIN
                      TimeSheetData
WHERE     (TimeSheetData.[Finance Processed] = 0)

The reason the rate is against the position is becos this is for billing purposes so the rate is defined by the position as opposed to the employee. So all project engineers are charged out at a single rate whether they're contract/employee/newbie or whatever.

 

[RiverGuy]

You don't always need a FROM clause. But if you're using joins, then you need it.

 

[markros]

The above statement is a bit strange and especially CROSS JOIN.

 

[kylua]

It's what SQL Server turned my attempt into. I have no idea what the CROSS JOIN does and, actually, I haven't attempted it yet, just parsed it. Bit dodgy on live data so I'm going to make a mock up and try it first.
And looked up 'cross join'. I'm going to very careful with this.

 

[RiverGuy]

You rarely need CROSS JOIN. It means that if you have 100 rows in one table and 100 rows in another, and you then CROSS JOIN them, you will get every combination, or 10,000 rows.

 

[kylua]

OK, back to work after a long weekend golfing on the wettest weekend of the year!!
And it's not working, I presume due to the cross join.
It runs, but the result alternates between the first result and null for all 100 records I have in my test data.
Tried:
Changing it to inner, outer and just join. Wouldn't parse.
Removed it altogether; The multi-part identifier "TimeSheetData_1.total" could not be bound.
Changed TimeSheetData_1.total to TimeSheetData.total and it parsed but then; Column or expression 'Total' cannot be updated.
I would like to do this 'properly' as I suspect it will be a great help in the future but I am getting tempted to just write some code to loop thru them. Which is a bodge job but nowhere as bad as what is currently in place.

 

[markros]

What was wrong with the original query by RiverGuy and why do you need a join to itself?

Can you post your tables creation scripts, some input and output?

 

[kylua]

The original query was using example info from tables I was testing on.
In changing the query to match the actual tables, SQL Server 2005 altered it to add the TimeSheetData as TimeSheetData_1 and then added the cross join to TimeSheetData.

Using RG's query as a basis I made several changes to the query as detailed above, with the results described.

The structure of the timesheetdata table

USE [CTCdata]
GO
/****** Object:  Table [dbo].[TimeSheetData]    Script Date: 11/03/2009 16:00:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TimeSheetData](
	[Time Sheet ID] [int] IDENTITY(1,1) NOT NULL,
	[Staff Ref] [nvarchar](8) NULL,
	[Time Sheet Code] [nvarchar](7) NULL,
	[Time Sheet Description] [nvarchar](50) NULL,
	[Time Sheet Date] [datetime] NULL,
	[Time Sheet Hours] [float] NULL,
	[Finance Processed] [bit] NULL CONSTRAINT [DF_TimeSheetData_Finance Processed]  DEFAULT (0),
	[Rate] [money] NULL CONSTRAINT [DF_TimeSheetData_Rate]  DEFAULT (0),
	[Total] [money] NULL CONSTRAINT [DF_TimeSheetData_Total]  DEFAULT (0),
	[Completed] [bit] NULL CONSTRAINT [DF_TimeSheetData_Completed]  DEFAULT (0),
	[systs] [timestamp] NULL,
 CONSTRAINT [PK_TimeSheetData] PRIMARY KEY CLUSTERED 
(
	[Time Sheet 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]

The Staffpositions:

USE [CTCdata]
GO
/****** Object:  Table [dbo].[StaffPositions]    Script Date: 11/03/2009 16:05:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StaffPositions](
	[Staff Position Ref] [int] IDENTITY(1,1) NOT NULL,
	[Staff Position] [nvarchar](50) NULL,
	[Rate] [money] NULL,
 CONSTRAINT [PK_StaffPositions] PRIMARY KEY CLUSTERED 
(
	[Staff Position Ref] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

And a cut down version of staff:

USE [CTCdata]
GO
/****** Object:  Table [dbo].[Staff]    Script Date: 11/03/2009 16:06:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Staff](
	[Staff Ref] [nvarchar](8) NOT NULL,
	[Staff Type Ref] [char](1) NULL,
	[Staff Position Ref] [int] NULL,
	
 CONSTRAINT [PK_Staff] PRIMARY KEY CLUSTERED 
(
	[Staff Ref] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Staff]  WITH NOCHECK ADD  CONSTRAINT [FK_Staff_Departments] FOREIGN KEY([Staff Department])
REFERENCES [dbo].[StaffDepartments] ([Department Ref])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[Staff] NOCHECK CONSTRAINT [FK_Staff_Departments]
GO
ALTER TABLE [dbo].[Staff]  WITH NOCHECK ADD  CONSTRAINT [FK_Staff_StaffPositions] FOREIGN KEY([Staff Position Ref])
REFERENCES [dbo].[StaffPositions] ([Staff Position Ref])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[Staff] NOCHECK CONSTRAINT [FK_Staff_StaffPositions]

Thanks

 

[markros]

Try

UPDATE    T
SET              Total = T.[Time Sheet Hours] * SP.Rate
FROM         TimeSheetData T INNER JOIN                      Staff S ON T.[Staff Ref] = S.[Staff Ref] INNER JOIN   StaffPositions SP ON S.[Staff Position Ref] = SP.[Staff Position Ref] WHERE    T.[Finance Processed] = 0

 

[kylua]

Hi Markros
SQL Server 'verified it against the data source', then gave the message, [navy] 'Column or expression 'Total' cannot be updated.'[/navy], then put a 'cross join' in again. And then stated that T was an invalid object when I verified again.

I've bodged it by looping thru all the position rates and, when they are not null, updating the timesheetdata using an 'IN' with a subquery on staff.

It's not pretty but it works and the performance overhead is not critical as it's done just once a month, but I would have preferred to have got the joins sorted out just so I know for other projects.

 

[markros]

I'm not sure what do you mean here. Did you try to create this query in some sort of a tool?

 

[kylua]

Yes, I'm using it in SQL Server Management Studio.

Haven't tried it from an asp.net script, I was testing it in the 'natural' environment first.

Run it thru asp.net just now:
Corrected a coupla C&P errors and it works!

Thank you Markros! You've just taught me how to do a multi-joined update query and not to trust what Microsoft products tell me!

Have a star!