USE [RFWH]
GO
/****** Object:  View [dbo].[STAGED_UNKNOWN_MIXED_QUERY]    Script Date: 06/05/2008 14:46:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[STAGED_UNKNOWN_MIXED_QUERY]
AS
SELECT     PORTABLE_ID, USER_ID, COUNT(DISTINCT MODEL_NUMBER) AS MIXED_COUNT, TRANS_KEY, 
                      SUM(CASE WHEN MODEL_NUMBER = 'UNKNOWN' THEN 1 ELSE 0 END) AS UNKNOWN_COUNT, CASE WHEN COUNT(DISTINCT MODEL_NUMBER) 
                      > 1 THEN 1 ELSE 0 END AS MIXED_MODELS
FROM         dbo.RFWH_STAGED_SERIAL
GROUP BY PORTABLE_ID, USER_ID, TRANS_KEY