Iterate through a list using SQL While Loop

Question

I have written a SQL query that joins two tables and calculates the difference between two variables. I want a while loop iterate through the code and do the procedure for a list of organizations numbers orgnr.

Use Intra;
drop table #Tabell1 
go


select Månad = A.Manad, Intrastat = A.varde, Moms = B.vardeutf
into #Tabell1 
From
IntrastatFsum A
join
Momsuppg B
    on A.Orgnr = B.Orgnr
where A.Orgnr = 165564845492
AND A.Ar = 2017
AND B.Ar = A.AR
--AND A.Manad = 11
AND A.Manad = B.Manad
AND A.InfUtf = 'U'
order by A.Manad

select *, Differens = (Intrastat - Moms)/ Moms from #Tabell1

Where do I put the while loop and how should I write it? I don't have a lot of experience with SQL so any help is appreciated.

wasnt clear at all when posting this question was in a big hurry, so sorry guys for that. But what im trying to do is: This code just runs trough some data, calculates the difference in % for a special company. Each month we get a list of companies that show high standard deviation for some variables. So we have to go through them and compare the reported value with the taxreturn. What im trying to do is write a code that compares the values for "Intra" which is the reported value and " Moms" which is reported tax value. That part i have already done what i need to do now is to insert a loop into the code that goes through a list instead where i have stored the orgnr for companies that show high std for some variables.I want it to keep a list of the companies where the difference between the reported values and taxreturn is high, so i can take an extra look at them. Because sometimes the taxreturn and reported value to ous is almost the same, so i try to remove the most obvious cases with automation

Include sample data, include table structure, and show expected output — dfundako
– dfundako, Commented Dec 19, 2017 at 15:50
Look up cursor. Not clear why you need to loop. You don't even need a temp and two selects. — paparazzo
– paparazzo, Commented Dec 19, 2017 at 16:01
SQL is designed to use set based queries. That's not to say a while loop can't be used but I'd do some research into better options. — melkisadek
– melkisadek, Commented Dec 19, 2017 at 16:05

Milnev · Accepted Answer · 2017-12-19 16:04:28Z

2

I don't think you need a loop in your query.

Try changing this where-clause:

where A.Orgnr = 165564845492

To this:

where A.Orgnr in (165564845492, 123, 456, 678)

answered Dec 19, 2017 at 16:04

Milnev

1051 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ravi · Accepted Answer · 2017-12-19 16:57:02Z

0

just remove A.Orgnr = 165564845492 in where clause, there is no need of loop

Use Intra;
drop table #Tabell1 
go


select Månad = A.Manad, Intrastat = A.varde,Moms = B.vardeutf
into #Tabell1 
From
IntrastatFsum A
join
Momsuppg B
    on A.Orgnr = B.Orgnr
where  A.Ar = 2017
AND B.Ar = A.AR
--AND A.Manad = 11
AND A.Manad = B.Manad
AND A.InfUtf = 'U'
order by A.Manad

select *, Differens = (Intrastat - Moms)/ Moms from #Tabell1

edited Dec 19, 2017 at 16:57

answered Dec 19, 2017 at 16:49

Ravi

1,1721 gold badge9 silver badges23 bronze badges

Comments

paparazzo · Accepted Answer · 2017-12-19 17:05:57Z

0

Question is not clear (to me)

select Månad = A.Manad, Intrastat = A.varde, Moms = B.vardeutf 
     , Differens = (A.varde - B.vardeutf) / B.vardeutf
From
IntrastatFsum A
join
Momsuppg B
    on A.Orgnr = B.Orgnr
   AND A.Orgnr in (165564845492, ...)
   AND A.Ar = 2017
   AND A.Manad = B.Manad
   AND A.InfUtf = 'U'
order by A.Manad

answered Dec 19, 2017 at 17:05

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

2 Comments

A.Salehi Over a year ago

You are right i wrote the question in a big hurry, sorry for that. This code just runs trough some data, calculates the difference in % for a special company. Each month we get a list of companies that show high standard deviation against usual reported values. So we have to go through them and compare the reported value with the taxreturn. What im trying to do is write a code that compares the values for "Intra" which is the reported value and " Moms" which is reported tax value. That part is done what i need to do is insert a loop into the code that goes through the list of companies

A.Salehi Over a year ago

and keeps a list of the companies where the difference between the reported values and taxreturn is high. Because sometime the taxreturn and reported value to ous is almost the same, so i try to remove the most obvious cases with automation.

Collectives™ on Stack Overflow

Iterate through a list using SQL While Loop

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related