Monday, May 10, 2010

Joint Invoice View

Often times it is helpful to know whether or not an invoice is a single matter or joint invoice. The following view is a pretty simple query that determines the answer to that question. You can then join this view to any query to include or exclude joint invoices from your results (or treat them differently).
USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[v_jointinv]
AS
SELECT
linvoice,
COUNT(DISTINCT lmatter) AS NumMtr,
CASE WHEN COUNT(DISTINCT lmatter) >1 THEN 'Yes' ELSE 'No' END AS JointInv
FROM
ledger
INNER JOIN ledcode ON llcode=lccode
WHERE
lcfco IS NOT NULL
GROUP BY linvoice
GO

No comments:

Post a Comment