Help with Query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help with Query

Author

Topic

Athell
Starting Member

4 Posts

Posted - 2008-11-28 : 04:15:55

Hi,
Please help with a query.

This is what I´ve done so far:
select counterStatPrinterId,printerName, modelName, printerLocation, counterStatChecked,
counterStatType, counterStatCount
from tbl_counter_statistics, tbl_printer, tbl_model
where counterStatPrinterId=printerId
and tbl_printer.printerModel=tbl_model.modelId
order By counterStatChecked desc

The purpose if this query is to get information on Printer total counters (counterStatCount). The problem is that the query generates 605000 rows and contains lots of nulls in "counterStatCount". Is there any way to modify the query to give me 1 row per "printerName" and the most recent "counterStatChecked" (a date) with the largest value in "counterStatCount" (printer total counter).

Thanx in advance
Anders

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-28 : 04:21:45

[code]select printerName,
MAX(counterStatChecked),
MAX(counterStatCount)
from tbl_counter_statistics,
tbl_printer, tbl_model
where counterStatPrinterId = printerId
and tbl_printer.printerModel = tbl_model.modelId
group by printerName[/code]

E 12°55'05.63"
N 56°04'39.26"

Athell
Starting Member

4 Posts

Posted - 2008-11-28 : 05:01:03

Thanx a lot for this fast reply.
It was exactly what I needed.

//Anders

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-28 : 05:06:53

Please notice that the number for StatCount may not be the number associated with the StatChecked value.
They may derive from different records.

E 12°55'05.63"
N 56°04'39.26"

Athell
Starting Member

4 Posts

Posted - 2008-11-28 : 10:12:22

There is 2 different "counterstattype" (0 and 1). Is there any way to get the MAX "Counterstatcount" where "counterstattype" is 0 and 1?
There is 2 counters in a printer (color and BW) and that is "counterstattype" 0 and 1. I want the Max value for both counters with the corresponding printername.

//Anders

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-28 : 11:11:06

Put in an extra row with counterstattype in the SELECT part and put in an extra row in the GROPU BY part with counterstattype.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 11:16:13

quote:
Originally posted by Athell

There is 2 different "counterstattype" (0 and 1). Is there any way to get the MAX "Counterstatcount" where "counterstattype" is 0 and 1?
There is 2 counters in a printer (color and BW) and that is "counterstattype" 0 and 1. I want the Max value for both counters with the corresponding printername.

//Anders

seems like this is what you're looking for


select		printerName,
		MAX(CASE WHEN cs.counterstattype=0 THEN counterStatChecked ELSE NULL END) AS ColorMaxStatChecked ,
                MAX(CASE WHEN cs.counterstattype=1 THEN counterStatChecked ELSE NULL END) AS BWMaxStatChecked ,
		MAX(CASE WHEN cs.counterstattype=0 THEN counterStatCount ELSE NULL END) AS ColorStatCount ,
                MAX(CASE WHEN cs.counterstattype=1 THEN counterStatCount ELSE NULL END) AS BWStatCount 
from		tbl_counter_statistics cs,
inner join	tbl_printer p
on              cs.counterStatPrinterId = p.printerId
inner join      tbl_model m
on              p.printerModel = m.modelId
group by	p.printerName

Try to use join syntax as above rather than old syntax as it wont be supported in future versions of sql server

Athell
Starting Member

4 Posts

Posted - 2008-12-01 : 02:24:18

Thanx a lot, that last query was amazing, works lika a charm.
I´m new to this forum but I love already....

//Anders

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 02:50:48

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources