CODEWITHSUNDEEP
sqlsubquery
Erick Ely
Erick Ely

Reputation: 299

SQL Sub-Select, show only items that is not zero

I have been asking questions to get the final results for a SQL query that my company has asked for. I finally got the end result that I was initially asked for, but they want more.

My company wants me to make the query show only values with movement (Sold, Purchased or Adjusted). I don't know how to make this work, or where to look.

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SELECT
[UPC]=t1.F01,
COALESCE((SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and (X.F1034 = 3) AND X.F01 = T1.F01 Group by X.F01,X.F1034),0) AS [Total QTY Sold],
COALESCE((SELECT SUM (COALESCE (X.F65, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and (X.F1034 = 3) AND X.F01 = T1.F01 Group by X.F01,X.F1034),0) AS [Total Amount Sold],
COALESCE((SELECT SUM (COALESCE (X.F1301, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and (X.F1034 = 3) AND X.F01 = T1.F01 Group by X.F01,X.F1034),0) AS [Total Cost Sold],
[Margin]=COALESCE(((1-(COALESCE((SELECT SUM (COALESCE (X.F1301, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and (X.F1034 = 3) AND X.F01 = T1.F01 Group by X.F01,X.F1034),0)/COALESCE((SELECT SUM (COALESCE (X.F65, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and (X.F1034 = 3) AND X.F01 = T1.F01 Group by X.F01,X.F1034),0)))*100),0),
COALESCE((SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and X.F1034 = 8201 AND X.F01 = T1.F01 Group by X.F01,X.F1034),0) AS [QTY Purchased],
COALESCE((SELECT SUM (COALESCE (X.F65, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and X.F1034 = 8201 AND X.F01 = T1.F01 group by X.F01,X.F1034),0) AS [Amount Purchased],
COALESCE((SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and X.F1034 = 8801 AND X.F01 = T1.F01 Group by X.F01,X.F1034),0) AS [QTY Adjusted],
COALESCE((SELECT SUM (COALESCE (X.F65, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and X.F1034 = 8801 AND X.F01 = T1.F01 Group by X.F01,X.F1034),0) AS [Amount Adjusted],
((COALESCE((SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2010-1-1' and X.F254<='2011-11-11') and X.F1034 = 8201 AND X.F01 = T1.F01 Group by X.F01,X.F1034),0)) - (COALESCE((SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2010-1-1' and X.F254<='2011-11-11') and X.F1034 = 3 AND X.F01 = T1.F01 Group by X.F01,X.F1034),0)) + (COALESCE((SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2010-1-1' and X.F254<='2011-11-11') and X.F1034 = 8801 AND X.F01 = T1.F01 Group by X.F01,X.F1034),0))) AS [Current Inventory]

FROM [STORESQL].[dbo].[COST_TAB] t1
where F27='119828' and (F90 is null or F90=1)
group by T1.F01
order by t1.[F01]

A spinet of the results is as follows (shortened headers to make it fit):

UPC  QTY_S  AMT_S  Cost_S  Margin  QTY_P  AMT_P  QTY_A  AMT_A  Inv
123  1      61.19  54      11.75   0      0      0      0      0
456  0      0      0       0       0      0      0      0      5
789  39     38.61  19.5    49.49   2600   1300   0      0      2559
012  0      0      0       0       0      0      0      0      0

The results that I need are as follows:

UPC  QTY_S  AMT_S  Cost_S  Margin  QTY_P  AMT_P  QTY_A  AMT_A  Inv
123  1      61.19  54      11.75   0      0      0      0      0
789  39     38.61  19.5    49.49   2600   1300   0      0      2559

As you may have noticed, Regardless of value in UPC and Inv, I need the query to result in only items with movement.

Thank you in advance

Upvotes: 2

Views: 6448

Answers (2)

jmoreno
jmoreno

Reputation: 13571

Basically you need to get your results and then apply your filter.

; with cte as (
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SELECT
[UPC]=t1.F01,
COALESCE((SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and (X.F1034 = 3) AND X.F01 = T1.F01 Group by X.F01,X.F1034),0) AS [Total QTY Sold],
COALESCE((SELECT SUM (COALESCE (X.F65, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and (X.F1034 = 3) AND X.F01 = T1.F01 Group by X.F01,X.F1034),0) AS [Total Amount Sold],
COALESCE((SELECT SUM (COALESCE (X.F1301, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and (X.F1034 = 3) AND X.F01 = T1.F01 Group by X.F01,X.F1034),0) AS [Total Cost Sold],
[Margin]=COALESCE(((1-(COALESCE((SELECT SUM (COALESCE (X.F1301, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and (X.F1034 = 3) AND X.F01 = T1.F01 Group by X.F01,X.F1034),0)/COALESCE((SELECT SUM (COALESCE (X.F65, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and (X.F1034 = 3) AND X.F01 = T1.F01 Group by X.F01,X.F1034),0)))*100),0),
COALESCE((SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and X.F1034 = 8201 AND X.F01 = T1.F01 Group by X.F01,X.F1034),0) AS [QTY Purchased],
COALESCE((SELECT SUM (COALESCE (X.F65, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and X.F1034 = 8201 AND X.F01 = T1.F01 group by X.F01,X.F1034),0) AS [Amount Purchased],
COALESCE((SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and X.F1034 = 8801 AND X.F01 = T1.F01 Group by X.F01,X.F1034),0) AS [QTY Adjusted],
COALESCE((SELECT SUM (COALESCE (X.F65, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2011-10-1' and X.F254<='2011-11-11') and X.F1034 = 8801 AND X.F01 = T1.F01 Group by X.F01,X.F1034),0) AS [Amount Adjusted],
((COALESCE((SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2010-1-1' and X.F254<='2011-11-11') and X.F1034 = 8201 AND X.F01 = T1.F01 Group by X.F01,X.F1034),0)) - (COALESCE((SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2010-1-1' and X.F254<='2011-11-11') and X.F1034 = 3 AND X.F01 = T1.F01 Group by X.F01,X.F1034),0)) + (COALESCE((SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE (X.F254>='2010-1-1' and X.F254<='2011-11-11') and X.F1034 = 8801 AND X.F01 = T1.F01 Group by X.F01,X.F1034),0))) AS [Current Inventory]

FROM [STORESQL].[dbo].[COST_TAB] t1
where F27='119828' and (F90 is null or F90=1)
group by T1.F01
)
Select * from cte 
where [Total Amount Sold]> 0 or [QTY Purchased] > 0 or [QTY Adjusted] > 0
order by UPC

Upvotes: 1

KM.
KM.

Reputation: 103637

do this:

SELECT
    *
    FROM (
            --your query here (remove order by)
         ) dt
    WHERE dt.QTY_S!=0
    ORDER BY dt.UPC

Upvotes: 8

Related Questions