Solved

staffel with price

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now