Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Reader Challenges - SQL Puzzles wanted
 Reply to Topic
 Printer Friendly
Author  Topic Next Topic  

kselvia
Aged Yak Warrior

526 Posts

Posted - 07/08/2004 :  18:48:57  Show Profile  Reply with Quote
There have been no new Reader Challenges in a long time. I'm bored and wanted something challenging to play with. Got any difficult T-SQL problems, or know a good place with unsolved problems?

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/09/2004 :  00:03:06  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
How do you create an endless loop in T-SQL? How are you going to prove it?

Go ahead, prove it.....I dare you!!!!

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 07/09/2004 :  02:11:32  Show Profile  Visit ditch's Homepage  Reply with Quote
kselvia - write the code then and ask derrick to run it on his prodcution box to prove it.

Duane.
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 07/09/2004 :  04:18:11  Show Profile  Reply with Quote
Reminds me of the time I tried to open the event log in EM and got impatient waiting for it so I disconnected. (I was dialed up at the time.) Browsing to the log dir showed a 350MB log file growing by the several MB/second with 'Unable to send results to the client' messages (My disconnected session browsing the log being the client in question!) I had to shutdown the server before the log filled up the hard drive.
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 07/11/2004 :  19:39:23  Show Profile  Reply with Quote
Here is a pretty good question posted on another forum. What is a set-based solution to solve this problem? I don't know the right answer. I know a few ways to do it, but there must be better ways.

----------------------
I have my table like this. The zip  code column has zip code in 9 digits.  

Zipcode          Code
056790000    A101
056800000    A101
056810000    A101
056820000    A101
056890000    A101
056900000    A102
056901111    A102
056901168    A103
056910000    A102
056920000    A102
....
...
I have data like 100000 records in this view/table. I think the data format in the table is clear, if not i can provide more info


This is how i want the output to be.... I can have two columns to so i can have it like 

ZipStart         ZipEnd            Code
056790000   056890000     A101
056900000   056901111     A102
056901168   056901168     A103
056910000   056920000     A102
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/11/2004 :  21:13:00  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
that is a good one; i show one set-base way to do it in my article:

http://www.sqlteam.com/item.asp?ItemID=12654

- Jeff
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 07/11/2004 :  21:22:04  Show Profile  Reply with Quote
I had it up to the point of creating the run groups but couldn't get from there to the answer.

Can you demonstrate with this?

create table zip33 (zip varchar(9), code varchar(4))

insert zip33 select '056790000' ,'A101'
insert zip33 select '056800000' ,'A101'
insert zip33 select '056810000' ,'A101'
insert zip33 select '056820000' ,'A101'
insert zip33 select '056890000' ,'A101'
insert zip33 select '056900000' ,'A102'
insert zip33 select '056901111' ,'A102'
insert zip33 select '056901168' ,'A103'
insert zip33 select '056910000' ,'A102'
insert zip33 select '056920000' ,'A102'
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/11/2004 :  21:30:04  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
here you go:


select code, min(zip) as ZipStart, max(zip) as ZipEnd
from
(
select a.*, (select count(*) from zip33 b where a.code <> b.code and b.zip < a.zip) as RunGroup
from zip33 a
) a
group by code, rungroup
order by min(zip)


- Jeff
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 07/11/2004 :  21:39:25  Show Profile  Reply with Quote
Oh I see. Very cool I need to learn how to think like that. :) I was creating a running counter

select a.*, (select count(*) from zip33 b where a.code = b.code and b.zip < a.zip) as RunGroup
from zip33 a


and that was getting me nowhere;) Thanks alot.

For the record, I'll post your solution for the original poster rather than mine that used a couple of temp tables.

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21054849.html


--Ken
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/11/2004 :  23:23:05  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
It always amazes me how many forums there are out there. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 07/12/2004 :  19:58:14  Show Profile  Reply with Quote
Jeff,

The original questioner came back with:
----------------
I tried using this code for my data but it takes a lot of time. I have around 50,000 rows in my view and the query takes more than two minutes to run. Could it be because the view has the data randomly spread in it..? Actualy the query has been running from 3 minutes now and no results yet......

I tried running the inner code(subquery) and that takes more then a minute and does not return any results...
Help!

---------------
What is plan B when there are 50,000 rows? 50,000 subqueries may take some time


--Ken
Your Kung-Fu is not strong. -- 'The Core'
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/13/2004 :  08:08:48  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
I would have thought it would be that slow. Are there any indexes involved? Should there be?

Corey
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/13/2004 :  09:08:29  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
yes, that is the major drawback -- it is not really intended for large sets of data.

The thing is, by definition when you determine "streaks" in data you should only be appending newer rows in a particular sequence into your table ... that is, data should be added in the order of day1, day2, day3 and not day3,day1,day2 . If that is the case, a "posting" process as opposed to re-querying the entire transaction table would make sense -- keep track of new entries into the table, and then add to a summary table -- perhaps with a trigger. then for this reporting, use the summary table. that would be a fairly easy trigger to write.

if data is added randomly, or not always in order, then it doesn't seem to make sense as to why you would want to calculate and return the results asked for (to me, anyway).



- Jeff

Edited by - jsmith8858 on 07/13/2004 09:09:26
Go to Top of Page

pawankkmr
Starting Member

4 Posts

Posted - 05/05/2015 :  08:11:59  Show Profile  Reply with Quote

An effective one ...

;WITH CTE AS
(
SELECT * , ROW_NUMBER() OVER (ORDER BY %%Physloc%%) - DENSE_RANK() OVER ( PARTITION BY code ORDER BY zip) rnk FROM zip33
)
SELECT MIN(Code) Code , MIN(zip) ZipStart , MAX(zip) ZipEnd FROM CTE
GROUP BY rnk



Thanks !
Pawan Kumar Khowal
MSBISkills.com
Go to Top of Page
   Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000