CODEWITHSUNDEEP
sqlsql-servert-sql
user1455942
user1455942

Reputation:

SQL subquery / select subset of data from separate table

I'm writing an SQL query to extract the printing usage for individual cartridges. I've got the main body of the query down as below but I'm having trouble selecting some specific data to do with the meter readings stored in a separate table.

The below query lists cartridges put into printers with the date they were activated and the date they were deactivated. I would then like to use a MeterReadings table to see what the usage was over that period using the ActivatedDate and DeactivatedDate based on the DeviceID. What I have so far is below

SELECT Devices.DeviceID, 
    Devices.DeviceDescription, 
    DeviceConsumables.ConsumableVariantID,
    ConsumableVariants.Type,
    ConsumableDescriptions.Description,
    MAX(ConsumableReadings.ReadingDate) as DeactivatedDate,
    MIN(ConsumableReadings.ReadingDate) AS ActivatedDate,
    ConsumableReadings.ChangedDate,
    CASE ConsumableVariants.ColourID
    WHEN 1 THEN MAX(MeterReadings.TotalMono) - MIN(MeterReadings.TotalMono)
    ELSE MAX(MeterReadings.TotalColour) - MIN(MeterReadings.TotalColour)
    END AS PrintingDiff,
    ConsumableVariants.ExpectedPageCoverage,
    ConsumableVariants.ExpectedPageYield

FROM Devices

LEFT JOIN DeviceConsumables ON Devices.DeviceID = DeviceConsumables.DeviceID
LEFT JOIN ConsumableVariants ON DeviceConsumables.ConsumableVariantID = ConsumableVariants.ConsumableVariantID
LEFT JOIN ConsumableReadings ON DeviceConsumables.ConsumableID = ConsumableReadings.ConsumableID
LEFT JOIN ConsumableDescriptions ON ConsumableVariants.DescriptionID = ConsumableDescriptions.ConsumableDescriptionID
LEFT JOIN MeterReadings ON DeviceConsumables.DeviceID = MeterReadings.DeviceID

WHERE ConsumableVariants.Type = '3' -- To only get toner cartridges
    AND Devices.DeviceID = '24'
    AND MeterReadings.ScanDateTime > MIN(ConsumableReadings.ReadingDate)
    AND MeterReadings.ScanDateTime < MAX(ConsumableReadings.ReadingDate)

GROUP BY devices.DeviceID, Devices.DeviceDescription,
    DeviceConsumables.ConsumableVariantID, ConsumableVariants.Type, ConsumableDescriptions.Description,
    ConsumableReadings.ChangedDate, ConsumableVariants.ColourID, ConsumableVariants.ExpectedPageCoverage,
    ConsumableVariants.ExpectedPageYield

ORDER BY Devices.DeviceID

This is currently generating the error "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

The calculated fields ActivatedDate and DeactivatedDate are the date ranges I will require. I want to use the case statement to select MAX(MeterReadings.TotalMono) - MIN(MeterReadings.TotalMono) for black and white or MAX(MeterReadings.TotalColour) - MIN(MeterReadings.TotalColour) for colour. This would effectively give me the usage as readings can only go upwards. This would hopefully give me the starting point usage with the MIN and ending point usage with the MAX for the specific DeviceID.

As shown above I'm joining on my MeterReadings table on DeviceID.

I can't figure out how to get the MeterReadings for device x between y and z (where x is DeviceID, y is ActivatedDate and z is DeactivatedDate) so I can then add a calculated column into the case statement. Any help appreciated.

-- Edit For brevity I won't add all the schema in here but what should be enough.

Devices - list of all known devices

DeviceID

DeviceDescription

lots of extra fields that describe the device

DeviceConsumables - What devices use what consumables

ConsumableID

DeviceID - Forign key to device

ConsumableVariantID - Forign key to ConsumableVariant

ConsumableVariant - list of all the consumable variants there are

ConsumableVariantID

Type - 3 here indicates toner, what I'm interested in

