How to Return the Most Recent Logtime for Each Sku

How to return the most recent LogTime for each SKU

This will work in SQL Server and MySQL. (https://www.db-fiddle.com/f/kDhqfwDG2bUcqm5GKUbdj3/0)

First get the maximum LogTime for each combination of EventID and Sku and then join from tblTransactionLog to that.

SELECT     tl.EventID
         , tl.Sku
         , tl.User1
         , tl.LogTime
FROM       dmhost.tblTransactionLog tl
INNER JOIN (
               SELECT EventID
                    , Sku
                    , MAX(LogTime) AS MaxLogTime
               FROM   dmhost.tblTransactionLog
               WHERE  LogTime BETWEEN '7/5/2019' AND '7/9/2019'
           GROUP BY   EventID, Sku
           ) AS tmax ON tl.EventID = tmax.EventID
                        AND tl.Sku = tmax.Sku
                        AND tl.LogTime = tmax.MaxLogTime
WHERE    tl.LogTime BETWEEN '7/5/2019' AND '7/9/2019'

How to return the most recent LogTime for each SKU

This will work in SQL Server and MySQL. (https://www.db-fiddle.com/f/kDhqfwDG2bUcqm5GKUbdj3/0)

First get the maximum LogTime for each combination of EventID and Sku and then join from tblTransactionLog to that.

SELECT     tl.EventID
         , tl.Sku
         , tl.User1
         , tl.LogTime
FROM       dmhost.tblTransactionLog tl
INNER JOIN (
               SELECT EventID
                    , Sku
                    , MAX(LogTime) AS MaxLogTime
               FROM   dmhost.tblTransactionLog
               WHERE  LogTime BETWEEN '7/5/2019' AND '7/9/2019'
           GROUP BY   EventID, Sku
           ) AS tmax ON tl.EventID = tmax.EventID
                        AND tl.Sku = tmax.Sku
                        AND tl.LogTime = tmax.MaxLogTime
WHERE    tl.LogTime BETWEEN '7/5/2019' AND '7/9/2019'

Most Recent Price for an Item

Try using TIES along with ROW_NUMBER:

SELECT TOP 1 WITH TIES ITEM, UNITPRICE, AUDTDATE
FROM OEI
ORDER BY ROW_NUMBER() OVER (PARTITION BY ITEM ORDER BY AUDITDATE DESC);

How to check (count) are returned data?

Your code is all kinds of wrong.

First of all, the 2nd call to "get()" is not necessary here:

return c = Products.get(Products.sku == article).get()

Second of all, you are returning an assignment (?) which makes no sense. Change to:

 return Products.get(Products.sku == article)

If the product exists, it will be returned. If not, a DoesNotExist exception will be raised, making it unnecessary to call "count()" anywhere.

To make the code work even if the product is not found:

try:
    return Products.get(Products.sku == article)
except Products.DoesNotExist:
    # Product was not found.
    return

Zend_Db_Select with 'FOR XML PATH' for SQL Server

Looks like I was close, and with KSiimson's comments about string conversion in mind, this works:

$s1 = $products->select()
               ->setIntegrityCheck(false)
               ->from(array('p2' => 'Products'),
                      new Zend_Db_Expr("',' + Status")
                 )
               ->where('p2.SKU = p1.SKU')
               ->order('Status');

$s2 = $products->select()
               ->from(array('p1' => 'Products'),
                      array('p1.SKU',
                            'Statuses' => new Zend_Db_Expr('(' . $s1 .
                                " FOR XML PATH(''))")
                      )
                 )
               ->group('SKU');

echo $s2;
$dbRowSet = $Products->fetchAll($s2);

This just concats the FOR XML PATH clause with the first query as a string. Not quite as elegant as I was hoping for, but "perfect is the enemy of good".

How to set variable to all in T-SQL?

If your goal to use an optional "all"-value for a parameter, you can do it like this:

DECLARE @sku nvarchar(100) = '%'; -- or any other token

...
WHERE @sku IN (field, '%')

This evaluates to: if @sku is %, the condition is always true and returns all rows.

For any other value, it only filters the rows where field equals the parameter.

Match Cell, compare adjacent values, return non matches

Vlookup with an if statement worked in this case.

"If when you look it up and get a match returns true, show nothing, if different, lookup the value in the Old Database Snapshot and return the value."

=IF(VLOOKUP(A2,Old!A:C,3,FALSE)=C2,"",VLOOKUP(A2,Old!A:C,3,FALSE))

SQL Server : optimize the efficiency with many joins relationship

Expansion on my comment. Note it is just a suggestion, no guarantee if will run faster.

Take the following derived table histORy:

SELECT SKU,SUM([SALES Qty]) AS [Global Sales History Qty - All the years]
FROM dbo.[SALES]
WHERE [PO] IS NOT NULL
GROUP BY SKU

Before you run your query, materialize the derived table in a temporary table:

SELECT SKU,SUM([SALES Qty]) AS [Global Sales History Qty - All the years]
INTO #histORy
FROM dbo.[SALES]
WHERE [PO] IS NOT NULL
GROUP BY SKU

Then use the temporary table in the query:

LEFT JOIN #histORy AS h ON MATINFO.[SKU]=h.[SKU]

In this case you may want to have a index on the SKU field, so you could create the temporary table yourself, slap an index on it, populate with INSERT INTO #history... SELECT ... etc.

---

Related Topics