Solved

staffel with price

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

Suggested Solutions

Title # Comments Views Activity
need help in sql 4 63
System Center 2016 SQL Srv 1 24
T-SQL--Two Different Totals Even with the Same Concept 6 44
Sql Join Problem 2 19
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Read about achieving the basic levels of HRIS security in the workplace.
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 SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 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

12 Experts available now in Live!

Get 1:1 Help Now