USE [cool]
GO

/****** Object:  View [dbo].[asr_stk_use_view]    Script Date: 2020/6/15 9:50:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[asr_stk_use_view] as
SELECT     row1, SUM(total_qty) AS total_qty, SUM(full_qty) AS full_qty, SUM(null_qty) AS null_qty, SUM(forbid_qty) AS forbid_qty, SUM(empty_qty) AS empty_qty, SUM(use_qty) AS use_qty, CONVERT(float,
                      SUM(CAST(full_qty AS DECIMAL(12, 2))) * 100 / SUM(total_qty)) AS full_rate, CONVERT(float, (SUM(CAST(full_qty AS DECIMAL(12, 2))) + SUM(forbid_qty) + SUM(empty_qty) + SUM(use_qty))
                      * 100 / SUM(total_qty)) AS occ_rate
FROM         (SELECT     row1, COUNT(*) AS total_qty, 0 AS full_qty, 0 AS null_qty, 0 AS forbid_qty, 0 AS empty_qty, 0 AS use_qty
                       FROM          dbo.asr_loc_mast
                       WHERE      (whs_type = 1)
                       GROUP BY row1
                       UNION ALL
                       SELECT     row1, 0 AS Expr1, COUNT(*) AS Expr2, 0 AS Expr3, 0 AS Expr4, 0 AS Expr5, 0 AS Expr6
                       FROM         dbo.asr_loc_mast AS asr_loc_mast_5
                       WHERE     (loc_sts = 'F') AND (whs_type = 1)
                       GROUP BY row1
                       UNION ALL
                       SELECT     row1, 0 AS Expr1, 0 AS Expr2, COUNT(*) AS Expr3, 0 AS Expr4, 0 AS Expr5, 0 AS Expr6
                       FROM         dbo.asr_loc_mast AS asr_loc_mast_4
                       WHERE     (loc_sts = 'O') AND (whs_type = 1)
                       GROUP BY row1
                       UNION ALL
                       SELECT     row1, 0 AS Expr1, 0 AS Expr2, 0 AS Expr3, COUNT(*) AS Expr4, 0 AS Expr5, 0 AS Expr6
                       FROM         dbo.asr_loc_mast AS asr_loc_mast_3
                       WHERE     (loc_sts = 'X') AND (whs_type = 1)
                       GROUP BY row1
                       UNION ALL
                       SELECT     row1, 0 AS Expr1, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4, COUNT(*) AS Expr5, 0 AS Expr6
                       FROM         dbo.asr_loc_mast AS asr_loc_mast_2
                       WHERE     (loc_sts = 'D') AND (whs_type = 1)
                       GROUP BY row1
                       UNION ALL
                       SELECT     row1, 0 AS Expr1, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4, 0 AS Expr5, COUNT(*) AS Expr6
                       FROM         dbo.asr_loc_mast AS asr_loc_mast_1
                       WHERE     (loc_sts = 'R' OR
                                             loc_sts = 'S' OR
                                             loc_sts = 'P' OR
                                             loc_sts = 'Q') AND (whs_type = 1)
                       GROUP BY row1) AS a
GROUP BY row1
GO