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
Mysql - Left Join Takes Too Long, How to Optimize Query
Select Distinct Records on a Join
Mysql - Get All Records That Have More Than 1 Record for the Same Id
Multiplying Two Columns in SQL Server
Sql: How to Get Both Match and Non-Match Records
Sql to Find Upper Case Words from a Column
Sql71501 - How to Get Rid of This Error
How to Convert a Timestamp (Date Format) to Bigint in SQL
Select Column Based on Column Name Stored in Another Table
Select First Row in Each GROUP BY Group
Postgres: Insert If Does Not Exist Already
How to Get the Last 12 Months from the Current Date
How to Combine First Name, Middle Name and Last Name in SQL Server
Combine Multiple Columns from Database into One Column
1052: Column 'Id' in Field List Is Ambiguous
Convert String to Date in Ms Access Query
How to Check in Sqlite Whether a Table Exists
Sqlstate[Hy000] [2002] Php_Network_Getaddresses: Getaddrinfo Failed: Name or Service Not Known