Looping through an ordered select statment

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)

Looping through an ordered select statment

Author

Topic

Reeve
Starting Member

5 Posts

Posted - 2007-03-22 : 10:48:20

I have a select statement with an order by clause. There are rows in the query that I need to ignore but I can't remove from within the select statement itself.

I've created a temp table (#ICCTemp) in the query that contains the original select statement. I am trying to give each row a unique number (1-n) based on month and year, but it comes back with only the first record being changed n times. It's almost like the update statement in the loop doesn't commit each time and it still reads the first record as having a null value in the ICCID column.

Any help on how I can cycle through the records is appreciated.

set @tempMonth = (select max(returnmonth) from #ICCTemp where iccid is null)
set @tempYear = (select max(returnyear) from #ICCTemp where iccid is null)

set @rowNum = 1
while @rowNum <= (select count(accountid) from #ICCTemp) 
begin
	update #ICCTemp
	set ICCID = @rowNum
	where returnMonth = @tempMonth and returnYear = @tempYear
		
	set @tempMonth = (select max(returnmonth) from #ICCTemp where ICCID is null)
	set @tempYear = (select max(returnyear) from #ICCTemp where ICCID is null)

	set @rowNum = @rowNum + 1
end

select * from #ICCTemp
ORDER BY ReturnYear DESC, ReturnMonth DESC, ShadedData DESC

Kristen
Test

22859 Posts

Posted - 2007-03-22 : 11:15:28

Why not create a #TEMP table with an IDENTITY column.

Then:


CREATE TABLE #TEMP
(
    ID int IDENTITY(1,1) NOT NULL,
    MyPK ... datatype etc. according to the Primary Key column(s) in #ICCTemp
)

INSERT INTO #TEMP
SELECT MyPKCol
FROM #ICCTemp
WHERE ICCID is null
ORDER BY MyCol1, MyCol2 -- i.e. the order in which you wish to assign Identity values

so you now have all the PKs of all the rows where ICCID is Null, and you have an Identity value on each one.

Then you can do:
[code]
update U
set ICCID = T.ID -- i.e. the Identity column in #TEMP
FROM #ICCTemp AS U
JOIN #TEMP AS T
ON T.MyPK = U.MyPK
[code]
Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-22 : 11:17:35

Some sample data and more info about what you are trying to do would be helpful as well. It looks like you are trying to force some formatting into your SQL code, which is usually more trouble than it is worth and much easier to do at the presentation layer, but that's just a guess, it's hard to tell for sure w/o more info.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-22 : 11:36:58

I think I will spoil the fun now...

SELECT     *
FROM       #ICCTemp
ORDER BY  (12 * ReturnYear + ReturnMonth) DESC,
          ShadedData DESC

Peter Larsson
Helsingborg, Sweden

Reeve
Starting Member

5 Posts

Posted - 2007-03-22 : 11:37:52

Kristen, I'm a little confused by your post. You're too smart for me. :) The temp table doesn't have a PK. I just put the query in table format.

Here is the entire query if it helps.

declare
@AccountID int,
@StartMonth	int,
@StartYear	int,
@EndMonth	int,
@EndYear	int,
@tempMonth	int,
@tempYear	int,
@rowNum		int

set @Accountid = -
set @startmonth = 1
set @startyear = 1997
set @endmonth = 12
set @endyear = 2006

create table #ICCTemp
(
	ICCID int,
	AccountID int,
	Frequency char(1),
	Quarter int,
	ReturnMonth int,
	ReturnYear int,
	ShadedData bit,
	SlocumData bit,
	Gross decimal(12, 4),
	Net decimal(12, 4),
	MarketValue money,
	GrossICCReturnID int,
	NetICCReturnID int
)
insert into #ICCTemp(AccountID, Frequency, Quarter, ReturnMonth, ReturnYear, ShadedData, SlocumData, Gross, Net, MarketValue, GrossICCReturnID, NetICCReturnID)
(
	SELECT DISTINCT   AccountID, Frequency, dbo.fn_Quarter(ReturnMonth) AS Quarter, ReturnMonth, ReturnYear, ShadedData, SlocumData,
							  (SELECT     MIN(ReturnValue)
								FROM          JSAReturns JSAR
								WHERE      JSAR.AccountID = JSAReturns.AccountID AND JSAR.ReturnMonth = JSAReturns.ReturnMonth AND 
													   JSAR.ReturnYear = JSAReturns.ReturnYear AND JSAR.Frequency = JSAReturns.Frequency AND 
													   JSAR.ShadedData = JSAReturns.ShadedData AND JSAR.AssetCategoryID = 19) AS Gross,
							  (SELECT     MIN(ReturnValue)
								FROM          JSAReturns JSAR
								WHERE      JSAR.AccountID = JSAReturns.AccountID AND JSAR.ReturnMonth = JSAReturns.ReturnMonth AND 
													   JSAR.ReturnYear = JSAReturns.ReturnYear AND JSAR.Frequency = JSAReturns.Frequency AND 
													   JSAR.ShadedData = JSAReturns.ShadedData AND JSAR.AssetCategoryID = 18) AS Net,
							  (SELECT     MIN(EndingMV)
								FROM          JSAReturns JSAR
								WHERE      JSAR.AccountID = JSAReturns.AccountID AND JSAR.ReturnMonth = JSAReturns.ReturnMonth AND 
													   JSAR.ReturnYear = JSAReturns.ReturnYear AND JSAR.Frequency = JSAReturns.Frequency AND 
													   JSAR.ShadedData = JSAReturns.ShadedData AND JSAR.AssetCategoryID = 19) AS MarketValue,
							  (SELECT     MIN(ICCReturnID)
								FROM          JSAReturns JSAR
								WHERE      JSAR.AccountID = JSAReturns.AccountID AND JSAR.ReturnMonth = JSAReturns.ReturnMonth AND 
													   JSAR.ReturnYear = JSAReturns.ReturnYear AND JSAR.Frequency = JSAReturns.Frequency AND 
													   JSAR.ShadedData = JSAReturns.ShadedData AND JSAR.AssetCategoryID = 19) AS GrossICCReturnID,
							  (SELECT     MIN(ICCReturnID)
								FROM          JSAReturns JSAR
								WHERE      JSAR.AccountID = JSAReturns.AccountID AND JSAR.ReturnMonth = JSAReturns.ReturnMonth AND 
													   JSAR.ReturnYear = JSAReturns.ReturnYear AND JSAR.Frequency = JSAReturns.Frequency AND 
													   JSAR.ShadedData = JSAReturns.ShadedData AND JSAR.AssetCategoryID = 18) AS NetICCReturnID
	FROM         dbo.JSAReturns
	WHERE      (((AccountID = @AccountID) AND (ReturnYear > @StartYear AND ReturnYear < @EndYear)) OR
						  ((AccountID = @AccountID) AND (ReturnYear = @StartYear) AND (ReturnMonth >= @StartMonth) AND (@StartYear<>@EndYear)) OR
						  ((AccountID = @AccountID) AND (ReturnYear = @EndYear) AND (ReturnMonth <= @EndMonth) AND (@StartYear<>@EndYear)) OR 
						  ((AccountID = @AccountID) AND (ReturnYear = @StartYear) AND (ReturnMonth >= @StartMonth) AND (ReturnMonth <= @EndMonth) AND (@StartYear=@EndYear)))
						  and (assetcategoryid = 19 or assetcategoryid = 18)
	--ORDER BY ReturnYear DESC, ReturnMonth DESC, ShadedData DESC
)
set @tempMonth = (select max(returnmonth) from #ICCTemp where iccid is null)
set @tempYear = (select max(returnyear) from #ICCTemp where iccid is null)

set @rowNum = 1
while @rowNum <= (select count(accountid) from #ICCTemp) 
begin
	update #ICCTemp
	set ICCID = @rowNum
	where returnMonth = @tempMonth and returnYear = @tempYear
		
	set @tempMonth = (select max(returnmonth) from #ICCTemp where ICCID is null)
	set @tempYear = (select max(returnyear) from #ICCTemp where ICCID is null)

	set @rowNum = @rowNum + 1
end

select * from #ICCTemp
ORDER BY ReturnYear DESC, ReturnMonth DESC, ShadedData DESC

drop table #ICCTemp

And the first 10/75 rows it produces.

ICCID	AcctID	Freq	Quar	Month	Year	Shad	Sloc	Gross	Net	MV		GrossID	NetID
75	-	M	4	12	2006	0	1	3.8200	3.7199	6691811.00	527480	527479
NULL	-	M	4	11	2006	0	1	3.8600	3.7600	6451870.00	508728	508727
NULL	-	M	4	10	2006	0	1	2.8100	2.7099	6218150.00	496899	496898
NULL	-	M	3	9	2006	0	1	0.2699	0.1700	6053913.00	488767	488766
NULL	-	M	3	8	2006	0	1	2.8200	2.7199	6043384.00	411929	411928
NULL	-	M	3	7	2006	0	1	0.9300	0.8299	5883359.00	405117	405116
NULL	-	M	2	6	2006	0	1	0.0999	0.0000	5834929.00	399171	399170
NULL	-	M	2	5	2006	0	1	-5.0700	-5.1700	5834927.00	415403	415402
NULL	-	M	2	4	2006	0	1	4.4600	4.3600	6152867.00	371853	371852
NULL	-	M	1	3	2006	0	1	4.0400	3.9299	5895986.00	356025	356024

edit: all AcctID's are the same.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-22 : 11:38:48

[code]update #ICCTemp
set ICCID = 12 * returnYear + returnMonth[/code]

Peter Larsson
Helsingborg, Sweden

Reeve
Starting Member

5 Posts

Posted - 2007-03-22 : 11:43:12

Nice idea Peso. It works, but is it clunky or inefficient?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-22 : 12:02:00

Try this query for speedier select.

SELECT		AccountID,
		Frequency,
		CASE
			WHEN ReturnMonth BETWEEN 1 AND 3 THEN 1
			WHEN ReturnMonth BETWEEN 4 AND 6 THEN 2
			WHEN ReturnMonth BETWEEN 7 AND 9 THEN 3
			WHEN ReturnMonth BETWEEN 10 AND 12 THEN 4
		END AS Quarter,
		ReturnMonth,
		ReturnYear,
		ShadedData,
		SlocumData,
		MIN(CASE WHEN AssetCategoryID = 19 THEN ReturnValue END) AS Gross,
		MIN(CASE WHEN AssetCategoryID = 18 THEN ReturnValue END) AS Net,
		MIN(CASE WHEN AssetCategoryID = 18 THEN EndingMV END) AS MarketValue,
		MIN(CASE WHEN AssetCategoryID = 19 THEN ICCReturnID END) AS GrossICCReturnID,
		MIN(CASE WHEN AssetCategoryID = 18 THEN ICCReturnID END) AS NetICCReturnID
FROM		dbo.JSAReturns
WHERE		AccountID = @AccountID
		AND assetcategoryid IN (19, 18)
		AND (

(ReturnYear > @StartYear AND ReturnYear < @EndYear)
OR
(ReturnYear = @StartYear AND ReturnMonth >= @StartMonth AND @StartYear <> @EndYear)
OR
(ReturnYear = @EndYear AND ReturnMonth <= @EndMonth AND @StartYear <> @EndYear)
OR 
(ReturnYear = @StartYear AND ReturnMonth >= @StartMonth AND @StartYear = @EndYear AND ReturnMonth <= @EndMonth)
)

GROUP BY	AccountID,
		Frequency,
		CASE
			WHEN ReturnMonth BETWEEN 1 AND 3 THEN 1
			WHEN ReturnMonth BETWEEN 4 AND 6 THEN 2
			WHEN ReturnMonth BETWEEN 7 AND 9 THEN 3
			WHEN ReturnMonth BETWEEN 10 AND 12 THEN 4
		END AS Quarter,
		ReturnMonth,
		ReturnYear,
		ShadedData,
		SlocumData
--ORDER BY	ReturnYear DESC,
--		ReturnMonth DESC,
--		ShadedData DESC

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-03-22 : 12:44:38

"set ICCID = 12 * returnYear + returnMonth"

Are returnYear and returnMonth unique combinations then?

"The temp table doesn't have a PK"

Well, whatever columns are a unique combination - my plan was to join the #TEMP back to the #ICCTemp and apply the IDENTITY value from #TEMP into #ICCTemp. For that to work there must be some field / combination-of-fields in #ICCTemp that are unique - that's what I was referring to as the PrimaryKey - actually "Unique Key Set" would be a better description!

Kristen

Reeve
Starting Member

5 Posts

Posted - 2007-03-22 : 13:17:38

Thanks Kristen and Peso. Yes, the returnMonth and returnYear are unique combos. I'll add this to my internal knowledge base.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-23 : 06:57:17

Yes, I noticed that with this query

set @tempMonth = (select max(returnmonth) from #ICCTemp where iccid is null)
set @tempYear = (select max(returnyear) from #ICCTemp where iccid is null)

if there are several records with same returnmonth and returnyear, they still get same id, so I figured that would be easily replacable with "12 * year + month" style. The sum is still unique per month & year, even if it does not start with 1.

Peter Larsson
Helsingborg, Sweden

Reeve
Starting Member

5 Posts

Posted - 2007-03-26 : 12:19:20

After a closer look, I found the month/year combos were not unique. I have solved my problem though via the query itself. I used variables based on row value to denote the year and month in question and added to my where clause to account for or ignore rows based on those variable values. Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources