[sql] dddd
Viewer
- DECLARE @RowCount INT,@RowCountstat INT;
- DECLARE @OrdrItmsStatus INT
- SET @OrdrItmsStatus=1
- --SELECT @RowCount=COUNT(OrdrItmsId) FROM ORDERITEMS GROUP BY OrderId
- --SELECT @RowCountstat=COUNT(OrdrItmsId) FROM ORDERITEMS WHERE ORDERITEMS.OrdrItmsStatus=@OrdrItmsStatus GROUP BY ORDERITEMS.OrderId
- SELECT DISTINCT ORDERHEAD.OrderId,OrderNum , CONVERT(VARCHAR,OrderDate,105) AS OrderDateS, OrderPaymntStat,
- CASE ORDERHEAD.OrderPaymntStat WHEN 0 THEN 'Not Paid' WHEN 1 THEN 'Paid' ELSE 'Partial' END AS OrderPaymntStatS,
- ORDERHEAD.OrdeStat
- -- , CASE WHEN (@OrdrItmsStatus=5) THEN (SELECT CASE WHEN @RowCount =@RowCountstat THEN 'Delivered' ELSE 'Partially Delivered' END)
- -- WHEN (@OrdrItmsStatus=4) THEN (SELECT CASE WHEN @RowCount =@RowCountstat THEN 'Shipped' ELSE 'Partially Shipped' END)
- -- WHEN (@OrdrItmsStatus=3) THEN (SELECT CASE WHEN @RowCount =@RowCountstat THEN 'Processed' ELSE 'Partially Processed' END)
- -- WHEN (@OrdrItmsStatus=2) THEN (SELECT CASE WHEN @RowCount =@RowCountstat THEN 'Open' ELSE 'Partially Processed' END)
- -- WHEN (@OrdrItmsStatus=1) THEN (SELECT CASE WHEN @RowCount =@RowCountstat THEN 'Open' ELSE 'Partially Processed' END) END AS OrdrItmsStatusS
- ,(SELECT COUNT(OrdrItmsId) FROM ORDERITEMS GROUP BY OrderId) AS RowsCount,(SELECT COUNT(OrdrItmsId) AS RowCountstat FROM ORDERITEMS WHERE ORDERITEMS.OrdrItmsStatus=@OrdrItmsStatus GROUP BY ORDERITEMS.OrderId )
- FROM ORDERHEAD INNER JOIN
- ORDERITEMS ON ORDERHEAD.OrderId = ORDERITEMS.OrderId INNER JOIN
- PRODUCTMAST ON ORDERITEMS.ProdId = PRODUCTMAST.ProdId
- WHERE OrdrItmsStatus=@OrdrItmsStatus
- GROUP BY ORDERHEAD.OrderId,ORDERHEAD.OrderNum,ORDERHEAD.OrderDate,ORDERHEAD.OrderPaymntStat,ORDERHEAD.OrdeStat
Editor
You can edit this paste and save as new: