Attempting to Dodge a Cursor - 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 2000 Forums

SQL Server Development (2000)

Attempting to Dodge a Cursor

Author

Topic

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2006-03-01 : 11:03:48

Hi, I'm attempting to make a report that will rate our vendors, based on dates that they promise an order's delivery date vesus when it actually arrives. Here is the query so far..


alter procedure tsp_VendorPerformanceRpt2 /*Parameters, two date ranges*/
(@T1 VARCHAR(20),
@T2 VARCHAR(20))
AS

DECLARE @DATE_DIFF VARCHAR(20)

SELECT	
	V.NAME AS VENDOR_NAME,	
	PO.ID AS PO_ID,	
	--PO.ORDER_DATE AS PO_ORDER_DATE,
	--PO.DESIRED_RECV_DATE AS PO_ACKNOWLEDGED_DATE,	
	PO.LAST_RECEIVED_DATE AS PO_ACT_RECV_DATE,
	--PO.BUYER,
	--PO.STATUS,
	PO.PROMISE_DATE AS PO_PROMISE_DATE,
	--DATEDIFF(DD, PO.ORDER_DATE, PO.PROMISE_DATE) - DATEDIFF(WW, PO.ORDER_DATE, PO.PROMISE_DATE) * 2 AS CALC_LEAD_TIME, 
	DATE_DIFF = DATEDIFF(DD, PO.PROMISE_DATE, PO.LAST_RECEIVED_DATE) - DATEDIFF(WW, PO.PROMISE_DATE, PO.LAST_RECEIVED_DATE) * 2, 
	/*A positive DATE_DIFF means it was that many days after promised, negatives being early.. Doesn't count Sat/Sun.. so 6 days = 1 week?*/
	RATING = CASE /* Case is for adjusting to our one week early allowance, and one day late grace*/
		WHEN (DATEDIFF(DD, PO.PROMISE_DATE, PO.LAST_RECEIVED_DATE) - DATEDIFF(WW, PO.PROMISE_DATE, PO.LAST_RECEIVED_DATE) * 2) > 1 
			THEN (DATEDIFF(DD, PO.PROMISE_DATE, PO.LAST_RECEIVED_DATE) - DATEDIFF(WW, PO.PROMISE_DATE, PO.LAST_RECEIVED_DATE) * 2) - 1
		WHEN (DATEDIFF(DD, PO.PROMISE_DATE, PO.LAST_RECEIVED_DATE) - DATEDIFF(WW, PO.PROMISE_DATE, PO.LAST_RECEIVED_DATE) * 2) < -6 
			THEN ABS((DATEDIFF(DD, PO.PROMISE_DATE, PO.LAST_RECEIVED_DATE) - DATEDIFF(WW, PO.PROMISE_DATE, PO.LAST_RECEIVED_DATE) * 2) + 6) 
		ELSE 0 /* Zero means on-time.*/
		END
FROM
	VENDOR V
	LEFT JOIN PURCHASE_ORDER PO ON (V.ID = PO.VENDOR_ID)
WHERE	
	PO.ORDER_DATE BETWEEN @T1 AND @T2 
	AND PO.PROMISE_DATE IS NOT NULL
	AND PO.STATUS = 'C'
ORDER BY RATING DESC

Here is some sample output when running the SP:


|---VENDOR_NAME----------------|--PO_ID-----|--PO_ACT_RECV_DATE------|--PO_PROMISE_DATE--------|--DATE_DIFF--|--RATING--|
VENDOR 1        	        PO-101549	2006-02-21 00:00:00.000	2006-03-14 00:00:00.000	-15	      9
VENDOR 2                 	PO-101554	2006-02-22 00:00:00.000	2006-02-17 00:00:00.000	3	      2
VENDOR 3                	PO-101555	2006-02-16 00:00:00.000	2006-02-14 00:00:00.000	2	      1
VENDOR 4                	PO-101546	2006-02-17 00:00:00.000	2006-02-15 00:00:00.000	2	      1
VENDOR 1        	        PO-101535	2006-02-14 00:00:00.000	2006-02-14 00:00:00.000	0	      0

