Solved

# staffel with price

Posted on 2009-04-10
575 Views
Last Modified: 2012-05-06
I have a sp to calculate

We used it before, but now we are having different prices for the books, so its not just the number but als the prices, to check.

The problem is that the numer of lines from the #RO_test has doubled, so the percentage is on two records.

After the first "run" , the sp should get the next percentage, wich is different than the one before.

I tried to create use distinct on crs2, but that won't work

``````ALTER Procedure dbo.SO2_NL
@Isbn1 char(13),
@rol1 char(15),
@jaar char (4)
AS
SET NOCOUNT ON
delete RO_Results
where isbn = @isbn1 and
landcode ='NL' and
jaar = @jaar and
rol = @rol1 and
verkoopsoort ='Boek'

declare @van int
DECLARE @staffel int
declare @som int
DECLARE @aantalvk INT
declare @totalevj int
declare @verkochtperstaffel real
declare @verschilinstaffel int
declare @aantalinstaffel int
declare @rest int
declare @rest1 int
declare @verschilinstaffel1 int
declare @ISBN varchar (13)
declare @land varchar (3)
declare @rol varchar (15)
declare @prijs money
declare @restant int
declare @intcountProperties int
declare	@intcounter int
declare	@lopendaantal int
declare @tim int
declare @land1 varchar (3)
declare @afrekening1 money
declare @jaar1 varchar (4)
declare @intcountProperties1 int
declare	@intcounter1 int
declare @overinstaffel int
declare @percenteller int

create  table  #RO_test
(
ISBN varchar(13) NOT NULL,
rol  varchar(15)  ,
staffel   int,
percentage   real,
totalevj   int,
land   nvarchar(3) not null,
aantalvk   int,
verkochtperstaffel int,
aantal int ,
van int   ,
totaleverkoop int,
rest int,
jaar varchar (4),
afrekening int,
prijs money
)
INSERT into  #RO_test (ISBN, rol, staffel, percentage,totalevj,land,aantalvk,verkochtperstaffel,aantal,van,totaleverkoop,rest,jaar,afrekening,prijs)
SELECT DISTINCT [PI_ISBN-Rechthebbende].ISBN, [PI_ISBN-Rechthebbende].Rol, PI_Calculaties_staffel.Aantal AS staf, PI_Calculaties_staffel.Percentage AS [percent], [resultaat correcties royalties].[totale verkopen],
[verkopen totaal per jaar en per land].land, [verkopen totaal per jaar en per land].Expr1 AS aantal_verkocht, 0 AS verkochtperstaffel, 0 AS aantal,
PI_Calculaties_staffel.van, [verkopen totaal per jaar en per land].Expr1 + [resultaat correcties royalties].[totale verkopen] AS totaleverkoop, 0 AS rest, @jaar AS jaar, 0 AS afrekening,
[verkopen totaal per jaar en per land].prijs/1.06 as prijs FROM [PI_ISBN-Rechthebbende] INNER JOIN [resultaat correcties royalties] ON [PI_ISBN-Rechthebbende].ISBN = [resultaat correcties royalties].ISBN INNER JOIN
[verkopen totaal per jaar en per land] ON [PI_ISBN-Rechthebbende].ISBN = [verkopen totaal per jaar en per land].ISBN AND [resultaat correcties royalties].jaar = [verkopen totaal per jaar en per land].Jaar
INNER JOIN PI_Calculaties_staffel ON [PI_ISBN-Rechthebbende].Staffel = PI_Calculaties_staffel.Staffel AND [PI_ISBN-Rechthebbende].Rol = PI_Calculaties_staffel.Rol WHERE ([PI_ISBN-Rechthebbende].Rol = @rol1)
AND ([PI_ISBN-Rechthebbende].ISBN = @isbn1) AND ([verkopen totaal per jaar en per land].land = 'NL') AND ([verkopen totaal per jaar en per land].Jaar = @jaar)

create  table  #RO_test1
(
id int identity (1,1),
ISBN varchar(13) NOT NULL,
aantal int,
prijzen money,
jaar char (4),
ingangdatum datetime,
land varchar (3),
afrekening int,
verkoopsoort char(15),
)

INSERT into  #RO_test1 (isbn,aantal,prijzen,jaar,land,afrekening,verkoopsoort)
SELECT     ISBN, SUM(som) AS aantal, prijs /1.06 as prijzen , Jaar, land, 0 AS afrekening, 'BOEK' AS verkoopsoort
FROM         dbo.RO_verkopen_isbn
WHERE     (NOT (verkoopsoort IN (N'Club', N'Spec', N'Specials')))
GROUP BY ISBN, Jaar, prijs, land
HAVING      (ISBN = @isbn1) AND (land = 'NL') AND (Jaar = @jaar)

select 	@intcountproperties = count (*),
@intcounter = 1,
@restant = 0,
@prijs=   0
from #Ro_test1

while @intcounter <= @intcountproperties
begin

select  	@restant  = aantal,
@prijs= prijzen
from #RO_test1
where id = @intcounter
set @intcounter1 =1

create clustered index UI_Seq on #RO_test(ISBN,van,prijs)
DECLARE csr1 CURSOR FOR
SELECT  isbn,totalevj,van,percentage,staffel,aantalvk,totaleverkoop,aantal,rest,land,rol,jaar,afrekening,prijs
FROM #RO_test
ORDER BY van
FOR READ ONLY
OPEN csr1

