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)
 Reader Challenges - SQL Puzzles wanted

Author  Topic 

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-08 : 18:48:57
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

4184 Posts

Posted - 2004-07-09 : 00:03:06
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
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-09 : 02:11:32
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 - 2004-07-09 : 04:18:11
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 - 2004-07-11 : 19:39:23
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

7423 Posts

Posted - 2004-07-11 : 21:13:00
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 - 2004-07-11 : 21:22:04
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

7423 Posts

Posted - 2004-07-11 : 21:30:04
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 - 2004-07-11 : 21:39:25
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.

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


--Ken
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-11 : 23:23:05
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 - 2004-07-12 : 19:58:14
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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-13 : 08:08:48
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

7423 Posts

Posted - 2004-07-13 : 09:08:29
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
Go to Top of Page

pawankkmr
Starting Member

4 Posts

Posted - 2015-05-05 : 08:11:59

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
   

- Advertisement -