[geebra]

Hi,

I am quite a noob but what I trying to do is complete a sql query by looking a the max value and matching it with the corresponding date.

I was able to get the max value but it doesnt work as I want it when I try to add the date.

SELECT     dbo.meterDefineDPView.meterName, MAX(dbo.dailyMeterReading.pressure) AS [Max Pressure]
FROM         dbo.meterDefineDPView INNER JOIN
                      dbo.dailyMeterReading ON dbo.meterDefineDPView.meterId = dbo.dailyMeterReading.meterId
WHERE     (dbo.dailyMeterReading.dataQuality = '11' OR
                      dbo.dailyMeterReading.dataQuality = '10') AND (dbo.meterDefineDPView.meterName IN ('001640', '008776', '002536', '008603', '002526', '005071', 
                      '006240', '008620', '006305', '004720', '005064', '002703')) AND (dbo.dailyMeterReading.contractTime BETWEEN CONVERT(DATETIME, '2012-May-14', 
                      102) AND CONVERT(DATETIME, '2013-May-14', 102))
GROUP BY dbo.meterDefineDPView.meterName

I am already querying with the 'contractTime'

This is the results I get back, but I would also like the corresponding date with it.

Meter name Pressure
001640 6897.4458
002526 6909.6948
002536 6913.7915
002703 5434.4595
004720 6003.3535
005064 5285.4771
005071 5701.0576
006240 5833.3745
006305 5813.6563
008603 6574.1294
008620 6563.9731
008776 6732.4878

Thank you for your guidance!

[r937]

Originally Posted by geebra

...but it doesnt work as I want it when I try to add the date.

but in your WHERE clause, you're already specifying which date you want!

[geebra]

Originally Posted by r937

but in your WHERE clause, you're already specifying which date you want!

Yes because I am searching within that period for the highest pressure. I can come up with the Meter Name with the highest pressure but I would also like it to show the date when the highest pressure happened.

For example:
Meter Name Pressure Date
001640 7000 05/02/13
002526 5000 06/30/12
etc

Thanks

[r937]

Originally Posted by geebra

Yes because I am searching within that period for the highest pressure.

but "within that period" is only one date!!

what am i missing?

[geebra]

Originally Posted by r937

but "within that period" is only one date!!

what am i missing?

The dailyMeterReading tables holds daily data for those meter names.

Say I want to find the highest pressure that happened between may 2012 and may 2013.

I am able to find the highest pressure but I also want to know what day it happened and have it display with the results.

[r937]

Originally Posted by geebra

...between may 2012 and may 2013.

my bad, i read those two dates as being the same

SELECT dbo.meterDefineDPView.meterName
     , maxes.[Max Pressure]
     , dbo.dailyMeterReading.contractTime
  FROM dbo.meterDefineDPView.meterName
INNER
  JOIN ( SELECT meterId
              , MAX(pressure) AS [Max Pressure]
           FROM dbo.dailyMeterReading 
          WHERE dataQuality IN ('10','11') 
            AND contractTime 
                BETWEEN CONVERT(DATETIME, '2012-May-14', 102) 
                    AND CONVERT(DATETIME, '2013-May-14', 102))
         GROUP 
             BY meterId ) AS maxes
    ON maxes.meterId = dbo.meterDefineDPView.meterId
INNER
  JOIN dbo.dailyMeterReading 
    ON dbo.dailyMeterReading.meterId = dbo.meterDefineDPView.meterId
   AND dbo.dailyMeterReading.dataQuality IN ('10','11')  
   AND dbo.dailyMeterReading.pressure = maxes.[Max Pressure] 
 WHERE dbo.meterDefineDPView.meterName IN 
       ('001640', '008776', '002536', '008603', '002526', '005071'
       ,'006240', '008620', '006305', '004720', '005064', '002703')

[geebra]

Originally Posted by r937

my bad, i read those two dates as being the same

SELECT dbo.meterDefineDPView.meterName
     , maxes.[Max Pressure]
     , dbo.dailyMeterReading.contractTime
  FROM dbo.meterDefineDPView.meterName
INNER
  JOIN ( SELECT meterId
              , MAX(pressure) AS [Max Pressure]
           FROM dbo.dailyMeterReading 
          WHERE dataQuality IN ('10','11') 
            AND contractTime 
                BETWEEN CONVERT(DATETIME, '2012-May-14', 102) 
                    AND CONVERT(DATETIME, '2013-May-14', 102))
         GROUP 
             BY meterId ) AS maxes
    ON maxes.meterId = dbo.meterDefineDPView.meterId
INNER
  JOIN dbo.dailyMeterReading 
    ON dbo.dailyMeterReading.meterId = dbo.meterDefineDPView.meterId
   AND dbo.dailyMeterReading.dataQuality IN ('10','11')  
   AND dbo.dailyMeterReading.pressure = maxes.[Max Pressure] 
 WHERE dbo.meterDefineDPView.meterName IN 
       ('001640', '008776', '002536', '008603', '002526', '005071'
       ,'006240', '008620', '006305', '004720', '005064', '002703')

Thanks for the quick reply, when I run this code I get a 'Incorrect syntax near the Keyword GROUP'

[SimonJM]

There is an extra close bracket in the sub-query, I think the one you want to drop is the one at the end of the

                    AND CONVERT(DATETIME, '2013-May-14', 102))

line

[geebra]

Originally Posted by SimonJM

There is an extra close bracket in the sub-query, I think the one you want to drop is the one at the end of the

                    AND CONVERT(DATETIME, '2013-May-14', 102))

line

I deleted that close bracket and checked the sql check, it was good.

Then when I ran it, it gave me this error
Invalid object name 'dbo.meterDefineDPView.meterName'.


I tried adding [] to dbo.meterDefineDPView.[meterName] but doesnt help.

Thanks guys

[r937]

Originally Posted by geebra

Invalid object name 'dbo.meterDefineDPView.meterName'

aaargh, copy/paste error

the FROM clause should reference the table, not a column

[geebra]

Originally Posted by r937

aaargh, copy/paste error

the FROM clause should reference the table, not a column

I thought it would have been a simpler solution(which is why mine doesnt work) but it turns out not to be haha.

Thanks for the help guys!!!!

[geebra]

Originally Posted by geebra

I thought it would have been a simpler solution(which is why mine doesnt work) but it turns out not to be haha.

Thanks for the help guys!!!!

Can you guys also explain to me why we need to create a new table for this to work?

Thanks,
Brandon

[r937]

Originally Posted by geebra

Can you guys also explain to me why we need to create a new table for this to work?

what new table?

[geebra]

Originally Posted by r937

what new table?

haha sorry for my noobness,

JOIN ( SELECT meterId
              , MAX(pressure) AS [Max Pressure]
           FROM dbo.dailyMeterReading 
          WHERE dataQuality IN ('10','11') 
            AND contractTime 
                BETWEEN CONVERT(DATETIME, '2012-May-14', 102) 
                    AND CONVERT(DATETIME, '2013-May-14', 102))
         GROUP 
             BY meterId ) AS maxes

I thought this was making a table maxes and then polling information from that.

[r937]

Originally Posted by geebra

haha sorry for my noobness,

not a problem

i assure you, that condition will not last long

technically, it is a table, since when mysql executes the query, it retrieves the data according to the subquery, and treats it exactly like a table

that's why a subquery in the FROM clause like this is often called a derived table

however, it does not exist after the query finishes execution, so in that sense it's different from a table that you would declare yourself

another name for it is inline view, and that name should also describe how it works