CODEWITHSUNDEEP
sqlerror-handlingmultiple-conditionsminmax
Bella
Bella

Reputation: 1

Calculating emission values based on min or max of a certain column for specific reporting time

I am a SQL beginner and self learner working on improving my SQL fundamentals. I am trying to use retrieve the min and max Entry_ID to calculate emission values based on certain dates.

I have 2 tables which I used left join on a report ID to join. Table 1 contains report submission ID (let's call it Entry_ID), factory name, factory ID, and Reporting Time. Table 2 contains various emission information (CO2, NOX, S0X, etc). I want to use the Min Entry_ID to calculate the start emission on Jan 1st, 2024 (Reporting Time) and Max Entry_ID to calculate yesterday's emission for each factory name. For each day, there are multiple Entry_IDs which is why I need to use the above requirements.

I've tried to use inner subqueries but I get an error as follows:

SELECT
    UPPER(d.Factory_Name) as FactoryName,
    d.DE65_Factory_ID,
    ISNULL(AVG(CASE WHEN d.Entry_ID = (SELECT MIN(Entry_ID) FROM DE65_DailyReport) THEN c.SOX_emission END), 0) AS Start_Sox,
    ISNULL(AVG(CASE WHEN d.Entry_ID = (SELECT MIN(Entry_ID) FROM DE65_DailyReport) THEN c.CO2_emission END), 0) AS Start_Co2,
FROM
    ReportInfo d
LEFT JOIN  
    EmissionInfo c ON d.Report_ID = c.Report_ID
WHERE 
    AND d.Factory_Name = 'Artsy'
GROUP BY
    UPPER(d.Factory_Name),
    d.DE65_Factory_ID

Error: Incorrect syntax near the keyword 'FROM'.

However, this part only calculates the min/max Entry_ID without even stating the dates but I'm already getting an error. How do I go about solving this? Appreciate your input.

Edited to include sample data:

Table 1

Entry_ID Factory_Name Factory_ID Report_ID ReportingTime
1000 Artsy 675 A_1 2024-01-01 21:33:33
1041 Artsy 675 A_2 2024-01-01 7:36:46
1052 Artsy 675 A_3 2024-01-01 17:28:36
1067 Artsy 675 A_4 2024-05-13 6:54:26
1125 Artsy 675 A_5 2024-05-13 20:02:07

Table 2

Report_ID SOX_emission CO2_emission
A_1 224.00 641.00
A_2 330.00 563.00
A_3 595.00 490.00
A_4 217.00 224.00
A_5 564.00 518.00

Desired Output Table

Entry_ID Factory_Name Factory_ID ReportingTime Start_SOX Start_CO2 End_SOX End_CO2
Artsy 675 224.00 641.00 564.00 518.00

Specified Where = 'Artsy' just to make sure code work for one factory first since there's a lot of different factories in the data.

Edit notes:

  1. Changed input Table 1 and Table 2 to show just Artsy for ease, specifying Entries for Jan 1,2024 & yesterday's date (May 14, 2024)
  2. Prefer to include either ReportingTime or Entry_ID in output, but not sure how to visualise them since they'll contain both for Start and End emissions.

Upvotes: 0

Views: 40

Answers (0)

Related Questions