"Select" problems

Common questions about 1C:Query language, Query builder tool and Data composition schema

Pages: 1

Sebastian Dan	#1 People who like this: 0 Yes / 0 No Jun 30, 2016 12:57 AM
Active user Rating: 3 Joined: Dec 4, 2015 Company: Smart ID Dynamics	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. Attached Files Download capture2.png (50.09 KB)

Alexei Khatin

People who like this: 1 Yes / 0 No

Jun 30, 2016 01:31 AM

Active user

Rating: 4
Joined: Apr 18, 2012
Company:

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

Sebastian Dan	#3 People who like this: 0 Yes / 0 No Jun 30, 2016 02:16 AM
Active user Rating: 3 Joined: Dec 4, 2015 Company: Smart ID Dynamics	Thank you a lot, Alexei Khatin ! This is what I needed .

Pages: 1

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

Download brochures