SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[v_OrderStatus]
AS
WITH cteDoorGroup
AS (
	SELECT OrderId
		,COUNT(DISTINCT ParentId) AS NumType
		,SUM(Quantity) AS NumDoors
		,SUM(Quantity * UnitCost) AS TotCost
	FROM dbo.Door
	GROUP BY OrderId
	)
SELECT ord.OrderId
	,ord.CompanyId
	,ord.OrderExtCode
	,ord.DateIns
	,ord.UserIdIns
	,ord.DateMod	
	,ord.DateProm
	,ord.DateOrd
	,ord.UserIdMod
	,ord.STATUS AS OrderStatus
	,ord.OrderDescript
	,ISNULL(cte.NumType, 0) AS NumType
	,ISNULL(cte.NumDoors, 0) AS NumDoors
	,ISNULL(cte.TotCost, 0) AS TotCost
	,ord.DateDelivery
	,ord.Discount
FROM dbo.[Order] AS ord
LEFT OUTER JOIN cteDoorGroup AS cte ON ord.OrderId = cte.OrderId
GO