Adding max and case to data?

Question

I'm trying to add a few things to my code here and alter this table, but I'm a bit of a greenhorn in SQL and I'm struggling to make the mental leap to find out how to connect a few things.

1. I'm trying to add a Partition by job to get row 1,2,3,4... (So job 21980 will have 3 rows)

2. I then want to add MAX of ROW NO by JOB (So essentially I want to display the highest of the rows for each job. So job 21980 has 3 rows, I want to just show value 3)

3. I then want to say: when the row number ISN'T the maximum row number, then I want the data in the rows to be ZERO for columns: PO$$, Wip Total, per pc, Standard Cost, DIFF,% of Profit

Current SQL to generate this data:

SELECT  [Job #],
        ,[Date]
        ,[Variance Amt]
        ,[Job QTY] 
        ,[OpenQty] 
        ,[Part #] 
        ,[Material] 
        ,[PCS #] 
        ,[Matrl$$] 
        ,[Date Last Issue] 
        ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders] 
        ,[PO$$] 
        ,[Date Last Rcvd] 
        ,[Wip Total] 
        ,[per pc] 
        ,[Standard Cost] 
        ,[DIFF] 
        ,[% of Profit] 
        FROM [VarianceView] 
        Order By [Job #]

I kind of get how to get steps 1 and 2 started, but that's about all I know.

ROW_NUMBER() OVER(PARTITION BY [Job #]
ORDER BY [Job #] DESC) AS 'RN'
,count(*) over(partition by [Job #]) as maxrn

Some Sample Data

I appreciate all the help in advance!

Answer 1

I think the missing piece to your puzzle is a Common Table Expression. You can't reference a windowed function unless it is first in a common table expression or sub-query. Here's how I would approach your problem. You'll also want your order by clause in the row_number() function to include the way you want the job records ordered. For example, you might want to have them evaluated with the newest [Date Last Issue] as the last record or the first record in the partition.

If you only want to show the top Job # record, you would need to add another row_number() function (e.g. "RowNumberDesc"), but with [Job #] being ordered descending. Then you could add a where clause to the outer query to limit where RowNumberDesc = 1.

with cte as (
    select
        [Job #]
        ,[Date]
        ,[Variance Amt]
        ,[Job QTY] 
        ,[OpenQty] 
        ,[Part #] 
        ,[Material] 
        ,[PCS #] 
        ,[Matrl$$] 
        ,[Date Last Issue] 
        ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders] 
        ,[PO$$] 
        ,[Date Last Rcvd] 
        ,[Wip Total] 
        ,[per pc] 
        ,[Standard Cost] 
        ,[DIFF] 
        ,[% of Profit] 
        ,ROW_NUMBER() OVER(PARTITION BY [Job #] ORDER BY [Date Last Issue]) AS 'RN'
        ,count(*) over(partition by [Job #]) as maxrn
      FROM [VarianceView] 
)

SELECT  [Job #]
        ,[Date]
        ,[Variance Amt]
        ,[Job QTY] 
        ,[OpenQty] 
        ,[Part #] 
        ,[Material] 
        ,[PCS #] 
        ,[Matrl$$] 
        ,[Date Last Issue] 
        ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders] 
        ,case when rn <> maxrn then 0 else [PO$$] end as [PO$$]
        ,[Date Last Rcvd] 
        ,case when rn <> maxrn then 0 else [Wip Total]      end as [Wip Total] 
        ,case when rn <> maxrn then 0 else [per pc]         end as [per pc] 
        ,case when rn <> maxrn then 0 else [Standard Cost]  end as [Standard Cost]
        ,case when rn <> maxrn then 0 else [DIFF]           end as [DIFF] 
        ,case when rn <> maxrn then 0 else [% of Profit]    end as [% of Profit] 
        ,rn as [ROW NO by JOB]
        ,maxrn as [MAX of ROW NO by JOB]
        FROM cte
        Order By [Job #]