0

I have two Queries which work independently; I need to combine these results.

  1. Fetch all fields (including wdate and Empid), from Fromdate to ToDate.
  2. Calculate a value (for efficiency, Efc), for a specific wdate and Empid from First Query.

1st Query

SELECT * 
  FROM  tblProductionEffcyDetails 
 WHERE wDate BETWEEN '06/26/2019' AND '07/25/2019' 
   AND worker = 'Techn' 
ORDER BY Empid, wDate

2nd Query

SELECT Cast(ROUND(SUM(Tstdmin) / NULLIF(SUM(TAvlblmin), 0) * 100,0) as int) AS [Efc] 
  FROM  tblProductionEffcyDetails 
 WHERE wDate='07/11/2019'  
   AND Empid='00021'  
GROUP BY wdate, Empid

That is, in this 2nd Query, the values for wDate and Empid should come from the results of the 1st Query.

Notes on the data/ table:

  • Any particular date (wDate) or person (Empid) can have any number of entries.
  • Efficiency (Efc) should be given just once per day (wDate) i.e. it should not have multiple values for a particular wDate.

Table structure is as below

SL      wDate       Avlbl_Mins  NP_Mins Empid   Name        Process         Model           Efc 
117571  7/13/2019   0           0       21      MARRY       Block removing      900-2930    80 
117572  7/13/2019   0           0       21      MARRY       Microscope checking 900-2929    Null 
116872  6/26/2019   430         75      52      SUGANTHI    Slab removing       900-2929    75 
116873  6/26/2019   0           0       52      SUGANTHI    Slab Removing       900-2528    Null
Sachi.Dila
  • 1,126
  • 7
  • 15
Shankar T
  • 1
  • 1

2 Answers2

0

Try this,

you can get the particular Empid set for date between 06/26/2019 and 07/25/2019 using your 1st query and by connecting it to your second query you can sum it up.

SELECT Empid, Cast(ROUND(SUM(Tstdmin) / NULLIF(SUM(TAvlblmin), 0) * 100,0) as int) AS [Efc] 
  FROM  tblProductionEffcyDetails t
 WHERE t.Empid in ( 
     SELECT Empid 
      FROM  tblProductionEffcyDetails tb
     WHERE tb.wDate BETWEEN '06/26/2019' AND '07/25/2019' 
       AND tb.worker = 'Techn') and t.wDate BETWEEN '06/26/2019' AND '07/25/2019' 
GROUP BY t.wdate, t.Empid

I made the query as I understood your question, let me know if it didn't get the output you want. so I can change the answer.

Hope this helps.

Sachi.Dila
  • 1,126
  • 7
  • 15
  • What about the first requirement from OP, "Fetch all fields"? – Joakim Danielson Jul 16 '19 at 07:00
  • Hi thanks for the time, its not fetching all the fields. Actually my both individual queries working absolutely fine. but i need to combine those results without mismatch – Shankar T Jul 16 '19 at 07:12
  • if you need all the details you can change the query to SELECT t.*, Cast(ROUND(SUM(Tstdmin) / NULLIF(SUM(TAvlblmin), 0) * 100,0) as int) AS [Efc] and try. If you are getting a aggregated function error meaning there are some field that cannot be grouped. – Sachi.Dila Jul 16 '19 at 07:49
  • better if you can add the table structure in the question. – Sachi.Dila Jul 16 '19 at 07:54
  • t.* also ask for aggregate functions. i have added the sample table in the comment. – Shankar T Jul 16 '19 at 09:27
  • by including the 'Group by' i get result i think. but is that correct to use like that? – Shankar T Jul 16 '19 at 10:22
  • https://stackoverflow.com/questions/13998552/why-do-we-need-group-by-with-aggregate-functions If you read this you'll get a better understanding why we get a aggregate function error when selecting all. – Sachi.Dila Jul 16 '19 at 11:32
  • for example with this query `SL` number 117571 & 117572 will be grouped and will show you as one record. So when you "select *" what `model` should be displayed in that row. is it 900-2930 or 900-2929. These kinds of problems lead to aggregate function error. – Sachi.Dila Jul 16 '19 at 11:34
0
SELECT Empid, Cast(ROUND(SUM(Tstdmin) / NULLIF(SUM(TAvlblmin), 0) * 100,0) as 
int) AS [Efc] 
FROM ( 
 SELECT *
  FROM  tblProductionEffcyDetails tb1
 WHERE tb1.wDate BETWEEN '06/26/2019' AND '07/25/2019' 
   AND tb1.worker = 'Techn') as t  
GROUP BY t.wdate, t.Empid
sultania23
  • 322
  • 3
  • 11
  • What about the first requirement from OP, "Fetch all fields"? – Joakim Danielson Jul 16 '19 at 07:00
  • Yes its not fetching all the fields, if i include * it asks for aggregate function. – Shankar T Jul 16 '19 at 07:11
  • The first Query fetches all values as in the table. second query calculates efficiency for the person on a particular date . So only a single value called efficiency needs to be added to the first query result appropriately. – Shankar T Jul 16 '19 at 07:16
  • I got it but what about other fields? how you want to get these other fields either aggregated or not.Please post some structured format to get better understanding of your problem. – sultania23 Jul 16 '19 at 07:19
  • SL wDate Avlbl_Mins NP_Mins Empid Name Process Model Efc 117571 7/13/2019 0 0 21 MARRY Block removing 900-2930 80 117572 7/13/2019 0 0 21 MARRY Microscope checking 900-2929 Null 116872 6/26/2019 430 75 52 SUGANTHI Slab removing 900-2929 75 116873 6/26/2019 0 0 52 SUGANTHI Slab Removing 900-2528 Null – Shankar T Jul 16 '19 at 07:30
  • i tried to paste small table. the first line upto Efc is header. – Shankar T Jul 16 '19 at 07:32
  • As a sample i gave 9 columns with 4 rows of values. – Shankar T Jul 16 '19 at 07:40