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)

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