Solved

staffel with price

Posted on 2009-04-10
3
565 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

Open in new window

0
Comment
Question by:timohorn
3 Comments
 
LVL 40

Expert Comment

by:Sharath
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

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

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

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

Join & Ask a Question