Populate Deviations between 2 rows of table in sql

Question

Populate Deviations between 2 rows of table in sql

Sai Roshna 1

Help me to get the desired output as below for given input

There are multiple/ Many fruits and staples available. few of them keep added to the table when necessary. i added few items for example (apple, kiwi, banana..... etc the list goes on)

i am looking for a query which displays only the items which were having different qty than previous day displaying both previous day qty and today qty.

Bert Zhou-msft 3,521 Reputation points

2022-06-14T01:32:57.74+00:00

Hi，@Sai Roshna
You should try JingyangLi 's code, it perfectly implements your needs and has been tested, please accept it.

Bert Zhou

3 answers

Your answer

Bert Zhou-msft 3,521 Reputation points

2022-06-14T01:32:57.74+00:00

Hi，@Sai Roshna
You should try JingyangLi 's code, it perfectly implements your needs and has been tested, please accept it.

Bert Zhou

Answer 1

Jingyang Li 5,901 Volunteer Moderator

Had a hard time to post.
Here is a link to the solution I posted in gitbub:
8200076ab67788813ce773146e703a85

Sai Roshna 1 Reputation point

2022-06-15T06:21:22.217+00:00

Hi,

Thanks for your code, the qty keeps on updated every day, also list of fruits keeps on added occasionally. so we cant define the name of fruits and qty.

we need to just check the item previous qty and current qty if there is difference then print the today qty and last day's qty (we don't want the difference we want only the procured qty).

Answer 2

Viorel 127K

For provided details, the next script seems acceptable:

drop table if exists #t  
  
select * into #t from Fruits  
  
if (select count(distinct Apple) from #t) = 1 alter table #t drop column Apple  
if (select count(distinct Kiwi) from #t) = 1 alter table #t drop column Kiwi  
if (select count(distinct Grape) from #t) = 1 alter table #t drop column Grape  
if (select count(distinct Orange) from #t) = 1 alter table #t drop column Orange  
if (select count(distinct Banana) from #t) = 1 alter table #t drop column Banana  
  
select * from #t

Sai Roshna 1 Reputation point

2022-06-15T06:17:43.323+00:00

Hi ,

Thanks for the code, But the issue is we keeps on adding as the fruits, pulses other FMCG stuff we are procuring. and the current list is almost 1250+ items it may go to a big number.
we can not list all of them one by one. can you check and update accordingly.

Thanks for your kind support.
Viorel 127K Reputation points

2022-06-15T09:04:24.8+00:00

If the previous queries gives the expected results, but your real table has a large number of columns, then try the attached dynamic query:

211559-fruits.txt

Answer 3

Naomi Nosonovsky 8,906

Looks like your output is the same as input just without Kiwi and Banana columns? Am I right or can you explain the logic behind your output?

0 comments

Share via

Populate Deviations between 2 rows of table in sql

3 answers

Your answer