3

Getting an error

Operand should contain 1 column(s)

PK is ID

The table just dumps data in to the table

need to get the earliest date qty and the latest date qty and display on the same column

Any help appreciated

SELECT ebx_r_history.ItemNumber,
       (SELECT r.QuantitySold as newqty, r.lastupdate as lu
          FROM ebx_r_history r
         WHERE ebx_r_history.ItemNumber = r.ItemNumber AND ebx_r_history.SKU = r.SKU
         ORDER BY r.LastUpdate ASC
         LIMIT 1),
       (SELECT r.QuantitySold as newqty, r.lastupdate as lu
          FROM ebx_r_history r
         WHERE ebx_r_history.ItemNumber = r.ItemNumber AND ebx_r_history.SKU = r.SKU
         ORDER BY r.LastUpdate DESC
         LIMIT 1)
FROM
ebx_r_history
GROUP BY ebx_r_history.ItemNumber,
         ebx_r_history.SKU
ORDER BY ebx_r_history.LastUpdate
Improve this question
1
  • Can you place example data on SQLfiddle? because this look more like an UNION ALL query to me.. Commented Nov 29, 2013 at 23:22

3 Answers 3

Reset to default
1

This version may offer a simplified and faster alternative for you. The inner query for "AllItems" does both a min and max of the last update on a per-item number/sku basis, although I believe they would be one-in-the-same record.

So now, join that results back to the history data by item/sku and only those that match either the min or max date. If a true date/time, there would expect to only be one anyhow, vs just a date-only. So, since there would be 2 possible records (one for the min, one for the max), I am applying a MAX( IIF( )) for each respective matching the minimum and maximum dates respectively and must retain the group by clause.

Note, if you are dealing with date-only entries, or possibilities of the exact same item/sku and lastupdate are the same to the second, then you would need an approach more towards limit 1 per ascending/descending basis.

SELECT
      AllItems.ItemNumber,
      AllItems.SKU,
      AllItems.MinUpdate,
      MAX( IIF( rh.lastupdate = AllItems.MinUpdate, rh.Quantity.Sold, 0 )) as QtyAtMinDate,
      AllItems.MaxUpdate,
      MAX( IIF( rh.lastupdate = AllItems.MaxUpdate, rh.Quantity.Sold, 0 )) as QtyAtMaxDate
   from 
      ( SELECT 
              r.ItemNumber,
              r.SKU,
              MIN( r.lastupdate ) as MinUpdate,
              MAX( r.lastupdate ) as MaxUpdate
          FROM 
             ebx_r_history r
          group by 
             r.ItemNumber,
             r.SKU ) AllItems
      JOIN ebx_r_history rh
         ON AllItems.ItemNumber = rh.ItemNumber
         AND AllItems.SKU = rh.SKU
         AND ( rh.lastUpdate = AllItems.MinUpdate
            OR rh.lastUpdate = AllItems.MaxUpdate )

group by AllItems.ItemNumber, AllItems.SKU

Per another answer where you were only looking to IGNORE items within the most recent 14 days, you can just add a WHERE clause to the inner query similar via 

 WHERE r.LastUpdate >= CURDATE() - INTERVAL 14 DAY

If your history table has an auto-incrementing ID column, AND the respective transactions have the lastUpdate sequentially stamped, such as when they are added and not modified by any other operation, then you could just apply similar but MIN/MAX of the ID column, then join back TWICE on the ID and just each row ONCE such as...

SELECT
      AllItems.ItemNumber,
      AllItems.SKU,
      rhMin.LastUpdate as MinUpdate,
      rhMin.QuantitySold as MinSold,
      rhMax.LastUpdate as MaxUpdate,
      rhMax.QuantitySold as MaxSold
   from 
      ( SELECT 
              r.ItemNumber,
              r.SKU,
              MIN( r.AutoIncrementColumn ) as MinAutoID,
              MAX( r.AutoIncrementColumn ) as MaxAutoID
          FROM 
             ebx_r_history r
          group by 
             r.ItemNumber,
             r.SKU ) AllItems
      JOIN ebx_r_history rhMin
         ON AllItems.MinAutoID = rhMin.AutoIncrementColumn
      JOIN ebx_r_history rhMax
         ON AllItems.MaxAutoID = rhMax.AutoIncrementColumn
   order by
      rhMax.LastUpdated
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Try something like this:

SELECT r1.ItemNumber,
  (
    SELECT r.QuantitySold
    FROM ebx_r_history r
    WHERE r1.ItemNumber = r.ItemNumber
      AND r1.SKU = r.SKU
    ORDER BY r.LastUpdate ASC LIMIT 1
    ) AS earliestDateQty,
  (
    SELECT r.QuantitySold
    FROM ebx_r_history r
    WHERE r1.ItemNumber = r.ItemNumber
      AND r1.SKU = r.SKU
    ORDER BY r.LastUpdate DESC LIMIT 1
    ) AS latestDateQty
