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
As it is written as a view, it can easily be used in SmartList Designer, SmartList Builder, Refreshable Excel Reports, SSRS or other reporting tools.
Update on 6/4/2020: Correct corrupt SQL