DECLARE csr2 CURSOR FOR
SELECT  percentage
FROM #RO_test
ORDER BY percentage
FOR READ ONLY
OPEN csr2

loop:

set @som = 0
set @lopendaantal =0
set @tim =0
set @jaar1 = @jaar
set @overinstaffel = 0
set @percenteller = 0

FETCH NEXT FROM csr1 INTO @ISBN, @totalevj,@van,@verkochtperstaffel,@staffel,@aantalvk,@verschilinstaffel,@aantalinstaffel,@rest,@land,@rol,@jaar1,@afrekening1,@prijs

loop2:

FETCH NEXT FROM csr2 INTO @verkochtperstaffel
if @percenteller > 0   and @verkochtperstaffel = @verkochtperstaffel
begin

FETCH NEXT FROM csr2 INTO @verkochtperstaffel
set @percenteller = @percenteller +1
end

set @rest1= @aantalvk

WHILE @@FETCH_STATUS = 0

begin
if @aantalvk < 0
begin
set @totalevj = @totalevj + @aantalvk
set @aantalvk = -@aantalvk
set @rest1=  - @aantalvk
set @restant = - @restant
end

if @staffel<999999
begin
Set @verschilinstaffel1 =((@staffel-@van-@totalevj)-@som+@van+@overinstaffel)
if   @staffel   < @totalevj
begin
set @totalevj = @totalevj+@verschilinstaffel1
set @verschilinstaffel1=0
end
set @rest1 = @rest1-@verschilinstaffel1
if @rest1 <= 0
begin
Set @verschilinstaffel1= @verschilinstaffel1+@rest1
set @rest1=0
end
end
else
begin
Set @verschilinstaffel1 = @rest1
set @rest1= 0
end

set @lopendaantal = @lopendaantal + @verschilinstaffel1

if  @restant < @lopendaantal
begin
INSERT INTO  RO_Results VALUES (@ISBN,@totalevj,@van,@verkochtperstaffel,@staffel,@aantalvk,@restant-@tim,@staffel-@van,@rest1,@land,@rol,@prijs,@jaar1,@afrekening1,'BOEK')

set @verschilinstaffel1 = @verschilinstaffel1 - (@restant-@tim)

set @intcounter1 =@intcounter1 +1

select  @restant = aantal
from #RO_test1
where id = @intcounter

end

if @totalevj < @staffel INSERT INTO  RO_Results VALUES (@ISBN,@totalevj,@van,@verkochtperstaffel,@staffel,@aantalvk,@verschilinstaffel1,@staffel-@van,@rest1,@land,@rol,@prijs,@jaar1,@afrekening1,'BOEK')

if  @aantalvk+@totalevj < @staffel 	GOTO einde

set @som = (@staffel-@van-@totalevj)+@van
set @tim=@verschilinstaffel1

if @rest1> 0  set @aantalvk = @rest1 set @overinstaffel = @rest1 goto loop2

FETCH NEXT FROM csr1 INTO @ISBN,@totalevj,@van,@verkochtperstaffel,@staffel,@aantalvk,@verschilinstaffel,@aantalinstaffel,@rest,@land,@rol,@jaar1,@afrekening1,@prijs
end
end
einde:
set @intcounter = @intcounter+1
if @intcounter <= @intcountproperties goto loop

RO_test1 results are

ISBN	aantal	prijzen	Jaar	land	afrekening	verkoopsoort
9789041707246	3293	4,716981	2008	NL	0	BOEK
9789041707246	2399	6,556603	2008	NL	0	BOEK

RO_test results are
ISBN	Rol	staf	percent	totale verkopen	land	aantal_verkocht	verkochtperstaffel	aantal	van	totaleverkoop	rest	jaar	afrekening	prijs
9789041707246	Author	2433	5	0	NL	2399	0	0	0	2399	0	2008	0	6,95
9789041707246	Author	2433	5	0	NL	3293	0	0	0	3293	0	2008	0	5
9789041707246	Author	999999	6	0	NL	2399	0	0	2434	2399	0	2008	0	6,95
9789041707246	Author	999999	6	0	NL	3293	0	0	2434	3293	0	2008	0	5

Because the percentage is double, i get the same percentage twice, but this should be the next  one (6 %)

any suggestions
``````
0
Question by:timohorn
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
3 Comments

LVL 41

Expert Comment

ID: 24120265

Can you tell which records you want in your temp tables from the records provided by you?

RO_test1 results are

ISBN  aantal prijzen  Jaar land afrekening verkoopsoort
9789041707246 3293 4,716981 2008 NL 0  BOEK
9789041707246 2399 6,556603 2008 NL 0  BOEK

RO_test results are
ISBN  Rol staf percent totale verkopen land aantal_verkocht verkochtperstaffel aantal van totaleverkoop rest jaar afrekening prijs
9789041707246 Author 2433 5 0 NL 2399 0  0   0 2399 0  2008 0 6,95
9789041707246 Author 2433 5 0 NL 3293 0  0   0 3293 0  2008 0 5
9789041707246 Author 999999 6 0 NL 2399 0  0   2434 2399 0  2008 0 6,95
9789041707246 Author 999999 6 0 NL 3293 0  0   2434 3293 0  2008 0 5
0

Accepted Solution

timohorn earned 0 total points
ID: 24259166
Found it myself , made the whole sp again.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
###### Suggested Courses
Course of the Month2 days, 4 hours left to enroll

#### 710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.