Put result of one query into another

Question

Put result of one query into another

Edvard Korsbæk

SSCertifiable

Points: 5994
More actions
July 3, 2022 at 7:48 pm

#4058236
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
- This topic was modified 2 years, 4 months ago by Edvard Korsbæk.

July 8, 2022 at 2:10 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

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

Edvard Korsbæk SSCertifiable Points: 5994 More actions · Answer 2

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

planetmatt SSCrazy Points: 2207 More actions · Answer 3

Steve Collins wrote:

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 ?

Steve Collins SSCrazy Points: 2157 More actions · Answer 4

planetmatt wrote:

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

planetmatt SSCrazy Points: 2207 More actions · Answer 5

planetmatt

SSCrazy

Points: 2207

July 11, 2022 at 3:04 pm

#4061690

Thanks Steve, understood.