SQL View to return most recent five purchase orders by vendor

Microsoft Dynamics GPI 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 a Dynamics GP Consultant (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

What should we write about next?





Your Name (required) –
Your Email (required) –

3 thoughts on “SQL View to return most recent five purchase orders by vendor

  1. Rob says:

    I tried to test your view but receiving the following error:
    Msg 156, Level 15, State 1, Procedure uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor, Line 39 [Batch Start Line 4]
    Incorrect syntax near the keyword ‘SELECT’.
    Msg 15151, Level 16, State 1, Line 62
    ==================================================================
    The SELECT means the last select in your view, the script runs fine before the last SELECT
    Any idea? I think you might need a temp table to host union table result before your last SELECT.
    Let me know what your thought.

    1. Ian Grieve says:

      Hi Rob,

      The CTE at the top had been corrupted somehow when I transposed the SQL into the blog.

      I have now corrected this, so the above code should work .

Leave a Reply

Your email address will not be published. Required fields are marked *