Select with counting between rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select with counting between rows

Author

Topic

knightrider
Starting Member

3 Posts

Posted - 2004-03-05 : 16:28:26

Ok, maybe it's a classic, but I can't find an elegant solution...

I've got one table: (called PAGES)
Date
Printername
PrintedPages

Once in a while I add data to the table.
Date Printername PrintedPages
2004/01 PRINTER1 10000
2004/01 PRINTER2 5000
2004/01 PRINTER3 7500
2004/02 PRINTER1 10250
2004/02 PRINTER3 8000
2004/03 PRINTER1 10400

Now I want for 1 printer (variable defined in a stored procedure) to get the total pages and the difference between the different rows

create procedure sp_printerpages
(
@printername nvarchar(15)
)
as
select date, Printedpages, ?????? from PAGES where printername = @printername order by date
GO

As a result i'd like to get: (exec sp_printerpages PRINTER1)

2004/01 10000
2004/02 10250 250
2004/03 10400 150

or

2004/02 250
2004/03 150

Can anyone help me with it?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-05 : 16:37:38

[code]select * ,
(
select top 1 (p.printedpages - printedpages)
from pages
where date < p.date and printername = @printername order by date asc
) pagediff
from pages p
where printername = @printername
order by date asc[/code]

knightrider
Starting Member

3 Posts

Posted - 2004-03-05 : 17:26:47

quote:

Originally posted by ehorn

select * , 
	(
	select top 1 (p.printedpages - printedpages) 
	from pages 
	where date < p.date and printername = @printername  order by date asc
	) pagediff 
from pages p
where printername = @printername
order by date asc

Thank you very very much!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-05 : 18:27:55

glad that worked for you Knightrider, and by the way it is a classic here at SQLTeam:

http://www.sqlteam.com/item.asp?ItemID=12654

knightrider
Starting Member

3 Posts

Posted - 2004-03-06 : 09:31:55

quote:

Originally posted by ehorn

select * , 
	(
	select top 1 (p.printedpages - printedpages) 
	from pages 
	where date < p.date and printername = @printername  order by date asc
	) pagediff 
from pages p
where printername = @printername
order by date asc

He Ehorn,
The code works fine, but the result of this query is that it returns the difference starting from one Pagecount (the first one)

I adjusted it by changing the orde by date ASC in the pagediff to DESC, and then I get the difference between each row!

Thanks for pointing me in the right direction!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources