Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1213141516

Celko's SQL Stumper: The Data Warehouse Problem Expand / Collapse
Author
Message
Posted Monday, October 12, 2009 2:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:31 AM
Points: 5,014, Visits: 10,514
Thank you all, guys!
It really was a great experience and great fun!

Peso, I came across your blog on sqlteam last friday and I saw your new post: congratulations to you and your wife!
That is the best prize one can win!


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #801423
Posted Monday, October 12, 2009 3:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:31 AM
Points: 5,014, Visits: 10,514
OK, I know the competition is over, but I could not resist!
I combined the last query I posted with a technique I borrowed from Barry Young. I don't know if he already has given a name to it, but I think "Quirky XML Select" could fit:

DECLARE @customer_id char(10)
DECLARE @alreadyMatched int = 0
DECLARE @order_date date = GETDATE()
DECLARE @order_amt decimal(8,2) = 0

DECLARE @x nvarchar(max)
SET @x = ''

DECLARE @xml AS XML

SELECT
@alreadyMatched =
CASE @customer_id
WHEN customer_id THEN @alreadyMatched + 1
ELSE 0
END,
@x =
CASE @alreadyMatched
WHEN 1 THEN
CASE
WHEN @order_amt < order_amt
THEN
@x + '<C ID="' + RTRIM(customer_id) + '" DT="'+ CONVERT(char(8), @order_date ,112) +'"/>'
ELSE @x
END
ELSE @x
END,
@customer_id = customer_id,
@order_date = order_date,
@order_amt = order_amt
FROM DailySalesTotals
ORDER BY customer_id ASC, order_date DESC

SET @xml = CAST('<R>' + @x + '</R>' AS XML)

SELECT T.results.value('@ID[1]','char(10)') as [id],
T.results.value('@DT[1]','char(8)') as [dt]
FROM @xml.nodes('/R/C') AS T(results)

Although Joe isn't going to like it, it still works blindigly fast.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #801433
Posted Friday, October 23, 2009 2:30 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 577, Visits: 2,502
Having taken the 'Phil Factor SQL Speed Phreak' award in this competition, Peso won the next competition, ...
Phil Factor SQL Speed Phreak Competition: No 1: the The ‘Subscription List’ SQL Problem.

... It was quite a struggle with some close competition from many of those who participated in this competition. However, Peso came up with a blindingly fast winner that produced an aggregation from a million rows in a third of a second.

We decided that the winner of the competition should in turn present the next problem. We felt it would be a good way of keeping the ideas fresh, and preventing one person from the whole time. Peso has come up with a very intriguing problem
Phil Factor SQL Speed Phreak Competition: No 2: The ‘FIFO Stock Inventory’ SQL Problem

... This looks as if it will be an even more interesting challenge and so we're hoping for even more entrants to join together to see if there is a lightening-fast solution. Peso has submitted a cursor-based solution already for you to compare your routine to.

See you there!!





Best wishes,

Phil Factor
Simple Talk
Post #808161
Posted Thursday, November 12, 2009 7:54 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, June 13, 2014 10:04 AM
Points: 880, Visits: 605
Well, I see that the basic tenets of my solution have all been covered by previous posters, with one exception. nevertheless, here's the code I came up with:
;WITH ordered AS(
SELECT customer_ID, order_Date, order_amt, ROW_NUMBER() OVER (ORDER BY customer_id DESC, order_Date DESC)-rank() OVER(ORDER BY customer_id DESC) AS logicalOrder
FROM dailySalesTotals
)
,filtered AS(
SELECT customer_ID, order_Date, order_amt, logicalOrder
FROM ordered where logicalOrder < 2
)
SELECT l.customer_ID, ' dropped purchase amount on ', l.order_date
FROM filtered l
INNER JOIN filtered r ON l.customer_id = r.customer_id AND l.logicalOrder = 0 AND r.logicalOrder = 1
WHERE l.order_Amt < r.order_Amt

The one thing that I'd like to point out about my code is that using PARTITION BY customer_id forced the inclusion of an extra sort in the query plan, since partition by does not allow the ASC/DESC specification. Using RANK avoids that sort, thus saving me between 12 and 40 % of the overall query cost, depending on the particular data in the table.

The problem with this approach is in the duplicate index scans. It appears that some posters have been able to create an optimal plan by the use of the pivot operator. Clearly I need ot investigate...




Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
Post #818210
Posted Friday, June 11, 2010 3:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 5:22 AM
Points: 201, Visits: 51
select First.* from
(Select H1.* ,(select order_amt from #DailySalesTotals where H1.Odate = order_date and customer_id = H1.customer_id) order_amt
from (select customer_id ,max(order_date)as Odate
from #DailySalesTotals
group by customer_id having count(order_date)> 1) H1)First
inner join

(Select H1.* ,(select order_amt from #DailySalesTotals where H1.Odate = order_date and customer_id = H1.customer_id) order_amt
from (select customer_id ,max(order_date)as Odate
from (select t1.* from #DailySalesTotals t1 ,(select customer_id,max(order_date) as order_date from #DailySalesTotals
group by customer_id )t2 where t1.customer_id = t2.customer_id and t1.order_date <> t2. order_date)#DailySalesTotals
group by customer_id ) H1)Second
on First.customer_id = Second.customer_id and First.order_amt < Second.order_amt



above query run fine in sql server 2000
Post #935888
« Prev Topic | Next Topic »

Add to briefcase «««1213141516

Permissions Expand / Collapse