First question is, I need use a forumla to average the "Rating" per vendor, where right now my SP is giving me rating per Order (PO).. I haven't figured a final forumla for the Vendor Rating - it'll be some sort of average of the Order ratings from that vendor, then weighing in the Ontime ratings.. An on time rating will be 0. How can I gather the Order rating from each vendor and use it in a forumla without a cursor, and temporary tables?

Here is my desired output:


|---VENDOR_NAME----------------|--PO_ID-----|--PO_ACT_RECV_DATE------|--PO_PROMISE_DATE--------|--DATE_DIFF--|-PO_RATING--|--VENDOR_RATING--|
VENDOR 1        	        PO-101549	2006-02-21 00:00:00.000	2006-03-14 00:00:00.000	-15	      9                5
VENDOR 2                 	PO-101554	2006-02-22 00:00:00.000	2006-02-17 00:00:00.000	3	      2                2
VENDOR 3                	PO-101555	2006-02-16 00:00:00.000	2006-02-14 00:00:00.000	2	      1                1
VENDOR 4                	PO-101546	2006-02-17 00:00:00.000	2006-02-15 00:00:00.000	2	      1                1
VENDOR 1        	        PO-101535	2006-02-14 00:00:00.000	2006-02-14 00:00:00.000	0	      0                5

(I hope these columns are showing up ok)

So notice VENDOR 1 has an order he shipped to us on time, then one was 9 days, in this case, early.. so his Vendor rating for this time period is 5. And to tie it all together I need the highest Vendor ratings showing first (a simple order by vendor_rating).

Hope that isn't too confusing! Tell me if I should provide more information.

Question 2 is much simpler.. notice in my SP, I'm using that same DATEDIFF() several times, is there a way to set a variable and reuse that? I've tried several examples that were giving me many errors, but no luck.

Thanks in advance --

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-01 : 11:56:56

For the second question..


SELECT
	VENDOR_NAME,	
	PO_ID,	
	PO_ACT_RECV_DATE,
	PO_PROMISE_DATE,
	DATE_DIFF,
	RATING = CASE /* Case is for adjusting to our one week early allowance, and one day late grace*/
		WHEN DATE_DIFF > 1 
			THEN DATE_DIFF - 1
		WHEN DATE_DIFF < -6 
			THEN ABS(DATE_DIFF + 6) 
		ELSE 0 /* Zero means on-time.*/
	END
FROM
(
	SELECT	
		V.NAME AS VENDOR_NAME,	
		PO.ID AS PO_ID,	
		--PO.ORDER_DATE AS PO_ORDER_DATE,
		--PO.DESIRED_RECV_DATE AS PO_ACKNOWLEDGED_DATE,	
		PO.LAST_RECEIVED_DATE AS PO_ACT_RECV_DATE,
		--PO.BUYER,
		--PO.STATUS,
		PO.PROMISE_DATE AS PO_PROMISE_DATE,
		--DATEDIFF(DD, PO.ORDER_DATE, PO.PROMISE_DATE) - DATEDIFF(WW, PO.ORDER_DATE, PO.PROMISE_DATE) * 2 AS CALC_LEAD_TIME, 
		DATE_DIFF = DATEDIFF(DD, PO.PROMISE_DATE, PO.LAST_RECEIVED_DATE) - DATEDIFF(WW, PO.PROMISE_DATE, PO.LAST_RECEIVED_DATE) * 2, 
		/*A positive DATE_DIFF means it was that many days after promised, negatives being early.. Doesn't count Sat/Sun.. so 6 days = 1 week?*/
	FROM
		VENDOR V
		LEFT JOIN PURCHASE_ORDER PO ON (V.ID = PO.VENDOR_ID)
	WHERE	
		PO.ORDER_DATE BETWEEN @T1 AND @T2 
		AND PO.PROMISE_DATE IS NOT NULL
		AND PO.STATUS = 'C'
	ORDER BY RATING DESC
) A

As for the first question, If you want to display both records then I can't think of a way without using a #temp table..

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2006-03-01 : 12:59:05

Hmm would that be a performance gain, using two selects vs doing datediff() more times? Can't tell a difference in QA. Thanks though the output works of course :)

I might handle my first question at the Crystal Report level, using two SPs.. one to do vendors, then another in a sub-report for orders, I'll probably make both and see which is faster execution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources