Sql Server Query help

Question

Hi

I need help in writing the sql code for the below data.

          Date               Store_ID                    Item_ID                            QTY_ON_HAND

        10/1/2015        1                                50                                    3
        10/2/2015        1                                50                                    0
        10/3/2015        1                                50                                    10
        10/4/2015        1                                50                                    5
        10/5/2015        1                                50                                    1
        10/6/2015        1                                50                                    11
        10/7/2015        1                                50                                    3

There are two things which affect the Items on Hand ; items sell causing the number on hand to get less and items are shipped in ,causing the items on hand to increase.

We need to write a sql Query which shows the date that a shipment has come in .

          Date               Store_ID                    Item_ID                            QTY_ON_HAND

        10/3/2015        1                                50                                    10
        10/6/2015        1                                50                                    11
      
                                

Answer 1

What results do you expect?

Check next query:

;
with CTE as
(
    select *, lag(QTY_ON_HAND) over (order by [Date]) p
    from MyTable
)
select [Date], Store_ID, Item_ID, QTY_ON_HAND 
from CTE
where QTY_ON_HAND > p
order by [Date]

Answer 2

Hi @Aditi Sharma ,

Welcome to Microsoft Q&A!

Please also refer the another solution from below:

;with cte as (  
select ROW_NUMBER() over (order by date) rn,* from yourtable)  
select a.date,a.Store_ID,a.Item_ID,a.QTY_ON_HAND  
from cte a  
left join cte b on a.rn=b.rn+1  
where a.QTY_ON_HAND>b.QTY_ON_HAND  

Output:

date	Store_ID	Item_ID	QTY_ON_HAND  
2015-10-03	1	50	10  
2015-10-06	1	50	11  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Try this:

DECLARE @T TABLE (  
	[Date] date,  
	[Store_ID] int,  
	[Item_ID] int,   
	[QTY_ON_HAND] int  
);  
  
INSERT INTO @T VALUES  
('10/1/2015', 1, 50, 3), ('10/2/2015', 1, 50, 0), ('10/3/2015', 1, 50, 10), ('10/4/2015', 1, 50, 5),  
('10/5/2015', 1, 50, 1), ('10/6/2015', 1, 50, 11), ('10/7/2015', 1, 50, 3);  
  
;WITH CTE AS (  
	SELECT *, LAG([QTY_ON_HAND], 1, NULL) OVER(ORDER BY [Date]) AS QTY_ON_HAND_Next_Day  
	FROM @T  
)  
  
SELECT [Date], [Store_ID], [Item_ID], [QTY_ON_HAND]  
FROM CTE   
WHERE QTY_ON_HAND_Next_Day < [QTY_ON_HAND];  
GO  

Output: