none
Two Select Statements: Select referring previous select query RRS feed

  • Question

  • Hi Friends, i have two working queries in that I st query fetch all the details from Fromdate to ToDate
    II Query calculate efficiency for every person on all dates, that date and person should come from first Query's processing row. Need combined result of two queries.

    I st Query

     SELECT * FROM  tblProductionEffcyDetails WHERE wDate between '06/26/2019' and '07/25/2019' and worker = 'Techn' order by Empid,wDate

    II nd Query

    select Cast(ROUND(SUM(Tstdmin) / NULLIF(SUM(TAvlblmin), 0) * 100,0) as int) AS [Effcy %] FROM  tblProductionEffcyDetails WHERE wDate='07/11/2019'  and Empid='00021'  group by wdate, Empid

    in the above, IInd query parameters wDate and Empid should come from Ist Query's present processing row.

    Note: Any particular date person can have any number of process/entries . efficiency should be one per day ie it should not repeat every entry of a particular Day.

    Thanks for the guiding experts.

    Shankar T







    • Edited by Shankar T Monday, July 15, 2019 9:55 AM
    Monday, July 15, 2019 9:00 AM

All replies

  • II nd Query
    select Cast(ROUND(SUM(Tstdmin) / NULLIF(SUM(TAvlblmin), 0) * 100,0) as int) AS [Effcy %] FROM  tblProductionEffcyDetails WHERE wDate='07/11/2019'  and Empid='00021'  group by wdate, Empid

    in the above, IInd query parameters wDate and Empid should come from Ist Query's present processing row.

    Try

    -- code #1
    set dateformat mdy;
    SELECT Empid, wDate, cast (round ( sum (Tstdmin) / nullif ((sum (TAvlblmin), 0) * 100), 0) as int) as [Effcy %] from tblProductionEffcyDetails where wDate between '06/26/2019' and '07/25/2019' and worker = 'Techn' group by Empid, wDate;
     

    If this answer was helpful, remember to mark it.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Edited by José Diz Saturday, August 10, 2019 3:11 PM
    Friday, August 9, 2019 10:20 PM