zaccaria.majid
37 lines
701 B
Transact-SQL
37 lines
701 B
Transact-SQL
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 |