Multiple Results within a subquerie

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)

Multiple Results within a subquerie

Author

Topic

Hakuzen
Starting Member

24 Posts

Posted - 2009-07-17 : 17:59:11

Hello, I currently have code that inserts specific data into a temp table.

		Select FSEID,  Max(idTSDetail) MaxID, Max(idTSDetail) SecondMaxID, convert(datetime,'1/1/1900',101) as DateFirstCallBack 
	into #CompareTemp 
	from #MultiTicket
	Group By FSEID

This inserts the maximum value of idTSDetail into two seperate columns, for each individual FSE.

This works fine, however I also need to update these columns to the next highest value down. Here is the code:

		Update #comparetemp
			Set #comparetemp.MaxID = 
			(Select max(idTSDetail)
				from #Multiticket inner join #CompareTemp on #CompareTemp.FSEID = #multiticket.FSEID
					Where #CompareTemp.FSEID = #multiticket.FSEID
					)
				           
		
				Update #comparetemp
			Set #comparetemp.SecondMaxID = 
			(Select max(idTSDetail)
				from #Multiticket inner join #CompareTemp on #CompareTemp.FSEID = #multiticket.FSEID
					Where #CompareTemp.FSEID = #multiticket.FSEID
				           and IDTSDetail < #comparetemp.MaxID
				           )

This code works, BUT it only sets ONE maximum value to ALL fse's which is not what I want. When I try adding a group by clause it tells me that it produces multiple results (which is what I want, one for each FSE) but multiple results is not allowed in a subquerie.

Is anyone able to help me with this? The first select statement that is not a subquerie works exactly how I want it to, but you cannot insert a select statement into the same temp table after it has been populated so that is not an option.. Any help would be greatly appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-18 : 00:01:02

[code]
select t.FSEID, max(m.idTSDetail) as MaxID, max(t.idTSDetail) as SecondMaxID, convert(datetime, '19000101') as DateFirstCallBack
from #MultiTicket t
inner join
(
select FSEID, idTSDetail = max(idTSDetail)
from #MultiTicket
group by FSEID
) m on t.FSEID = m.FSEID
and t.idTSDetail <> m.idTSDetail
group by t.FSEID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Hakuzen
Starting Member

24 Posts

Posted - 2009-07-20 : 16:43:06

Hello KH,

how do I use your code to actually update the values?

Thank you!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 18:34:04

update which table ?

KH
[spoiler]Time is always against us[/spoiler]

Hakuzen
Starting Member

24 Posts

Posted - 2009-07-21 : 14:23:31

Well, the point of this is to loop through an update 6 times, each time the maxid, secondmaxid should be updated so that it can pull the new dates for comparison, so in simple terms

get max id, second max id
set datefirstcallback, datecustomerconfirm

when datecustomerconfirm >= datefirstcallback, set multiticket = 1

*Start loop*

get new max id, second max id,
set new datefirstcallback, datecustomerconfirm

when datecustomerconfirm >= datefirstcallback, set multiticket = 1

so each step in the loop it would update the fields, and do the comparison again based on the next set of fields.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 19:30:59

You are looping for FSEID ?

KH
[spoiler]Time is always against us[/spoiler]

Hakuzen
Starting Member

24 Posts

Posted - 2009-07-22 : 11:42:40

grouped by FSEID, is that what you mean?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-22 : 11:46:06

yes. The query i posted on is GROUP BY FSEID. So you get the MaxID and SecondMaxID for each FSEID.

Not sure what kind of looping you need in your post of 07/21/2009 : 14:23:31.

KH
[spoiler]Time is always against us[/spoiler]

Hakuzen
Starting Member

24 Posts

Posted - 2009-07-22 : 15:25:53

the point of the loop is to get the next MaxID and SecondMaxID for each FSEID.

So if there is a MaxID and a SecondMaxID set,
the loop would do something like

Get max(idTSDetail) where idTSDetail < MaxID (Set it as new MaxID)
Get max(idTSDetail) where idTSDetail < MaxID (Set it as SecondMaxID)

Get what I mean? im looping it to do that 6 times so that it can go through an entire week starting with the most recent..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-22 : 18:31:24

you can include the date col into part of the GROUP BY then you don't have to loop for each date


select	t.Datecol, t.FSEID, max(m.idTSDetail) as MaxID, max(t.idTSDetail) as SecondMaxID, convert(datetime, '19000101') as DateFirstCallBack
from	#MultiTicket t
	inner join
	(
		select	Datecol, FSEID, idTSDetail = max(idTSDetail)
		from	#MultiTicket
		group by Datecol, FSEID
	) m	on	t.FSEID      =  m.FSEID
                and     t.Datecol    =  m.Datecol
		and	t.idTSDetail <> m.idTSDetail
where   t.Datecol >= ...
and     t.Datecol <= ...
group  by t.Datecol, t.FSEID

KH
[spoiler]Time is always against us[/spoiler]

Hakuzen
Starting Member

24 Posts

Posted - 2009-07-23 : 12:02:11

There are two date columns that need to be compared against each other, i notice in your code you only had one.. Is that on purpose? two seperate columns, determined by the maxid and secondmaxid

Hakuzen
Starting Member

24 Posts

Posted - 2009-07-23 : 16:02:48

I figured out how to alter your code myself to do what I needed,

Thank you so much for your help!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources