SQLTeam.com | Weblogs | Forums

Creating a View in SQL 2016


#1

I am creating a view of an existing table to enable a crystal report to run faster.

I have written the SQL

SELECT DISTINCT TOP (100) PERCENT FEE_CODE, VALID_FROM, VARIANT_CONDITION, CURRENCY, PRICE, VALID_TO, DESCRIPTION
FROM ACTA.SMO_PRICING
GROUP BY CURRENCY, PRICE, APPLICATION, CONDITION_TYPE, SALES_ORG, FEE_CODE, MATERIAL_PRICING_GRP, VARIANT_CONDITION, VALID_TO, VALID_FROM, DESCRIPTION, DELETION_IND
HAVING (MATERIAL_PRICING_GRP = '01') AND (DELETION_IND <> 'X') AND (SALES_ORG = '1000')
ORDER BY FEE_CODE, VALID_FROM DESC, VARIANT_CONDITION, CURRENCY, PRICE, VALID_TO, DESCRIPTION

However this returns multiple lines for each Fee_Code with Variant_Condition, I only want to extract the latest record for the combination which is determined by the Valid_From date, which is not a fixed date. Does anyone know how I need to change the script. thank you in advance for any help, I am sure its obvious when you know how :slight_smile:


#2

Your can't (reliably - it may work by serendipity most of the time, but its not guaranteed) use TOP 100 PERCENT and ORDER BY in a VIEW. Take that out and put the Sort Order in the outer SELECT statement.

To get only the most-recent row for [Valid_From] you could try something like

SELECT	FEE_CODE, VARIANT_CONDITION, CURRENCY, PRICE, VALID_TO, DESCRIPTION
FROM
(
	SELECT	[T_RowNumber] = ROW_NUMBER()
			OVER
			(
				PARTITION BY FEE_CODE, VARIANT_CONDITION, CURRENCY, PRICE, VALID_TO
						, DESCRIPTION
				ORDER BY VALID_FROM DESC
			),
		FEE_CODE, VARIANT_CONDITION, CURRENCY, PRICE, VALID_TO, DESCRIPTION
	FROM	ACTA.SMO_PRICING
) AS T
WHERE	T_RowNumber = 1

#3

thank you I will give that a go


#4

A Crystal Report can also use a stored procedure as its source - and in a stored procedure you can pass in the date range you want to select. Using a stored procedure also allows you to sort the data as you want prior to delivering the data to the report - however I would caution you to consider letting the report sort the data especially if the report is doing any type of grouping.

If the report has a fixed sort - and is not configurable by the client - then sorting in SQL Server could save some time when rendering the report, especially if the sorting operation is quite complex. But that is only when sorting the data and does not apply when grouping since grouping will require the report to resort the data according to the groups anyways.