FROM ebx_r_history r1
GROUP BY r1.ItemNumber,r1.SKU
ORDER BY 3

You had a couple of errors. you were getting two columns inside the inner selects, and you had a couple of places where you might get the error for ambiguous column name.

Improve this answer

9 Comments

There is an extra comma in the select clause of the first nested select query.
If this works slow, then I guess you can try to move your sub-selects in FROM and join them to ebx_r_history table. Not sure if that way it would work faster, but you can try.
@DarkSide. This isn't easily joined with original table, since you want maxdate quantity and mindate quantity. Joining them isn't very linear..
@FilipeSilva. I just thought, that maybe that way sorting by LastUpdate field will be done only once per 'cursor' and maybe that'll work faster. Not sure, just and idea. Need to try this out on large tables and *_history (audit) table supposed to be large one by definition :)
@DarkSide no the GROUP BY is executed first and gives you the FIFO records based on GROUPING so ORDER BY doesnt always make sense in combination with GROUP BY ..
|
1

sqlFiddle here

SELECT T1.ItemNumber,
       T1.SKU,
       T1.Old_QuantitySold,
       T1.Old_LastUpdate,
       T2.New_QuantitySold,
       T2.New_LastUpdate
FROM
   (SELECT itemNumber,SKU,QuantitySold as Old_QuantitySold,LastUpdate as Old_LastUpdate
      FROM ebx_r_history r
     WHERE NOT EXISTS (SELECT 1 FROM ebx_r_history e
                        WHERE e.itemNumber = r.itemNumber AND e.SKU = r.SKU
                          AND e.LastUpdate < r.LastUpdate)
    )T1 
  LEFT JOIN        
   (SELECT itemNumber,SKU,QuantitySold as New_QuantitySold,LastUpdate as New_LastUpdate
      FROM ebx_r_history r
     WHERE NOT EXISTS (SELECT 1 FROM ebx_r_history e
                        WHERE e.itemNumber = r.itemNumber AND e.SKU = r.SKU
                          AND e.LastUpdate > r.LastUpdate)
    )T2 ON (T2.itemNumber = T1.itemNumber AND T2.SKU = T1.SKU)
WHERE T1.Old_LastUpdate >= CURDATE() - INTERVAL 14 DAY
  AND T2.New_LastUpdate >= CURDATE() - INTERVAL 14 DAY
ORDER BY T2.New_LastUpdate;

you can do left join or inner join it's up to you, since T1 will always get earliest records and T2 will always get latest records for the ItemNumber,SKU grouping.

UPDATED TO IGNORE DATA OLDER THAN 14 DAYS

SELECT T1.ItemNumber,
       T1.SKU,
       T1.Old_QuantitySold,
       T1.Old_LastUpdate,
       T2.New_QuantitySold,
       T2.New_LastUpdate
FROM
   (SELECT itemNumber,SKU,QuantitySold as Old_QuantitySold,LastUpdate as Old_LastUpdate
      FROM ebx_r_history r
     WHERE LastUpdate >= CURDATE() - INTERVAL 14 DAY
       AND NOT EXISTS (SELECT 1 FROM ebx_r_history e
                        WHERE e.itemNumber = r.itemNumber AND e.SKU = r.SKU
                          AND e.LastUpdate >= CURDATE() - INTERVAL 14 DAY
                          AND e.LastUpdate < r.LastUpdate)
    )T1 
  LEFT JOIN        
   (SELECT itemNumber,SKU,QuantitySold as New_QuantitySold,LastUpdate as New_LastUpdate
      FROM ebx_r_history r
     WHERE LastUpdate >= CURDATE() - INTERVAL 14 DAY
       AND NOT EXISTS (SELECT 1 FROM ebx_r_history e
                        WHERE e.itemNumber = r.itemNumber AND e.SKU = r.SKU
                          AND e.LastUpdate >= CURDATE() - INTERVAL 14 DAY
                          AND e.LastUpdate > r.LastUpdate)
    )T2 ON (T2.itemNumber = T1.itemNumber AND T2.SKU = T1.SKU)
ORDER BY T2.New_LastUpdate;

ignore data older than 14 days sqlFiddle here

If you want to use exact time (14 days ago), you can replace occurences of CURDATE() with NOW()

Improve this answer

3 Comments

But i want to restrict the dates to the earliestin the last 14 days and the oldest in the last 14 days, how would i do that
if you want to ignore all data that are older than 14 days old, you'll have to add those conditions into the inner queries of T1 and T2
see the bottom portion of my answer if you want to ignore all data older than 14 days

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.