SLQ query about the same table

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

Transact-SQL (2008)

SLQ query about the same table

Author

Topic

celeriter
Starting Member

2 Posts

Posted - 2009-04-13 : 12:05:31

Hi guys...
I am a begginer in this tool...
I have a table that contain information about printers, they are identified by the serial number... in the same table a I have information about each one by date, for example:
serial--%toner--date1
seiral2--%toner--date1
.
.
.
serial1--%toner--date2
serial2--%toner--date2
.
.
.
serial1--%toner--date3
serial2--%toner--dadte4

So, I want a query that show me the toner percent by date in the next structure

serial1--%toner(date1)--%toner(date2)---%toner(date3)...
serial2--%toner(date1)--%toner(date2)---%toner(date3)...
serial3--%toner(date1)--%toner(date2)---%toner(date3)...
serial4--%toner(date1)--%toner(date2)---%toner(date3)...
.
.
.

And that later on I will can to see if there were changes, for example. Maybe one day the %toner is 20 and the next is 90 so I can say that there were a toner-change---

Please help me with this...

Regards,

celeriter
Starting Member

2 Posts

Posted - 2009-04-13 : 13:16:43

I did the next...

select noSerial
, toner as '%date14'
, (select toner
from discovery
where date='2009-03-20'
and noSerial='serial1'
) as '%date20'
from discovery
where date ='2009-03-14'
and noSerial='serial1'

with this query I can get in the same answer:

serial %toner14 %toner20
serial1 20 30

But I can do it only with one serial and I want with a lot of them !!!!!

If I erase the "and noSerial='serial1'" to the subquery sql return me the message
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

and of course if I erase the same statement to the query I can get all my list.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-17 : 06:38:56

Not sure what you are asking for.
Looks like you have the columns noSerial, toner, date in the table discovery

maybe something like this - you can put in a where clause if you just want the changes.

select d.noSerial ,
d.date ,
nextdate = d3.date ,
d.toner ,
nexttoner = d3.toner
from discovery d
join discovery d2
on d.noSerial = d2.noSerial
and d2.date = (select min(d3.date) from discovery d3 where d3.noSerial = d1.noSerial and d3.date > d1.date)
order by d.noSerial, d.date

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources