issue performing a very specific SQL query to place data in a different format

Question

Hello,

I am having some trouble dealing with an SQL table that needs to be queried and re-formatted into another table for reporting purposes. Here's the initial table:

id               int, 
logtimestamp    datetime, 
serialnumber    varchar(255), 
robotid         int, 
amountconsumed  float

The robotid's are only from 1 to 4. Every 15-20 minutes, new rows are added. Usually, each robot will report on a single serialnumber but that is not always the case. Periodically, the serialnumber can have duplicates. (This only happens when testing, but it can happen.)

My goal is to sum the amountconsumed for each robotid in a given day and place those values in a results table that looks like this:

id              int, 
logtimestamp   datetime,
robot1consumed float, 
robot2consumed float, 
robot3consumed float, 
robot4consumed float

I am having difficulty creating a query that will accurately take into account the fact that

• there can exist duplicate serialnumbers
• not each serialnumber will have all 4 robotid's (if a robotid does not exist for the day, then the value should be set to 0).

Here is what I have come up with so far (@startDate and @endDates are given):

SELECT 
    timestamp=dateadd(month,((Year(R1.logtimestamp)-1900)*12)+Month(R1.logtimestamp)-1,Day(R1.logtimestamp)-1),
    sum(R1.robot1consumed ), 
    sum(R2.robot1consumed ), 
    sum(R3.robot1consumed ), 
    sum(R4.robot1consumed )
FROM 
    Robot_Consumption R1, 
    Robot_Consumption R2, 
    Robot_Consumption R3, 
    Robot_Consumption R4
WHERE
   R1.robotid = '1' 
   AND R2.robotid = '2' 
   AND R3.robotid = '3' 
   AND R4.robotid = '4' 
   AND R1.logtimestamp BETWEEN @startDate AND @endDate 
   AND R1.serialnumber = R2.serialnumber 
   AND R1.serialnumber = R3.serialnumber 
   AND R1.serialnumber = R4.serialnumber 
GROUP BY 
   Year(R1.logtimestamp), Month(R1.logtimestamp), Day(R1.logtimestamp)

Aside from the constraints listed above, this works fine. Does any have a suggestion to change this query to work given the previously listed constraints?

Answer 1

SELECT  '0000-00-00' + INTERVAL dte DAY AS robot_date,
        id,
        SUM(amountconsumed)
FROM    (
        SELECT 1 AS id
        UNION ALL
        SELECT 2
        UNION ALL
        SELECT 3
        UNION ALL
        SELECT 4
        ) r
-- this is to select all robotids
JOIN
        (
        SELECT  DISTINCT TO_DAYS(logtimestamp) AS dte
        FROM    Robot_Consumption
        WHERE   logtimestamp BETWEEN @startDate AND @endDate 
        ) rd
-- this is to select all days that have any records
LEFT OUTER JOIN
        RobotConsumption rc
ON      rc.robotid = r.id
        AND rc.logtimestamp BETWEEN '0000-00-00' + INTERVAL dte DAY AND '0000-00-00' + INTERVAL dte + 1 DAY 
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    Robot_Consumption rci
        WHERE   rci.robotid = rc.robotid
                AND rci.serial_number = rc.serial_number
                AND rci.id < rc.id
        )
-- this is to handle duplicates
GROUP BY
        dte, id

If you need your results in a row, use:

SELECT  '0000-00-00' + INTERVAL dte DAY AS robot_date,
        COALESCE(
        (
        SELECT  SUM(amountconsumed)
        FROM    Robot_Consumption rc
        WHERE   rc.robotid = 1
                AND rc.logtimestamp BETWEEN '0000-00-00' + INTERVAL dte DAY AND '0000-00-00' + INTERVAL dte + 1 DAY
                AND NOT EXISTS
                (
                SELECT  1
                FROM    Robot_Consumption rci
                WHERE   rci.robotid = rc.robotid
                        AND rci.serial_number = rc.serial_number
                        AND rci.id < rc.id
                )
        ), 0) AS robot1consumed,
        COALESCE(
        (
        SELECT  SUM(amountconsumed)
        FROM    Robot_Consumption rc
        WHERE   rc.robotid = 2
                AND rc.logtimestamp BETWEEN '0000-00-00' + INTERVAL dte DAY AND '0000-00-00' + INTERVAL dte + 1 DAY
                AND NOT EXISTS
                (
                SELECT  1
                FROM    Robot_Consumption rci
                WHERE   rci.robotid = rc.robotid
                        AND rci.serial_number = rc.serial_number
                        AND rci.id < rc.id
                )
        ), 0) AS robot2consumed,
        COALESCE(
        (
        SELECT  SUM(amountconsumed)
        FROM    Robot_Consumption rc
        WHERE   rc.robotid = 3
                AND rc.logtimestamp BETWEEN '0000-00-00' + INTERVAL dte DAY AND '0000-00-00' + INTERVAL dte + 1 DAY
                AND NOT EXISTS
                (
                SELECT  1
                FROM    Robot_Consumption rci
                WHERE   rci.robotid = rc.robotid
                        AND rci.serial_number = rc.serial_number
                        AND rci.id < rc.id
                )
        ), 0) AS robot3consumed,
        COALESCE(
        (
        SELECT  SUM(amountconsumed)
        FROM    Robot_Consumption rc
        WHERE   rc.robotid = 4
                AND rc.logtimestamp BETWEEN '0000-00-00' + INTERVAL dte DAY AND '0000-00-00' + INTERVAL dte + 1 DAY
                AND NOT EXISTS
                (
                SELECT  1
                FROM    Robot_Consumption rci
                WHERE   rci.robotid = rc.robotid
                        AND rci.serial_number = rc.serial_number
                        AND rci.id < rc.id
                )
        ), 0) AS robot4consumed,
FROM    (
        SELECT  DISTINCT TO_DAYS(logtimestamp) AS dte
        FROM    Robot_Consumption
        WHERE   logtimestamp BETWEEN @startDate AND @endDate 
        ) rd