sql

##筛选低压事业部正在生产的柜子的数量。一张生产订单一行

SELECT T0.[U_T006], T0.[DocNum], T2.[ItemName],max(T0.[U_T004]),CAST(T0.[PlannedQty] as float)
                FROM OWOR T0 
                LEFT JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry], OITM T2 
                WHERE T0.[ItemCode] =  T2.[ItemCode] 
                AND T1.[PlannedQty] = T1.[IssuedQty] 
                AND T0.[CmpltQty] = 0.00 
                AND T0.[ItemCode] NOT IN ('C1','C2','C3','C4','C5','C6','C7','100000') 
                AND T0.[DueDate] > '2020-01-15' 
                AND T0.[U_T006] <>  '备用'  
                AND T0.[U_T006] NOT LIKE  '%维修%' 
                AND T0.[U_Z20_DEPT] = 'D' 
                AND T0.[DocNum] NOT IN ('12291','12321','12751','12783','12945') 
                GROUP BY T0.[DocNum],T0.[U_T006], T2.[ItemName],T0.[PlannedQty]
                ORDER BY T0.[DocNum]

 ##筛选低压事业部正在生产的工程的数量。

"SELECT a.[U_T006], a.[ItemName], SUM(a.[PlannedQty]) as Planned 
                FROM(SELECT T0.[U_T006], T0.[DocNum], T2.[ItemName], max(T0.[PlannedQty]) as [PlannedQty] 
                FROM OWOR T0 
                LEFT JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry], OITM T2 
                WHERE T0.[ItemCode] =  T2.[ItemCode] 
                AND T1.[PlannedQty] = T1.[IssuedQty] 
                AND T0.[CmpltQty] = 0.00 
                AND T0.[ItemCode] NOT IN ('C1','C2','C3','C4','C5','C6','C7','100000') 
                AND T0.[DueDate] > '2020-01-15' 
                AND T0.[U_T006] <>  '备用'  
                AND T0.[U_T006] NOT LIKE  '%维修%' 
                AND T0.[U_Z20_DEPT] = 'D' 
                AND T0.[DocNum] NOT IN ('12291','12321','12751','12783','12945') 
                GROUP BY T0.[DocNum],T0.[U_T006], T2.[ItemName]) as a 
                GROUP BY a.[U_T006], a.[ItemName] 
                ORDER BY a.[U_T006]"

  ##筛选低压事业部正在生产的工程的数量。（对上面进行了优化，就是max函数那里，对小数点进行了优化）

SELECT a.[U_T006], a.[ItemName],SUM(a.[PlannedQty]) as [PlannedQty]
FROM
(SELECT T0.[U_T006], T0.[DocNum], T2.[ItemName],max(T0.[U_T004])as [U_T004],CAST(T0.[PlannedQty]AS float) as [PlannedQty]
FROM OWOR T0 
LEFT JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry], OITM T2 
WHERE T0.[ItemCode] =  T2.[ItemCode] 
AND T1.[PlannedQty] = T1.[IssuedQty] 
AND T0.[CmpltQty] = 0.00 
AND T0.[ItemCode] NOT IN ('C1','C2','C3','C4','C5','C6','C7','100000') 
AND T0.[DueDate] > '2020-01-15' 
AND T0.[U_T006] <>  '备用'  
AND T0.[U_T006] NOT LIKE  '%维修%' 
AND T0.[U_Z20_DEPT] = 'D' 
AND T0.[DocNum] NOT IN ('12291','12321','12751','12783','12945') 
GROUP BY T0.[DocNum],T0.[U_T006], T2.[ItemName],T0.[PlannedQty])as a
GROUP BY a.[U_T006], a.[ItemName]

#所有已经在生产的工程的所有柜子清单

