Query only the changes in value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query only the changes in value

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2014-01-09 : 15:55:54

I have a table which is a stock ledger. Regularly, a few hundred rows appear with the SKUs for my stock and their inventory at the time. The table has the following fields, id (int), sku (nvarchar), qty (int), date (datetime).

So the table looks something like this

id	SKU	Qty	Date
1	Apple	50	2013-12-23 13:28:00
2	Orange	30	2013-12-23 13:28:00
3	Banana	88	2013-12-23 13:28:00
4	Pear	20	2013-12-23 13:28:00
5	Apple	45	2013-12-24 13:28:00
6	Orange	28	2013-12-24 13:28:00
7	Banana	85	2013-12-24 13:28:00
8	Pear	51	2013-12-24 13:28:00
9	Apple	45	2013-12-25 13:28:00
10	Orange	28	2013-12-25 13:28:00
11	Banana	80	2013-12-25 13:28:00
12	Pear	51	2013-12-25 13:28:00
13	Apple	45	2013-12-26 13:28:00
14	Orange	78	2013-12-26 13:28:00
15	Banana	80	2013-12-26 13:28:00
16	Pear	51	2013-12-26 13:28:00
17	Apple	145	2013-12-27 13:28:00
18	Orange	78	2013-12-27 13:28:00
19	Banana	80	2013-12-27 13:28:00
20	Pear	51	2013-12-27 13:28:00

I wouldn't want to query all 20 results. I just want to know if stock changed. So if stock was deducted (as a result of a sale) or if it increased (a result of a purchase) I would want to see the change. Like this:

id	SKU	Qty	Date
1	Apple	50	2013-12-23 13:28:00
2	Orange	30	2013-12-23 13:28:00
3	Banana	88	2013-12-23 13:28:00
4	Pear	20	2013-12-23 13:28:00
5	Apple	45	2013-12-24 13:28:00
6	Orange	28	2013-12-24 13:28:00
7	Banana	85	2013-12-24 13:28:00
8	Pear	51	2013-12-24 13:28:00
11	Banana	80	2013-12-25 13:28:00
12	Pear	51	2013-12-25 13:28:00
14	Orange	78	2013-12-26 13:28:00
15	Banana	80	2013-12-26 13:28:00
17	Apple	145	2013-12-27 13:28:00

Does anyone know how I might do this?

-Sergio
I use Microsoft SQL 2008

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-01-09 : 17:01:22

Maybe this:

select *
  from youttable as a
 where not exists (select *
                     from yourtable as b
                    where b.id=(select max(id)
                                  from yourtable as c
                                 where c.sku<a.sku
                                )
                      and b.qty=a.qty
                  )

ps.: haven't access to my db right now, so syntax may be off.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-01-09 : 20:58:19

[code]
1 Apple 50 2013-12-23 13:28:00
2 Orange 30 2013-12-23 13:28:00
3 Banana 88 2013-12-23 13:28:00
4 Pear 20 2013-12-23 13:28:00
5 Apple 45 2013-12-24 13:28:00
6 Orange 28 2013-12-24 13:28:00
7 Banana 85 2013-12-24 13:28:00
8 Pear 51 2013-12-24 13:28:00
11 Banana 80 2013-12-25 13:28:00
12 Pear 51 2013-12-25 13:28:00<- why this appear, pear doesn't change since id = 8
14 Orange 78 2013-12-26 13:28:00
15 Banana 80 2013-12-26 13:28:00<- why this appear, banana doesn't change since id = 11
17 Apple 145 2013-12-27 13:28:00
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 06:55:19

Assuming those two rows were typos you can use this


;With CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY [Date] ) AS Seq,*
FROM Table
)

SELECT c1.id,	
c1.SKU,	
c1.Qty,	
c1.[Date]
FROM CTE c1
LEFT JOIN CTE c2
ON c2.SKU = c1.SKU
AND c2.Seq = c1.Seq-1
WHERE COALESCE(c2.Qty,0) <> c1.Qty

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SergioM
Posting Yak Master

170 Posts

Posted - 2014-01-13 : 11:15:42

quote:

Originally posted by visakh16

Assuming those two rows were typos you can use this


;With CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY [Date] ) AS Seq,*
FROM Table
)

SELECT c1.id,	
c1.SKU,	
c1.Qty,	
c1.[Date]
FROM CTE c1
LEFT JOIN CTE c2
ON c2.SKU = c1.SKU
AND c2.Seq = c1.Seq-1
WHERE COALESCE(c2.Qty,0) <> c1.Qty

The rows were typos and the query you put together works exactly as I needed it. Thank you!

-Sergio
I use Microsoft SQL 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-13 : 14:14:42

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources