July 3, 2022 at 7:48 pm
Hi all!
I have to do this 177 times more, and it must be possible to make it a bit more automatic.
I have to deliver +200 sets of queries, whish tells that two values should be updated.
When finished, it will be run by my Customer.
First query;
SELECT per_personal_id FROM dbo.personal WHERE per_cpr = '070758-0402'
I find the CPR value in an Excel sheet, and that has to be manual. they are spred out between a lot of other lines, and I know which is right, as i know the people it's about.
The result (per_personal_id, value here 269) is used in this second query:
SELECT CounterBalance_Id
FROM dbo.CounterBalance WHERE CounterBalanceType_id IN (24,27) AND Duration <> '2,08 dage' AND Counterbalance_date = '20220228' AND Employee_id = 269
The result of this query, CounterBalance_Id (here 26473) is used in these two querys (3+4):
UPDATE dbo.CounterBalance SET Minutes = Minutes+ 552 WHERE CounterBalance_Id = 26473
UPDATE dbo.counterbalance
SET Duration = CAST(CAST(CAST(MINUTES AS decimal(7,2))/ CAST(100 AS DECIMAL(7,2)) AS DECIMAL(7,2)) AS VARCHAR(20))+ ' dage'
WHERE CounterBalance_Id = 26473
The value 552 is written by hand - Read from the same Excel sheet.
When this has been finished, the query's 3+4 should be copied to the end of a new query named 'result vacation'
It took me very short to declare two variables:
DECLARE @PERSONAL_ID INT
DECLARE @Counterbalanceid int
But to get result of query one in @personal_id and use that in Query two, And after that to get the result of Query two into @counterbalanceid , and after that to get the @counterbalanceid in query 3+4 is over my head.
The copy thing was just ta make it faster and more safe, but is not important.
And how should i have found the solution on Google?
Best regards
Edvard Korsbæk
July 3, 2022 at 11:37 pm
The input from Excel are key value pair(s)? Suppose the inputs were inserted into a temporary SQL Server table. Would/could it look something like this?
create table #cpr_adjustments(
cpr varchar(20) primary key not null,
min_adj int not null);
insert #cpr_adjustments(cpr, min_adj) values
('070758-0402', 552);
If so imo you could use one UPDATE statement. Maybe something like this
/* update statement */
update c
set [Minutes]=calc.adjusted_minutes,
Duration=concat(cast(cast(calc.adjusted_minutes/100.0 as decimal(7,2)) as varchar(20)), ' dage')
from dbo.CounterBalance c
join dbo.personal p on c.Employee_id=p.per_personal_id
join #cpr_adjustments ca on p.per_cpr=ca.cpr
cross apply (values (c.[Minutes]+ca.min_adj)) calc(adjusted_minutes)
where c.CounterBalanceType_id IN (24,27)
and c.Duration <> '2,08 dage'
and c.Counterbalance_date = '20220228';
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 4, 2022 at 12:58 am
Thanks - I did not know CROSS APPLY and CONCAT - reading on them now.
I live in Denmark, is 70 years old. For me, it's fantastic to have the hole world to help. When i was young, my asking range was my village of. app. 300 people....
Best regards
Edvard Korsbæk
July 11, 2022 at 9:55 am
The input from Excel are key value pair(s)? Suppose the inputs were inserted into a temporary SQL Server table. Would/could it look something like this?
create table #cpr_adjustments(
cpr varchar(20) primary key not null,
min_adj int not null);
insert #cpr_adjustments(cpr, min_adj) values
('070758-0402', 552);If so imo you could use one UPDATE statement. Maybe something like this
/* update statement */
update c
set [Minutes]=calc.adjusted_minutes,
Duration=concat(cast(cast(calc.adjusted_minutes/100.0 as decimal(7,2)) as varchar(20)), ' dage')
from dbo.CounterBalance c
join dbo.personal p on c.Employee_id=p.per_personal_id
join #cpr_adjustments ca on p.per_cpr=ca.cpr
cross apply (values (c.[Minutes]+ca.min_adj)) calc(adjusted_minutes)
where c.CounterBalanceType_id IN (24,27)
and c.Duration <> '2,08 dage'
and c.Counterbalance_date = '20220228';
Is there a reason to do the Cross Apply rather than just doing the addition calculation directly in the UPDATE SET ?
July 11, 2022 at 2:54 pm
Is there a reason to do the Cross Apply rather than just doing the addition calculation directly in the UPDATE SET ?
The value 'calc.adjusted_minutes' is referenced twice in the SELECT list so rather than repeat code the query uses the VALUES constructor to define it once. DRY = Don't Repeat Yourself. It's also a helpful hint/reminder the FROM clause is typically evaluated first (before WHERE and SELECT). From a performance/resource perspective it makes little to no difference
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 11, 2022 at 3:04 pm
Thanks Steve, understood.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy