Channel listings query data duplicated non active sku

Hi I did script to get all active listings form ebay subchannel . 

RIght now I can see in results duplicated SKU.

I did filter for active listings only but it show me old SKU and price for products which has been ended and relisted again on that channel.

The only difference I can see is 'RowId' but cant link it to the active one. 

 can anyone help me to sort this out please


------script-------


DECLARE @kanal NVARCHAR(255) = 'EBAY0'


SELECT DISTINCT 

si.ItemNumber AS SKU,

eb.ChannelSKU AS 'Channel SKU',

eb.SellingPrice AS 'Price',

el.pkListId,

eb.RowId,

sea.startTime,

sea.endTime,

el.ItemTitle,

eb.SubSource,

el.Active,

el.ListingPrice AS 'eBay Price',

CategoryName,



el.startTime,

el.endTime,

el.Qty,

el.Account,

el.ItemNumber,


el.MappedBy,

el.FixedPrice,

el.RelistedFromItemNumber,

el.LastUpdateStatus,

el.LastUpdateDate,

el.ListingPrice,

el.SiteId,

el.Currency,

el.OutOfStockControl,

el.IsGTC,

el.fkStockItemId


FROM StockItem si

LEFT JOIN ProductCategories cat on cat.CategoryId = si.CategoryId

LEFT JOIN eBay_Compatibility ec ON ec.fkStockItemId = si.pkStockItemId

LEFT JOIN eBay_CompatibilityList ecl ON ecl.CompitibilityListId= ec.fkCompitibilityListId   

LEFT JOIN Stock_ChannelSKU eb ON eb.fkStockItemId = si.pkStockItemId

LEFT JOIN Automation_eBayListing el ON el.fkStockItemId = si.pkStockItemId AND el.Account = @kanal

LEFT JOIN Sync_eBayActiveListings sea ON sea.pkListId = el.pkListId

LEFT JOIN Stock_ChannelSKU ebm ON ebm.RowId = eb.RowId

WHERE (eb.SubSource = @kanal) AND el.Active = 1 AND el.Account = @kanal AND si.bLogicalDelete = 0



Hello Damian,


This is Ethan from HyperCommerce. I'm guessing you have innocently changed the custom label in Ebay, hoping that Linnworks would download the listing in the next sync and update the SKU within their database. Unfortunately, Linnworks does not do that. Linnworks will store a new row, each time an SKU is updated on an EBAY channel, even though the same variation / listing already exists within their database.


For instance: You have an Ebay Itemnumber - 281353664867 which has had the custom label updated, your typical results would look something like this:

image


As you can see, Linnworks downloads the SKU again and stores it as an additional row. This can be verified by the custom Variation1 column, which suggests that these are the same variation, linked to the same Linnworks.SKU Column, but you can notice that the first column Last Updated clearly indicates which SKU was recently updated. In this case, the GoodSKU (first row) was updated recently and hence this is the Active SKU on the Channel.


If you need custom solutions such as Advanced SQL Queries, Custom Reports, API Solutions or Integrations, please feel free to reach me at ethan@hypercommerce.co.uk


Login to post a comment