# Put result of one query into another

• 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

• 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

• 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

• This was removed by the editor as SPAM

• 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 ?

• 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

• Thanks Steve, understood.

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