Looping Records Help! - 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)

Looping Records Help!

Author

Topic

RockDad
Starting Member

9 Posts

Posted - 2010-01-26 : 10:14:36

My distinct query returns:
Type1
Type2

I want loop through those and until the value changes. Does that make sense?

My current loop gives me this:
Type1
Type2
Type1
Type2

I need to get:
Type1
Type1
Type2
Type2

-David

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-26 : 10:31:26

Could you post your full query ? most likely your not using ORDER BY

Jack Vamvas
--------------------
http://www.ITjobfeed.com

RockDad
Starting Member

9 Posts

Posted - 2010-01-26 : 10:36:57

DECLARE @Counter integer
DECLARE @Count integer
SET @Count = 0
SET @Counter = (SELECT COUNT(*) AS Count FROM #CycleTimeDatesTemp)

WHILE @Count < @Counter
BEGIN

INSERT INTO CycleTimeDates
(AwardDate, AwardYear)
(Select Distinct AwardDate, AwardYear FROM CycleTimeNum)

INSERT INTO Types
(Type)
(Select Distinct Type FROM CycleTimeNum)

SET @Count = @Count + 1

END

-David

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 10:40:08

That's going to insert the same data into CycleTimeDates and Types each time round the loop.

Is that what you need?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 10:41:38

whats the need of insertions inside loop? may be you could explain what you're looking at with some sample data

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-26 : 10:42:04

I assume your talking about the line :
INSERT INTO Types
(Type)
(Select Distinct Type FROM CycleTimeNum)

use ORDER BY

But all this will do is place your records in a certain order.But you cannot assume the records will be selected and presented in the same order

When you do the SELECT from types , use ORDER BY to present the records in athe order you require

Jack Vamvas
--------------------
http://www.ITjobfeed.com

RockDad
Starting Member

9 Posts

Posted - 2010-01-26 : 10:45:37

The Cycle time dates are fine. The sample data is in my initial post.
The distinct query has two values (Type1, Type2), the counter with this query is set to 2, so query gets looped over 2 times and inserts the following:
Type1
Type2
Type1
Type2

But I need it to loop over each distinct record 2 times before doing the loop so I get:

Type1
Type1
Type2
Type2

Hope this make more sense.

-David

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 10:48:36

quote:
Originally posted by RockDad

The Cycle time dates are fine. The sample data is in my initial post.
The distinct query has two values (Type1, Type2), the counter with this query is set to 2, so query gets looped over 2 times and inserts the following:
Type1
Type2
Type1
Type2

But I need it to loop over each distinct record 2 times before doing the loop so I get:

Type1
Type1
Type2
Type2

Hope this make more sense.

-David

Its irrelevant what order you do inserts in table as you can always retrieve data in order you want using order by. there's no concept of first or last in table unless you use order by so you dont need to be concerned about order of insertion.

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 10:50:10

Just to reiterate what JackV said:

A relational database has no concept of order. So if you add the records Type1, Type1, Type2, Type2 or Type1, Type2, Type1, Type2 its all the same. To get the data to display in a particular order you have to sort it in the SELECT statement (using an ORDER BY clause).

But I suspect that isn't really what you mean, so no, I'm not sure I ahve understood, sorry.

Your two lists:

Type1
Type2
Type1
Type2

and

Type1
Type1
Type2
Type2

are identical (from SQL's perspective), just one is sorted (for presentation display) and the other isn't

If there are other fields that impact on the "sequence" you are describing you'll have to explain what those are please.

RockDad
Starting Member

9 Posts

Posted - 2010-01-26 : 11:07:53

Thanks for your help and attention.

What I am ultimatly trying to do is find missing records.

Here is the regular output of data:
Type1 11 2009 106
Type2 11 2009 182
Type2 12 2009 101

As you can see, Type1 has no record for 12/2009, so I need to insert a blank record for Type1 for 12/2009 for charting purposes.

Any ideas on how to get the missing record?

-David

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 11:32:52

quote:
Originally posted by RockDad

Thanks for your help and attention.

What I am ultimatly trying to do is find missing records.

Here is the regular output of data:
Type1 11 2009 106
Type2 11 2009 182
Type2 12 2009 101

As you can see, Type1 has no record for 12/2009, so I need to insert a blank record for Type1 for 12/2009 for charting purposes.

Any ideas on how to get the missing record?

-David

yup you could do like (assuming column names are Type,Period and value)


SELECT r.Type,r.Period,COALESCE(t.Value,0)
FROM
(
SELECT t.Type,p.Period
FROM (SELECT DISTINCT Type FROM Table) t
CROSS JOIN (SELECT DISTINCT Period FROM Table) p
) r
LEFT JOIN Table t
ON t.Type=r.Type
AND t.Period= r.Period

which gives you 0 value for missing records

RockDad
Starting Member

9 Posts

Posted - 2010-01-26 : 12:16:14

What is 'COALESCE'?

Columns are Type, Date, Year, Time

-David

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 12:20:07

You not got an SQL DOcs installed, and not learnt how to use Google ?

msdn.microsoft.com/en-us/library/ms190349(SQL.90).aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 12:21:05

quote:
Originally posted by RockDad

What is 'COALESCE'?

Columns are Type, Date, Year, Time

-David

coalesce is function which returns first non null value from list of values. more details below

http://msdn.microsoft.com/en-us/library/ms190349.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 12:21:45

RockDad
Starting Member

9 Posts

Posted - 2010-01-26 : 12:25:33

OK, thanks.

Here is my query:
SELECT r.ContractType, r.AwardDate, a.AwardYear, COALESCE(t.Value,0)
FROM
(SELECT t.ContractType, p.AwardDate, p.AwardYear
FROM (SELECT DISTINCT ContractType FROM tblCycleTimeMedian) t
CROSS JOIN (SELECT DISTINCT AwardDate, AwardYear FROM tblCycleTimeMedian) p) r
LEFT JOIN Table t
ON t.ContractType = r.ContractType
AND t.AwardDate = r.AwardDate AND t.AwardYear = r.AwardYear

Getting the follwoing errors:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'Table'.

-David

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 12:30:14

quote:
Originally posted by RockDad

OK, thanks.

Here is my query:
SELECT r.ContractType, r.AwardDate, a.AwardYear, COALESCE(t.Value,0)
FROM
(SELECT t.ContractType, p.AwardDate, p.AwardYear
FROM (SELECT DISTINCT ContractType FROM tblCycleTimeMedian) t
CROSS JOIN (SELECT DISTINCT AwardDate, AwardYear FROM tblCycleTimeMedian) p) r
LEFT JOIN Table t
ON t.ContractType = r.ContractType
AND t.AwardDate = r.AwardDate AND t.AwardYear = r.AwardYear

Getting the follwoing errors:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'Table'.

-David

you need to replace red part with your actual table name

RockDad
Starting Member

9 Posts

Posted - 2010-01-26 : 12:30:53

Duh.... my bad. Thanks.

-David

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 12:32:47

welcome

RockDad
Starting Member

9 Posts

Posted - 2010-01-26 : 12:55:25

Thank you very much again! I spent the better part of 2 days trying varios code to get what I needed. You not only help me solve my problem but educated me at the same time, a great post!!!

-David

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 12:57:14

no problem. you're welcome

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources