staffel with price
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
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 suggestionsASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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