| Author |
 Topic  |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
pootle_flump
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
 Posted - 07/31/2007 : 13:56:38
|
| Blimey, his post-padding-technique almost equals Peso's <BG> |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 08/01/2007 : 01:59:33
|
| Jarv's post has 3 lots of 3 consecutives ... |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 08/01/2007 : 01:59:43
|
| TEST! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/01/2007 : 02:30:10
|
quote:
Originally posted by Kristen
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87073
has 6 consecutive posts 
For once, I found an interesting problem.
I did this for my previous client in sql server 2000 with theupdate x set @v = v = case when ... then ... else ... end
thingy.
The 2005 version is more elegant.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 08/01/2007 : 02:34:05
|
"The 2005 version is more elegant"
Indeed! Except I can't understand it! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/01/2007 : 02:44:26
|
Which one of them?
The first one select the latest date according to parameters. This is the anchor part.
The recursive part looks for a record that has "EventTime + Duration" is equal to the last selected EventTime.
And the next time, the recursive part looks for a record that has "EventTime + Duration" is equal to the last selected EventTime.
And so on...
The second version does basically the same thing.
It gets all records according to parameters sorted by descending date. I then left join it with itself to see if EventTime + Duration is equal to EventTime of previous record.
Ultimately I return the record where NULL, meaning this is the earliest record with no match.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 08/01/2007 : 12:06:32
|
quote:
Originally posted by Kristen
"The 2005 version is more elegant"
Indeed! Except I can't understand it!
We've been using it for about a year in production now and the new T-SQL features are still foreign to me.
It took me a bit to figure out what Peter's solutions were doing, luckily he helped with an explanation. Thanks again, Peter.
Now no more talking about my post in the Twist List thread!
Tara Kizer
http://weblogs.sqlteam.com/tarad/ |
Edited by - tkizer on 08/01/2007 12:07:00 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 08/02/2007 : 04:01:43
|
This thread is now 7th in a Google search for "Twit List"
NALOPKT |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 08/02/2007 : 04:13:39
|
I'll know I'll have reached the pinacle status as a top-notch dba when I've got time to continuously post in this thread.
Until then... back to work.
Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
pootle_flump
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/06/2007 : 17:14:00
|
quote:
Hello,
I have a table with the following data: store, articles
I need to know how many articles in each store, in one sql query.
I know we can write smthg like:
select count(articles) from mystores group by store
But then i can't manipulate the data i get in my query! I am not writing t-sql, just a query.
I need to know for a given store the number or articles concerned.
Is there a way with CASE for example? Smthg like:
SELECT store, count(articles) ,
CASE store
WHEN '1' THEN store1count = store1count + 1
WHEN '2' THEN store2count = store2count + 1
ELSE 0
END
"total" FROM mystores
how can i define store1count and store2count in my query... remember , i can't use t-sql...
tx for helping!
Copied from http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=388164
What!? "I want to count the articles from a database but I do not want to use T-SQL because I am just writing a query"...
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Topic |
|
Twit List
