CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
Syed Zunair
Syed Zunair

Reputation: 11

How to get primary key of selected column in row in SQL Server 2012

I have the below table. I want to select 30 pieces of Product Code 011A from the table. Each row contains a number of pieces, in the column PCS. I want to select the 30 pieces in FIFO order based on date, and return the number of pieces selected from each row, so I'll need to know the primary key value for each row that has pieces selected from it. For example, from this data:

Key   Product Code     PCS   Date
 1      011A           10    2015-07-01
 2      011B           20    2015-07-01
 3      011C           20    2015-07-01
 4      011A           12    2015-07-02
 5      011A           40    2015-07-03
 6      011D           60    2015-07-04 
 7      011A           20    2015-07-04

Selecting 30 pieces of product code "011A" should give an output table like:

Key   Product Code     PCS   DATE
 1      011A           10    2015-07-01
 4      011A           12    2015-07-02
 5      011A           8     2015-07-03

You can see that the total number of pieces is 30, and that the maximum number of pieces were selected from the rows with primary key 1 and 4, because they're the first dates. Only 8 were selected from row #5, because it's the next in date order, and only 8 is needed to reach 30 total. Row #7 wasn't needed, so it doesn't show up in the result.

How can I write a query to accomplish this?

Upvotes: 0

Views: 104

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

In SQL Server 2012, you can use cumulative sum:

select t.*
from (select t.*,
             sum(pcs) over (partition by productcode order by date) as cumepcs
      from thetable t
      where productcode = '011A'
     ) t
where cumepcs - pcs < 30;

Doing a cumulative sum in SQL Server 2008 is a bit more work. Here is one way:

select t.*
from (select t.*,
             (select sum(t2.pcs) 
              from thetable t2
              where t2.productcode = t.productcode and
                    t2.date <= t.date
             ) as cumepcs
      from thetable t
      where productcode = '011A'
     ) t
where cumepcs - pcs < 30;

EDIT:

If you want the allocated amounts from each bucket, you need to tweak the size of the last bucket. Change the select to:

select t.*,
       (case when cume_pcs <= 30 then pcs
             else 30 - (cumepcs - pcs)
        end) as allocated_pcs
. . .

Upvotes: 2

Related Questions