azurecurve

I don’t recall when I wrote this SQL view, but I stumbled across it when I was looking for something else and thought it worth posting.

It uses a common table expression to return the most recent five purchase orders for each vendor.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor
GO
-- create view
CREATE VIEW uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor AS
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
WITH PurchaseOrders
	(
		PONUMBER
		,DOCDATE
		,VENDORID
		,SUBTOTAL
		,TAXAMNT
		,POSTATUS
		,RowNumber
	)
AS (
	SELECT
		PONUMBER
		,DOCDATE
		,VENDORID
		,SUBTOTAL
		,TAXAMNT
		,POSTATUS
		,ROW_NUMBER() OVER (PARTITION BY VENDORID ORDER BY DOCDATE DESC) AS RowNumber
	FROM
		(
			SELECT
				PONUMBER
				,DOCDATE
				,VENDORID
				,SUBTOTAL
				,TAXAMNT
				,POSTATUS
			FROM
				POP10100
			UNION ALL
				SELECT
					PONUMBER
					,DOCDATE
					,VENDORID
					,SUBTOTAL
					,TAXAMNT
					,POSTATUS
				FROM
					POP30100
		) AS PurchaseOrders
)
SELECT
	PONUMBER
	,DOCDATE
	,VENDORID
	,SUBTOTAL
	,TAXAMNT
	,CASE POSTATUS
		WHEN 1 THEN 'New'
		WHEN 2 THEN 'Released'
		WHEN 3 THEN 'Change Order'
		WHEN 4 THEN 'Received'
		WHEN 5 THEN 'Closed'
		WHEN 6 THEN 'Cancelled'
	END AS POSTATUS
FROM
	PurchaseOrders
WHERE
	RowNumber <= 5
GO

GRANT SELECT ON uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor TO DYNGRP
GO