SELECT b.[U_T006], b.[ItemName] ,SUM(b.[PlannedQty])
FROM
(SELECT MAX(P0.[U_T006]) AS [U_T006],P0.[DocNum], P2.[ItemName], P0.[U_T004] ,CAST(P0.[PlannedQty]AS FLOAT) AS [PlannedQty]
FROM OWOR P0  
LEFT JOIN WOR1 P1 ON P0.[DocEntry] = P1.[DocEntry], 
OITM P2,
(SELECT T0.[OriginNum] FROM OWOR T0 
LEFT JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.[PlannedQty] <> T0.[CmpltQty]
AND T0.[ItemCode] NOT IN ('C0','C1','C2','C3','C4','C5','C6','C7','100000')
AND T0.[U_T006] <>  '备用'  
AND T0.[U_T006] NOT LIKE  '%维修%' 
AND T0.[U_Z20_DEPT] = 'D' 
AND T0.[DocNum] NOT IN ('12291','12321','12751','12783','12945') 
AND T0.[DueDate] > '2020-01-15' 
AND T1.[PlannedQty] = T1.[IssuedQty] 
GROUP BY T0.[OriginNum]) as a
WHERE P0.[OriginNum] = a.[OriginNum] 
AND P0.[ItemCode] =  P2.[ItemCode]
AND P0.[U_Z20_DEPT] = 'D'
AND P0.[Status] = 'R'
AND P0.[DueDate] > '2020-01-15' 
GROUP BY P0.[U_T004], P2.[ItemName],P0.[DocNum],P0.[PlannedQty]) as b
GROUP BY b.[U_T006], b.[ItemName]
ORDER BY b.[U_T006]

 

 

#所有已经发生过领料的工程的所有生产订单

SELECT MAX(P0.[U_T006]),P0.[DocNum], P2.[ItemName], P0.[U_T004] ,CAST(P0.[PlannedQty] AS FLOAT)
FROM OWOR P0  
LEFT JOIN WOR1 P1 ON P0.[DocEntry] = P1.[DocEntry], 
OITM P2,
(SELECT T0.[OriginNum] FROM OWOR T0 
LEFT JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.[PlannedQty] <> T0.[CmpltQty]
AND T0.[ItemCode] NOT IN ('C0','C1','C2','C3','C4','C5','C6','C7','100000')
AND T0.[U_T006] <>  '备用'  
AND T0.[U_T006] NOT LIKE  '%维修%' 
AND T0.[U_Z20_DEPT] = 'D' 
AND T0.[DocNum] NOT IN ('12291','12321','12751','12783','12945') 
AND T0.[DueDate] > '2020-01-15' 
AND T1.[PlannedQty] = T1.[IssuedQty] 
GROUP BY T0.[OriginNum]) as a
WHERE P0.[OriginNum] = a.[OriginNum] 
AND P0.[ItemCode] =  P2.[ItemCode]
AND P0.[U_Z20_DEPT] = 'D'
AND P0.[Status] = 'R'
AND P0.[DueDate] > '2020-01-15' 
GROUP BY P0.[U_T004], P2.[ItemName],P0.[DocNum],P0.[PlannedQty]
ORDER BY P0.[U_T004]

 #给每张生产订单一个定义，是否已经入库了

SELECT MAX(P0.[U_T006]),P0.[DocNum], P2.[ItemName], P0.[U_T004] ,CAST(P0.[PlannedQty] AS FLOAT),
(CASE P0.[CmpltQty] WHEN P0.[PlannedQty] THEN '已入库' ELSE '未完工' END) as '完工情况'
FROM OWOR P0  
LEFT JOIN WOR1 P1 ON P0.[DocEntry] = P1.[DocEntry], 
OITM P2,
(SELECT T0.[OriginNum] FROM OWOR T0 
LEFT JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.[PlannedQty] <> T0.[CmpltQty]
AND T0.[ItemCode] NOT IN ('C0','C1','C2','C3','C4','C5','C6','C7','100000')
AND T0.[U_T006] <>  '备用'  
AND T0.[U_T006] NOT LIKE  '%维修%' 
AND T0.[U_Z20_DEPT] = 'D' 
AND T0.[DocNum] NOT IN ('12291','12321','12751','12783','12945') 
AND T0.[DueDate] > '2020-01-15' 
AND T1.[PlannedQty] = T1.[IssuedQty] 
GROUP BY T0.[OriginNum]) as a
WHERE P0.[OriginNum] = a.[OriginNum] 
AND P0.[ItemCode] =  P2.[ItemCode]
AND P0.[U_Z20_DEPT] = 'D'
AND P0.[Status] = 'R'
AND P0.[DueDate] > '2020-01-15' 
GROUP BY P0.[U_T004], P2.[ItemName],P0.[DocNum],P0.[PlannedQty],P0.[CmpltQty]
ORDER BY P0.[U_T004]

 

