CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Developer
Developer

Reputation: 245

How to find last non zero column with last row in select query

I have below data in my database in the same table. Need to find the last row for each item based on date and if the cost field is zero i need to take the next previous cost. Is there any way to do it in single select query? 

ID     | ItemID | Quantity |    DateTransferred  | StoreID |  DBTimeStamp       | Cost
157132 | 18296  |     2    |    9/27/16 1:09 PM  |  2      | 0x000000006F75E9CF | 0
115352 | 18296  |    -1    |    12/5/15 12:54 PM |  2      | 0x000000003DCCB2ED | 86.068
115351 | 18296  |    -1    |    12/5/15 12:54 PM |  2      | 0x000000003DCCB2EC | 86.068
158745 | 18297  |     1    |    10/15/16 2:04 PM |  2      | 0x0000000072A9CDC3 | 134.5499
157133 | 18297  |     1    |    9/27/16 1:09 PM  |  2      | 0x000000006F75EA40 | 134.5463
156657 | 18297  |     4    |    9/25/16 1:04 PM  |  2      | 0x000000006F75EAA3 | 129.25
158746 | 18298  |     1    |    10/15/16 2:04 PM |  2      | 0x0000000072A9D309 | 81
157134 | 18298  |     1    |    9/27/16 1:09 PM  |  2      | 0x000000006F75EC7E | 81
135793 | 18298  |    -1    |    4/20/16 8:33 AM  |  2      | 0x000000005D608EC2 | 80.85
152263 | 18238  |     2    |    8/10/16 11:34 AM |  2      | 0x000000006A5BB3AC | 19.5
152262 | 18238  |     3    |    8/10/16 11:34 AM |  2      | 0x000000006A5BB25A | 19.5
152261 | 18238  |    -1    |    8/10/16 11:34 AM |  2      | 0x000000006A5BB0DF | 19.5

I'm using SQL server 2008.

Here is the result I want

|   ID      |   ItemID  |   Quantity|   DateTransferred     |   StoreID |   DBTimeStamp         |   Cost        |
|   157132  |   18296   |   2       |   9/27/16 1:09 PM     |   2       |   0x000000006F75E9CF  |   86.068      |
|   158745  |   18297   |   1       |   10/15/16 2:04 PM    |   2       |   0x0000000072A9CDC3  |   134.5499    |
|   158746  |   18298   |   1       |   10/15/16 2:04 PM    |   2       |   0x0000000072A9D309  |   81          |
|   152263  |   18238   |   2       |   8/10/16 11:34 AM    |   2       |   0x000000006A5BB3AC  |   19.5        |

Upvotes: 1

Views: 1194

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use ROW_NUMBER in order to get the latest-per-group value. To find the next non zero Cost value you can use a correlated subquery:

;WITH CTE AS (
   SELECT ID, ItemID, Quantity, DateTransferred, StoreID, DBTimeStamp, 
          CASE 
             WHEN Cost <> 0 THEN Cost
             ELSE (SELECT Top 1 Cost
                   FROM mytable As t2
                   WHERE t1.ItemID = t2.ItemID AND t2.Cost <> 0 AND 
                         t1.DateTransferred > t2.DateTransferred
                   ORDER BY DateTransferred DESC)
          END AS Cost,
          ROW_NUMBER() OVER (PARTITION BY ItemID 
                             ORDER BY DateTransferred DESC) AS rn

   FROM mytable AS t1)
SELECT ID, ItemID, Quantity, DateTransferred, StoreID, DBTimeStamp, Cost
FROM CTE
WHERE rn = 1

Demo here

Upvotes: 2

Related Questions