Forum

"Select" problems

Sebastian Dan	#1 Jun 30, 2016 12:57 AM
Active user Points:: 0 Joined:: Dec 4, 2015	Hello! I got a problem with a query. I will give you an example of what my query should do : I have 2 stores ( Store 1 and Store 2) and lots of prices. If i have 5 prices on article "A" in Store 1 and 6 prices on same article but in Store 2, I need to get the last price for this article in both stores. Here is my query .It runs in sql, but not in 1C. Please help me.
	Profile

Alexei Khatin

Jun 30, 2016 01:31 AM

Active user

Points:: 0
Joined:: Apr 18, 2012

Hi, Sebastian Dan.

Try this query

Code

SELECT
   Prices.StoreNo,
   Prices.ArticleCode,
   MAX(Prices.LastDateModified) AS LastDateModified
INTO LastPrices
FROM
   Catalog.Prices AS Prices

GROUP BY
   Prices.StoreNo,
   Prices.ArticleCode
;

////////////////////////////////////////////////////////////////////////////////
SELECT
   Prices.StoreNo,
   Prices.ArticleCode,
   Prices.ExchangeId,
   Prices.FinalPrice,
   Prices.LastDateModified
FROM
   Catalog.Prices AS Prices
      INNER JOIN LastPrices AS LastPrices
      ON Prices.StoreNo = LastPrices.StoreNo
         AND Prices.ArticleCode = LastPrices.ArticleCode
         AND Prices.LastDateModified = LastPrices.LastDateModified

Also I recommend use information register instead catalog. Virtual table "SliceLast" will simplify your query.

Code

SELECT
   PricesSliceLast.Period AS LastDateModified,
   PricesSliceLast.StoreNo,
   PricesSliceLast.ArticleCode,
   PricesSliceLast.ExchangeId,
   PricesSliceLast.FinalPrice
FROM
   InformationRegister.Prices.SliceLast AS PricesSliceLast

Profile

Sebastian Dan	#3 Jun 30, 2016 02:16 AM
Active user Points:: 0 Joined:: Dec 4, 2015	Thank you a lot, Alexei Khatin ! This is what I needed .
	Profile

Users browsing this topic (guests: 1, registered: 0, hidden: 0)