"SELECT MAX(P0.[U_T006]),P0.[DocNum], P2.[ItemName], P0.[U_T004] ,CAST(P0.[PlannedQty] AS FLOAT),\
                        (CASE P0.[CmpltQty] WHEN P0.[PlannedQty] THEN '已入库' ELSE '未完工' END) as '完工情况' \
                        FROM OWOR P0 \
                        LEFT JOIN WOR1 P1 ON P0.[DocEntry] = P1.[DocEntry], \
                        OITM P2,\
                        (SELECT T0.[OriginNum] FROM OWOR T0 \
                        LEFT JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry] \
                        WHERE T0.[PlannedQty] <> T0.[CmpltQty] \
                        AND T0.[ItemCode] NOT IN ('C0','C1','C2','C3','C4','C5','C6','C7','100000') \
                        AND T0.[U_T006] <>  '备用' \
                        AND T0.[U_T006] NOT LIKE  '%维修%' \
                        AND T0.[U_Z20_DEPT] = 'D' \
                        AND T0.[DocNum] NOT IN ('12291','12321','12751','12783','12945') \
                        AND T0.[DueDate] > '2020-01-15' \
                        AND T1.[PlannedQty] = T1.[IssuedQty] \
                        GROUP BY T0.[OriginNum]) as a \
                        WHERE P0.[OriginNum] = a.[OriginNum] \
                        AND P0.[ItemCode] =  P2.[ItemCode] \
                        AND P0.[ItemCode] NOT IN ('C0','C1','C2','C3','C4','C5','C6','C7','100000') \
                        AND P0.[U_T006] <>  '备用' \
                        AND P0.[U_T006] NOT LIKE  '%维修%' \
                        AND P0.[U_Z20_DEPT] = 'D' \
                        AND P0.[DocNum] NOT IN ('12291','12321','12751','12783','12945') \
                        AND P0.[Status] = 'R' \
                        AND P0.[DueDate] > '2020-01-15' \
                        GROUP BY P0.[U_T004], P2.[ItemName],P0.[DocNum],P0.[PlannedQty],P0.[CmpltQty] \
                        ORDER BY P0.[U_T004]"

 

 #金工事业部按单据号

SELECT MAX(P0.[U_T006]),P0.[DocNum], P2.[ItemName], P0.[U_T004] ,CAST(P0.[PlannedQty] AS FLOAT),
                        (CASE P0.[CmpltQty] WHEN P0.[PlannedQty] THEN '已入库' ELSE '未完工' END) as '完工情况' 
                        FROM OWOR P0 
                        LEFT JOIN WOR1 P1 ON P0.[DocEntry] = P1.[DocEntry], 
                        OITM P2,
                        (SELECT T0.[OriginNum] FROM OWOR T0 
                        LEFT JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
                        WHERE T0.[PlannedQty] <> T0.[CmpltQty] 
                        AND T0.[ItemCode] NOT IN ('C0','C1','C2','C3','C4','C5','C6','C7','100000')  
                        AND T0.[U_Z20_DEPT] = 'J'
                        AND T0.[OriginNum] is not NULL 
                        AND T0.[DueDate] > '2020-01-15' 
                        AND T0.[Printed] = 'Y'
                        GROUP BY T0.[OriginNum]) as a 
                        WHERE P0.[OriginNum] = a.[OriginNum] 
                        AND P0.[ItemCode] =  P2.[ItemCode] 
                        AND P0.[ItemCode] NOT IN ('C0','C1','C2','C3','C4','C5','C6','C7','100000') 


                        AND P0.[OriginNum] is not NULL 
                        AND P0.[U_Z20_DEPT] = 'J' 
                        AND P0.[Status] = 'R' 
                        AND P0.[DueDate] > '2020-01-15' 
                        GROUP BY P0.[U_T004], P2.[ItemName],P0.[DocNum],P0.[PlannedQty],P0.[CmpltQty] 
                        ORDER BY P0.[U_T004]