ConsumableReadings

ReadingID - PK

ConsumableID - forign key to DeviceConsumables

ReadingDate - last time a reading was taken

ChangedDate - last time a new cartridge was inserted

MeterReadings

ReadingID - PK not to do with PK of consumablereadings

DeviceID

ScanDateTime - time usage scan was taken

TotalMono - total mono at scan time

TotalColour Total colour at scan time

Upvotes: 2

Views: 2414

Answers (3)

Nitin Midha
Nitin Midha

Reputation: 2268

Well you have to break you queries into nested queries ... Below query is not tested, so it may have some syntax problem, but it gives a way to find out what you are looking for ...

SELECT Devices.DeviceID, 
    Devices.DeviceDescription, 
    DeviceConsumables.ConsumableVariantID,
    ConsumableVariants.Type,
    ConsumableDescriptions.Description,
    A.DeactivatedDate,
    A.ActivatedDate,
    A.ChangedDate,
    CASE ConsumableVariants.ColourID
    WHEN 1 THEN MAX(MeterReadings.TotalMono) - MIN(MeterReadings.TotalMono)
    ELSE MAX(MeterReadings.TotalColour) - MIN(MeterReadings.TotalColour)
    END AS PrintingDiff,
    ConsumableVariants.ExpectedPageCoverage,
    ConsumableVariants.ExpectedPageYield

FROM Devices

LEFT JOIN DeviceConsumables ON Devices.DeviceID = DeviceConsumables.DeviceID
LEFT JOIN ConsumableVariants ON DeviceConsumables.ConsumableVariantID = ConsumableVariants.ConsumableVariantID
LEFT JOIN ConsumableReadings ON DeviceConsumables.ConsumableID = ConsumableReadings.ConsumableID
LEFT JOIN ConsumableDescriptions ON ConsumableVariants.DescriptionID = ConsumableDescriptions.ConsumableDescriptionID
LEFT JOIN 
(
   SELECT D.DeviceID,
          MAX(CR.ReadingDate) as DeactivatedDate,
          MIN(CR.ReadingDate) AS ActivatedDate,
          CR.ChangedDate
     FROM Devices D
     LEFT JOIN DeviceConsumables DC ON D.DeviceID = DC.DeviceID
     LEFT JOIN ConsumableReadings CR ON DC.ConsumableID = CR.ConsumableID
    WHERE D.DeviceID = '24'
    GROUP BY D.DeviceID, 
            CR.ChangedDate
) AS A ON DeviceConsumables.DeviceID = A.DeviceID
LEFT JOIN MeterReadings ON A.DeviceID = MeterReadings.DeviceID
WHERE ConsumableVariants.Type = '3' -- To only get toner cartridges
    AND Devices.DeviceID = '24'
    AND MeterReadings.ScanDateTime > A.ActivatedDate
    AND MeterReadings.ScanDateTime < A.DeactivatedDate

GROUP BY devices.DeviceID, Devices.DeviceDescription,
    DeviceConsumables.ConsumableVariantID, ConsumableVariants.Type, ConsumableDescriptions.Description,
    ConsumableReadings.ChangedDate, ConsumableVariants.ColourID, ConsumableVariants.ExpectedPageCoverage,
    ConsumableVariants.ExpectedPageYield

ORDER BY Devices.DeviceID

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Your problem is in your join statement.

Change the following line:

LEFT JOIN ConsumableTypes ON ConsumableVariants.Type = ConsumableVariants.Type

To something like:

LEFT JOIN ConsumableTypes ON ConsumableVariants.Type = ConsumableTypes.Type

(or whatever table you are joining to).

Upvotes: 0

ganders
ganders

Reputation: 7432

First, I'd add to your output the ColourID so you know if you are reading the Mono or Colour values. Second, I believe if you remove the ConsumableID from your group by clause, it should work. ConsumableID rows have one date, and if you include that in your group by, you'll never be able to get a max and min, therefore the difference.

Upvotes: 0

Related Questions